Help:COAP-3180: Difference between revisions

The educational technology and digital learning wiki
Jump to navigation Jump to search
mNo edit summary
Line 63: Line 63:
* [[Wordpress installation and configuration]]
* [[Wordpress installation and configuration]]
* [[SQL and MySQL tutorial]]
* [[SQL and MySQL tutorial]]
* [[XML]]
* [[DTD tutorial]] (understand how to use/read a DTD only)
* [[XSLT Tutorial - Basics]]
* [[XPath tutorial - basics]]
* [[PHP-MySQL tutorial -basics]]
* [[PHP - MySQL - XML tutorial - basics]]
* [[XQuery tutorial - basics]]


=== Other teaching resources ===
=== Other teaching resources ===

Revision as of 22:13, 8 February 2010

<pageby nominor="false" comments="false"/>

Introduction

Disclaimer: This is a resource page for my Spring 2010 I Web Databases course (COAP 3180) at Webster University, Geneva. This page does neither replace the official syllabus nor the information in the official learning management system (WorldClassroom/Blackboard). It allows students to navigate more efficiently through the materials in this wiki.

The program and materials are provisional and will be adjusted on a weekly basis.

Course level outcomes

At the completion of this course students will be able to:

  1. Add a Webpage Database to an HTML page
  2. Manipulate data using SQL operations
  3. Publish both static and dynamic data on the Web
  4. Convert text files to XML databases
  5. Be able to query XML data using XQuery
  6. Be able to work with relational data as XML

These outcomes defined in the syllabus can be translated to more practical outcomes:

  1. Use simple Web 2.0 database services and integrate it into a webpage.
  2. Manipulate data using SQL operations and create simple SQL databases in a DBMS.
  3. Understand the architecture of a simple web application (HTML client / Web server / Script / Database).
  4. Be able use a LAMP/WAMP software bundle and to install and to configure web applications, e.g. a CMS. This can evolve into a term project.
  5. Generate a very simple web application that can connect to a database management system (DBMS) though an ODBC API.
  6. Understand/recall XML basics, like converting text files and database output to XML, editing an XML file using a schema and displaying XML data.
  7. Be able to query and update XML data files and XML Databases using XQuery dand XQuery Update.
  8. Understand the general architecture and purpose of LDAP address and authentication servers and light-weight solutions like OpenID.

Week 1 - COAP 3180

Topics Covered

(1) Introduction

(2) Web 2.0 vs. "traditional Internet"

  • An ever increasing amount of applications are available through the Internet. Many of these applications can be shared, others are by definition "social software" (e.g. wikis, YouTube, flickr, blogs).
  • Reading (for students not familiar with the "web 2.0" concept): O'Reilly Tim (2005), What is Web 2.0, better take the print version.
  • Additional resources for those who want to explore this topic further: This wiki, e.g. start from the rich internet application article.

(3) Simple on-line database services

  • Learn how to create and use simple tabular databases with a web 2.0 service (http://creator.zoho.com)
  • Practical details are explored in class and through homework one.

Classroom activities and homework

Synopsis:

(1) Take an account with Zoho (see also below)

(2) Take an account with Netvibes (see also below)

  • http://netvibes.com
  • Create a new page (tab)
  • Import the result view from Zoho
  • Export this page to the public

(3) Turn in this homework in the WorldClassroom:

  • Provide the NetVibes URL
  • Define the objective of this application in 2-3 sentences (including the target audience)

Homework 1

  1. Think about a little database (i.e. a simple list of items that have features/properties) and that you would like to put on the web. Then make a list of properties that describe such a "item".
    • Examples: A list of CDs, a list things you want to sell, your favorite list of flowers
  2. Implement a Zoho creator database form that allows to describe each item with a few properties (fields). Make sure to select the right data type for a given kind of item.
  3. Add some test data to this database. Revise the database definition if needed.
  4. Enter some more data (at least 5 items)
  5. Make the Zoho "view" that displays the results public (gathered data becomes public on the web). You also can change the way data are displayed.
  6. If you wish, you also can make the data gathering public
  7. Create a public NetVibes page and import your Zoho creator widget (advanced course participants may use another environment that allows insertion of web widgets, talk to the instructor ....)
  8. Add an explanation using some appropriate NetVibes "text" widget, i.e. describe to the reader what your pagecast (in particular the Zoho form) is good for.
  9. In order to achieve better user experience, you also should add some useful extra information/widgets to the Netvibes page (or equivalent), i.e. the page cast should include the Zoho database plus the "what's this" (previous point) plus some useful extra information.
  10. Submit your work as assignment in the Worldclassroom. Important: Urgently consult the librarian if you can't log into this Webster on-line environment. In the submission field:
    • Provide the URL
    • Define the objective of this application in 2-3 sentences (including the target audience)


Remarks:

  • If you prefer to remain anonymous, do not leave Name, Firstname, professional email etc. or anything that can identify you on the public page.
  • Tips that help you getting this homework done are below ...

Submission Dates

  • This homework is due at start of Wednesday week 2 lesson
  • Students must provide the URL (where the instructor can see the widget) in the world classroom
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

  • Quality of Zoho database: Items should be described in a useful way with respect to your objectives and the target audience. You should use appropriate data types (but don't make it too big !)
  • Quality of the Netvibes pagecast (is it usable and understandable ?)
  • Worldclassroom upload (short definition of the objectives and correct URL)

Tip:

  • Quality is essentially related to the idea that your applications are useful to a given range of users.
  • Make sure that all required elements are present

Tips for the homework

Below is a rough outline of the procdure you should follow.

(1) Leave all the needed applications open in your browser

Make sure that you are logged to both applications.

  • Netvibes with your login
  • Zoho: Switch to Zoho Creator

Tip: It is best to work with 3 different web browser tabs: One for Netvibes, one for Zoho, and one for this page. If necessary, ask the instructor to explain tabbed browsing...

(2) Read the Help, in particular the Quick Start Guide for Zoho

Important starting points:

  • http://help.creator.zoho.com/ - The most important thing to understand about Creator is that you will have two interfaces: One for defining the database table structure (form authoring) and another for gathering data and looking at the results (the view).
  • http://tour.netvibes.com/overview.php - The most important thing to understand about NetVibes is that you must find a Netvibes widget called HTML/UWA, insert it in a pagecast and then enter the Zoho widget code through the Edit button.

(3) Make a Zoho widget public

To export a widget from Zoho:

  • Click on Edit this application / Share
  • Tick This application is: X public

(4) Exporting Zoho widget code To get the Zoho widget code for embedding in some other place

  • Click on Access this application
  • Select a view from the menu to the left
  • Then use the pull-down menu More Actions->Embed in your Website
  • Copy/Paste the code to another web application. E.g. the HTML Netvibes widget as explained below.

(5) Configuring Netvibes and widget import

Firstly (if this isn't already the case) create a new page in Netvibes

  • Then make this Netvibes page public:
    • Click on the Edit button (in the Tab of the page)
    • Then click on "Share this Tab" / Publish on your page.
    • Make sure to write down the URL (You will have to submit it as homework)

To import a Zoho Creator application (database) into Netvibes:

  • Create a HTML/UWA Widget (you can find it under "HTML" in the list of essential widgets)
  • Edit the widget
    • Copy/Paste the widget code from Zoho

See also the Creating websites with online services article in this wiki. It provides an overview of some easy and free online services.

Links and teaching materials

Online software
http://creator.zoho.com/ (Zoho creator online database)
http://help.creator.zoho.com/ (Help entry point)
http://netvibes.com/ (NetVibes webtop)
http://faq.netvibes.com/ (Help entry point)
Teaching materials
Creating websites with online services
Using web widgets
Slides for the overview talk (PDF)

Week 2 - COAP 3180

Topics Covered

We will explore (concurrently) the following topics:

Web application architecture overview

  • Overview of web hosting alternatives
  • Application architectures / frameworks overview
  • WAMP/LAMP software bundles and applications, various options
  • Use of a portable WAMP architecture (Mowes)
  • The three tier model

Short overview of databases

  • What is a Database and how is it used in Internet applications ?
  • Relational data base management systems (RDBMS) and ownership of databases
  • MySQL (databases, tables and users): First contact with this RDBMS

Portalware (Content management systems / light-weight web application frameworks)

  • Short overview of typical LAMP-based systems systems (and similar)
  • An overview of typical installation and configuration work.

Classroom activities

Monday

(1) Mix, download and install a Mowes combo'

(a) Download the Modular Webserver System (Mowes) server

You will need at least the following modules

  • Server software
Apache 2_SE
MySQL 5_SE
PHP5
  • Mission critical application software (!!)
PHPMyAdmin
  • Application software
Drupal
Joomla
ModX
Wordpress

(b) Dezip the downloaded archive to drive c:\temp2

  • Ask a lab assistant or the instructor if you need help with 7zip

(c) Click on Mowes.exe

  • This will install the Mowes server in the directory plus install all the downloaded application software. Be patient. This may take some time.

(d) You can add other portals by downloading a new module from MOWES. Drop the file to the top-level directory and restart the mowes application.

Getting your own copy

  • You may copy the whole directory to a memory stick (may take a full hour to copy). Applications will continue to work. Mowes doesn't write anything to a system directory or the registry and will adapt to a new directory structure.
  • Alternatively, you can re-enact the process at home on your personal computer...
Donwlaod again from http://www.chsoftware.net/en/mowes/mowesportable/mowes.htm

(2) A tour of the Mowes WAMP system

  • The instructor will show some features of the system and relate it to an introductory talk about web applications

(3) Playing with portalware

(4) Configuration of portalware (Mowes)

  • Mowes packages all include the same admin login and password.
Login = admin and password = password
  • You may need to figure the admin URL for each application.

Wednesday

(1) Configuration of Wordpress 

       18348 09-24 15:25 GPL.txt
         660 09-24 15:25 Licence.txt
           0 01-12 22:08 apache2
           0 01-12 22:07 mowes
      409600 09-24 15:25 mowes.exe
           0 01-12 22:08 mysql
           0 01-12 22:08 php5
                         wordpress.pck  <- newly added Mowes package
           0 01-12 22:13 www
    • (re)start the server. Wordpress will be automatically installed
  • Update
    • Log in as admin (password = password)
    • Click on the update banner to automatically update Wordpress to a latest version. (also available in Tools->Upgrade)

(2) Configure wordpress

  • Change the theme
  • Change the contents of the first default article, then post an article
  • Remove / add links
  • Add the formidable plugin (http://wordpress.org/extend/plugins/formidable/)
    • This will allow you to create forms (a bit like Zoho creator)
    • Click on the "Formidable" icon at the bottom of the left-hand "Dashboard" column in the admin interface and define a form
    • Create a page (click on pages), then add the "formidable" form inside. Use the following code to insert a form:
[ formidable id=x ]
  • Add a user or two and test drive with these (using a different web browser)

(3) Installing applications that are not packaged by MOWES

  • Create a database and a database user with PhpMyAdmin
  • Download, dezip to mowes/www directory
  • Install/configure the system according to instructions found

Students not familiar with this procedure should install a new version of wordpress.

(3) Discussion about web hosting

Homework 2

Topic: High-level exploration of a portalware (CMS/CMF) / preparation of the term project

Task

Task Summary:

  1. Shortly sketch out a website project for a given targent audience
  2. Collect links about a portalware (not wordpress) that "may do the job" and create a resource about this select
  3. Finally, try to install it on a local webserver (or a provider) and make a simple test to see if it runs

Deliverable: Produce a resource (e.g. an HTML page) that includes the five following elements:

(1) Objectives and target population of a web site that you plan to create. This website must include some dynamic elements (e.g. the possibility to add contents for specified users). Here is nice little template from the wordpress codex:

This website will be dedicated to X, Y, and Z,

and cover the topics of A, B, and C. The audience will

be __________ ________________ _______. I will be adding

posts every _____________ about ________ _______ ______________.

I am doing this because _____________ _____________ __________________.

(2) Provide some links to useful information. Create a shortlist of three CMS/CMF systems that run under PHP/MySQL. Select the most promising one and add the following links:

  • Download page of the software
  • A link to two respectable websites that provide comments and evaluations of this system
  • Link to the installation manual
  • Link to the configuration manual (if different)
  • 2-3 links to other useful help sites

(3) Make a simple test. Install the system with MOWES. If the installation fails, try to explain why and skip step 4.

(4) Create at least one form of content and explain what you had to do to get it down and tell us if it was difficult.

(5) Write a short general comment about the portware you looked at. Do you think that it might be suitable for your needs ?

Important: You are not allowed to choose Wordpress (WP) since I want to look at another system, but you may choose WP for the term project ...)

You can create this resource in three ways:

  • Create a webpage on your PC and upload the web page to the worldclassroom (you don't need to hand code HTML, just use a tool like Kompozer)
  • Create a netvibes pagecast and submit the URL (see hw 1)
  • Use your own personal web site, e.g. create a blog post and submit the URL.

This homework is a preparation for the term project. Doing it will help you get started.

Submission Dates

  • This homework is due on wednesday week 3 (before lesson start)
  • Students must provide an URL or upload an HTML file in the world classroom (no Word!).
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

see the grading form in the worldclassroom

Tips for the homework

  • The easiest solution is to select a CMS/CMF system that is available as Mowes package, e.g. ModX, Typolight, Drupal, Joomla, Zikula (all CMS/CMF)
  • Otherwise start from the links in the Portalware article. Do not google for finding a good CMS (too many websites you will have to sort out). Best bet is to start exploring OpensourceCMS.com. Ambitious students should try this strategy. It you help you become a CMS expert :)

Links and teaching materials

Software

Teaching materials

Reading

I suggest that you have a look at the following articles. Reading is not mandatory, but may help you understand some concepts and issues.

Week 3 - COAP 3180

Topics Covered

Portalware configuration (continued):

  • discussion of homework 2 if needed

Relational databases: Introduction to SQL (part 1):

  • Retrieving data
  • Definition of SQL tables and use of the PhpMySQL tool
  • Relational tables (principles)
Main teaching materials

Classroom activities

Monday

  • Define a table with SQL code
  • Learn how to inject SQL code in MySQL Admin (a web application to administer web databases) in order to create databases and tables
  • Create a simple 1-table MySQL database with the database administration tool

Play time

Warning. Be careful with quotes and double quotes.

  • Quotes must be straight (same for double quotes).
 '  = Ok
 ’  = BAD !!!

... So use a real text editor and not a word processor.

(1) Demo 1 example
CREATE TABLE demo1 (
  id int(10) NOT NULL auto_increment,
  login char(10) DEFAULT '' NOT NULL,
  password char(100),
  fullname char(40) DEFAULT '' NOT NULL,
  url char(60) DEFAULT '' NOT NULL,
  food int(11) DEFAULT '0' NOT NULL,
  work int(11) DEFAULT '0' NOT NULL,
  love int(11) DEFAULT '0' NOT NULL,
  leisure int(11) DEFAULT '0' NOT NULL,
  sports int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY login (login)
);
(2) Student database with 2 tables
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS exercise;

CREATE TABLE student (
  id int(10) NOT NULL auto_increment,
  name char(40) DEFAULT '' NOT NULL,
  first_name char(40) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO student VALUES (NULL,'Testeur','Bill');
INSERT INTO student VALUES (NULL,'Testeur','Joe');
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');

CREATE TABLE exercise (
  id int(10) NOT NULL auto_increment,
  title char(40) DEFAULT '' NOT NULL,
  student_id int(10) NOT NULL,
  comments varchar(128),
  url char(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  KEY student_id (student_id)
);

INSERT INTO exercise VALUES (NULL,"Exercise 1",'1',"small comment",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"Exercise 2",'1',"no comment",'http://tecfa.unige.ch/');

Classroom activities Wednesday

  • Continue creating a table (if needed)
  • Relational databases with 2 or 3 tables (simple 1-N and N-N cases)
  • Web hosting (discussion)
  • The anatomy of Wordpress

Homework 3

Topic: Database definition (2-3 tables), Details to be announced on Wednesday.

Define an SQL file that will create 2-4 related tables. You can freely choose the application area, but it should somewhat relate to prior homework or the term project.

Minimal requirements:

  • Minimum 2 tables and max. 4 tables. Make sure that the tables are related.
  • At least 10 fields (for all tables together, e.g. 6 for table one and 4 for table 2)
  • At least 1 integer and 1 varchar (for all tables)
  • Each table must define a primary key
  • You may look at real example and take inspiration from these under the condition that you make some modifications and that you quote the source in the comment !!

The SQL file must include some documentation on top that (1) explains the purpose of this database and of each table and that (2) provides some high-level design elements. See the SQL and MySQL tutorial for comment syntax.

Remarks and tips

This homework is not related to the term project, but it helps you preparing exams.

Before you submit your homework, make sure to test it, i.e. load it into the database, e.g. with PhPMyAdmin. If you this more than once, you must kill the old tables, use the following SQL command: 

DROP TABLE IF EXISTS name_of_your_table;

E.g. 

DROP TABLE IF EXISTS exercise;

To get the "2 related tables" structure right, think for example about a relationship between a person and what he can own or produce. E.g. One Author writes N books, One CD includes N titles.

An example discussed in Yarger et al. (1999:18ff) is a four tables structure: Artists - songs - CDs - Record labels. One artist can produce several songs. A CD contains several songs. A Record label publishes several CDs. A corresponding table structure would look like this: 

Artist
     Artistid  primary key
     name      ....

CD 
     CDid      primary key
     title     ....
     LabelID   foreign key

Song
     Songid    primary key
     name      ....
     CDId      foreign key
     ArtistId  foreign key 

Label
     Labelid   primary key
     name      ....

Submission Dates

  • This homework is due at start of wed week 4
  • Students must upload the SQL file to the worldclassroom (don't forget the documentation inside this file).
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

  • See the worldclassroom
  • A well documented and well designe two table design may also get an A (so you don't must go for a three our four table solution ...)

Teaching materials and further reading

Online software
  • We shall use MoWes again
Teaching materials (slides and reading)

You probably will have to complete my lecture notes with some extra reading. Pick one of the following. (Unfortunately, some of these articles are presented as multiple pages ....)

Week 4 - COAP 3180

Topics Covered

HTML principles recalled

  • HTML Forms
  • (XMLhttprequest)

Introduction to PHP

  • Principles of PHP programming
  • HTML forms tutorial
  • Simple PHP/MySQL interaction

Working with Apache/PHP/MySQL

  • Web server organization (a look at Mowes)

Using PHP/MySQL webform generators:

Classroom activities

Classroom activities Monday

HTML
  • Recall of HTML principles (lecture)
PHP
  • Exploration of the PHP language (lecture)
HTML-PHP Example

File form.html 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>Simple test with</title></head>
<body>
<h1>Simple test with PHP</h1><hr>
 <form action="process-it.php" method="post">

 What do you know about HTML ?
 <input type="radio" name="choice" value="1" checked>little
 <input type="radio" name="choice" value="2">some
 <input type="radio" name="choice" value="3">everything

 <br>
 What is your programming experience ?
 <input type="radio" name="choice2" value="1" checked>none
 <input type="radio" name="choice2" value="2">some
 <input type="radio" name="choice2" value="3">good
 <P>
 <input type="submit" value="See result!">
 </form>
</body>
</html>

File process-it.php: 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>Simple test with</title></head>
<body>
 <h1>Simple test with PHP</h1><hr>
 <?php

  // Get variables from the form
  $choice = $_POST['choice'];
  $choice2 = $_POST['choice2'];

  // Compute the score
  $score = $choice + $choice2;
  // Display the score
  echo "<h3>Your score is " . $score . "</h3>";
  if ($score < 3) {
    echo "<p>You are a beginner</p>";
  } elseif ($score < 5) {
    echo "<p>You have some knowledge</p>";
  } else {
    echo "<p>You are an expert !</p>";
  }
  ?>
  <hr>
  // phpinfo();
</body>
</html>

Classroom activities Wednesday

PHP-MySQL (homework preparation)
  • PHP MySQL functions (exploration of the online PHP manual / Slides) if time left only
  • Use of the PHP Generator for MySQL (Maestro Group)

In order to profit from this generator, you should declare foreign keys and use the InnoDB engine as in the example below. 

-- MySQL example with foreign keys.
-- Needs the InnoDB engine (see the table definitions) 

--
-- Table 'student'
--

DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
  id int(10) NOT NULL AUTO_INCREMENT,
  name char(40) NOT NULL DEFAULT '',
  first_name char(40) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Data for table 'student'
--

INSERT INTO student (id, name, first_name) VALUES
(1, 'Testeur', 'Bill'),
(2, 'Testeur', 'Joe'),
(3, 'Testeuse', 'Sophie'),
(4, 'Player', 'Jim');

--
-- Table 'exercise'
--

DROP TABLE IF EXISTS exercise;
CREATE TABLE IF NOT EXISTS exercise (
  id int(10) NOT NULL AUTO_INCREMENT,
  title char(40) NOT NULL DEFAULT '',
  student_id int(10) NOT NULL,
  comments varchar(128) DEFAULT NULL,
  url char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY student_id (student_id),
  FOREIGN KEY (student_id) REFERENCES student(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table 'exercise'
--

INSERT INTO exercise (id, title, student_id, comments, url) VALUES
(1, 'Exercise 1', 1, 'small comment', 'http://tecfa.unige.ch/'),
(2, 'Exercise 2', 1, 'no comment', 'http://tecfa.unige.ch/'),
(3, 'exe four', 2, 'No comment', 'http://localhost/'),
(4, 'exe four', 2, 'No comment', 'http://localhost/');
Term project
  • Q/A

Midterm exam (Wednesday)

Main subjects will be SQL and a little bit of database design. "Minor" subject will be web application architecture (high level principles)

SQL:

  • SELECT
    • SELECT ... WHERE, INSERT ... ORDER
    • Joins (select from two tables)
  • CREATE
    • Data types: Integer, Float, VarChar, text
    • DEFAULT, NOT NULL, Auto increment
    • Keys
    • Primary keys
  • INSERT (both forms(
  • UPDATE / DELETE
  • DROP

Database design:

  • Simple 2-3 table architectures
  • 1 to N and N to N relations between tables

Exam will be "open book". I will include a set of various tasks (e.g. a simple design problem, repair problems, conceptual questions)

Homework 4

Generate and configure a web application based on a SQL table definitions.

  • Create between 1 and 4 SQL tables or reuse one or all tables from homework 3. Add foreign keys definitions if needed, because if you use foreign keys then the Generator will be able to generate pull-down menus in the edit interface.
  • Use the Maestro PHP Generator to create an interface that allows to view and to edit data.
  • Explore the features of this tool (most are actually disabled in the free version)

Submission: choose either one

  1. Migrate the files to a service provider and submit the URL to the instructor together with a short list of features you implemented. You may paste the this list in the submission field of the Worldclassroom interface. Migration may require a little bit of PHP tweaking (edit the settings.php file).
  2. Create a short report with a short feature list plus screen captures of the most important screens a user will see and submit either an URL with an online text or an HTML file to the instructor. If you submit an HTML file, make sure to create a zip file that includes all the pictures.

In other words: I need (a) a list of what you have done and (b) either a URL where I can see the application or a zipped HTML + screen captures zip file.

Submission Dates

  • This homework is due at start of Wednesday week 5
  • Students must provide either an URL for the application, an URL for documentation or an HTML file with documentation.
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

  • Same principle as homework 1. Make sure to get both the table definition(s) and the user interface/ergonomics right.
  • See WorldClasroom

Tips for the homework

Make sure to get the bulk of the homework done by Monday, Week 5. I will reserve some time for questions.

SQL
  • Use foreign keys and the InnoDB engine if your tables a related. The foreign keys syntax to use is
 FOREIGN KEY (field_in_this_table) REFERENCES other_table (primary_key_field)

Example: In a exercises table, each record has a student_id column that points to the id column of the student table. 

 FOREIGN KEY (student_id) REFERENCES student(id)
PHP Generator

Entry screen:

  • In the connection properties make sure to get the database name right

Objects screen:

  • Select all tables you want the user to edit later on

Queries screen:

  • Skip, unless you know SQL very well. In the latter case you may formulate a query that will sort data displayed (see the manual)

Pages screen:

  • for each table, you can define which columns will be visible and editable. Hit the Edit button (top right)

Color scheme:

  • Select a theme
  • You may customize the CSS

Last screen (important !)

  • Change default headers and footers (top). This allows you to add extra information in the generated pages.
  • Define the output directory (bottom). We suggest to create a mowes/www/subdirectory and to put the files there directly.

If you plan to migrate to a an other provider (totally optional)

  • Export the SQL from the database
  • Edit file settings.php and change the return array values of the first function in the file.
  • Import the SQL to the other MySQL
  • Move all the files to the other web server.

Software, teaching materials and links

Software (installed in the Lab)
Teaching materials
PHP with MySQL tutorials

Reading these is optional, since PHP coding will not be tested in an exam or in the term project. Below are a few PHP and PHP/MySQL tutorials that can be found on the net. There exist many others.

  • PHP Tutorial (W3C Schools, includes a section about PHP/MySQL).
  • Williams, Hugh, E. (2002). Tips for Building Web Database Applications with PHP and MySQL, onLamp article (O'Reilly Databases). HTML

Week 5 - COAP 3180

ATTENTION: Monday week 6 class is cancelled. To catch up, we will add 20 minutes to each class.

Topics Covered

Homework and mid-term exam
  • Quick feedback
Termproject
  • Each student should now have a "must-have" and "nice-to-have" list of features
Crashcourse on XML
  • XML - The formalism
  • XML - where is it used
  • XSLT - XML transformations
  • XPath - identify elements in a tree (used by XPath and XQuery)

Classroom activities

Monday

(1) Term project

  • Must have and nice to have features
  • Remark: There must an embedded "database", i.e. a data-collection / presentation module

(2) Wordpress

  • Fire up MoWes and enter wordpress as admin
  • Install "Formidable" if not already done so
    • Best method is to click on Plugins in the Dashboard
    • Else, use the "manual" method described by its developer.

(3) Produce XML output from an SQL query with php 

<?php

/* Made by Daniel K. Schneider, TECFA Jan 2010. This is freeware
   Will connect to a MySQL database, execute an SQL statement and
   then return the result as valid XML. 
 */
error_reporting(E_ALL);

// ---------------------------  Configuration section

// Fill in according to your db settings (example is for a local and locked "play" WAMP server)
// Make sure to keep the ; and the ""
$host       = "localhost";
$user       = "root";
$pass       = "";
$database   = "exam";

// Replace by a query that matches your database
$SQL_query = "SELECT * FROM question,response_item WHERE question.id = response_item.id_question";

// Optional: add the name of XSLT file.
$xslt_file = ""; 
//$xslt_file = "mysql-result.xsl"; 

// -------------------------- no changes needed below

$DB_link = mysql_connect($host, $user, $pass) 
           or die("Could not connect to host.");
mysql_select_db($database, $DB_link) 
           or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) 
           or die ("Data not found. Your SQL query didn't work... ");

// we produce XML
header("Content-type: text/xml");
$XML = "<?xml version=\"1.0\"?>\n";
if ($xslt_file) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>\n";

// root node
$XML .= "<result>\n";
// rows
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {    
  $XML .= "\t<row>\n"; 
  $i = 0;
  // cells
  foreach ($row as $cell) {
    // Escaping illegal characters - not tested actually ;)
    $cell = str_replace("&", "&amp;", $cell);
    $cell = str_replace("<", "&lt;", $cell);
    $cell = str_replace(">", "&gt;", $cell);
    $cell = str_replace("\"", "&quot;", $cell);
    $col_name = mysql_field_name($result,$i);
    // creates the "<tag>contents</tag>" representing the column
    $XML .= "\t\t<" . $col_name . ">" . 
            $cell . "</" . $col_name . ">\n";
    $i++;
  }
  $XML .= "\t</row>\n"; 
 }
$XML .= "</result>\n";

// output the whole XML string
echo $XML;
?>

Wednesday

(1) Term project

  • Short demo of the Wordpress extension "Formidable"

(2) Editing an XML file (recall)

  • Use an XML editor since it can test the well-formedness of an XML file. You may use a normal programming editor for fixing small contents in a raw XML file (that is just well-formed but not valid) ... but make sure that tags remain matched.
  • For XML that can be validated, always use an XML editor and validate.

(5) Create/adapt an XSLT file 

<?xml version="1.0"?>                
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
  <xsl:output method="html"
	      doctype-public="-//W3C//DTD HTML 4.01 Transitional//EN"/>

  <xsl:template match="result">
    <html> <head> <title> Query results </title> </head>
      <body bgcolor="#ffffff">

	<h1>Query results</h1>

	<table border="2" cellspacing="1" cellpadding="6">
          <!-- Here you might insert a table heading row
	  <tr> <th>col1</th> <th>col2</th> <th>col 3</th> <th></th> </tr>
	   -->

	  <xsl:apply-templates select="row"/>
	</table>
	<hr />

      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="row">
    <tr> <xsl:apply-templates/> </tr>
  </xsl:template>

  <!-- copy/paste the rule below and replace by custom rules for various columns of your output -->
  <xsl:template match="*">
    <td> <xsl:apply-templates/> </td>
  </xsl:template>

  <xsl:template match="col2">
  <!-- no real output -->  
  </xsl:template>

</xsl:stylesheet>

Homework 5

Objectives
  • Pull out data in XML format from a database. If you turned in homework 3, use its tables. Else you may use any other table you can find (but you need to import it to a database).
  • Produce a XSLT file that will display the data in a nice way in HTML or XHTML. You also may do some filtering, i.e. only parts of the data. Finally, you also will have to add some extra information in the XSLT (at least a meaningful title)
  • Associate the XSLT file with the XML produced by the PHP script given to you by the instructor.
Steps
  • Read PHP - MySQL - XML tutorial - basics
  • Copy file http://tecfa.unige.ch/guides/xml/examples/mysql-php-xml/mysql-to-xml-raw.php.txt to a web directory of your WAMP server (or a PHP/MySQL provider). Alternatively you may copy/paste it from here. Then call it something like mysql-to-xml.php (I.e. file must end with .php!!)
  • Change the values in the configuration section to make it work with one of your databases. You do not need to change anything else in this file.
  • Test this PHP script. In your browser, you should see some raw XML.
  • Then save the result as test.xml (for testing purposes). In your browser, open the mowes/xxx directory, then right-click on mysql-to-xml.php and "save target as" test.xml.
  • Create an XSLT file that will translate test.xml to (X)HTML.

Test it with an XML editor and the test.xml file until you are pleased with the result. Remark: The XML file will be well-formed in principle, but you are likely to make XSLT syntax mistakes. Validate the XSLT file with the Exchanger light editor.

  • Once you are done, copy this XSLT file to the directory of the PHP file.
  • If necessary, edit the configuration of the PHP file and specify the name of the XSLT file.

If the above is not clear, look at the files in this directory (it includes the test files)

XSLT - If things go wrong

Style-sheet error !

  • Validate the style-sheet in your XML editor
  • If it provides XSLT support, it will help you find the error spots

XHTML doesn’t display in Firefox !

  • Firefox wants a namespace declaration in the XHMTL produced, do it (see above).

HTML doesn’t seem to be right !

  • Transform the XML document within your XML editor and look at the HTML

In "Exchanger Lite", use Transform in the menu bar with the following parameters:

  • Transform->Execute Advanced XSLT
  • Input = current document
  • XSLT = Use Processing instructions
  • Output = to new file or to new document
  • You also may validate the output HTML with an HTML validator !

There is various unformatted text in the output

  • You forgot to write a rule for some XML element

HTML still doesn’t seem to be right !!

  • Use a XSLT debugger/tracer to understand how your XSLT executes
PHP - If something goes wrong

Blank page

  • If you get a blank page, it is very likely that you have a syntax mistake. Mowes is configured not to display PHP errors. You must have a look at the Apache log files that you can find here:
...  \mowes\apache2\logs\error.log


Submission Procedure and dates

You must upload the following files to the worldclassroom:

  • The *.xsl file
  • The *.sql file
  • The *.php file
  • This homework is due at start of Wed week 6
  • Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

See the worldclassroom.

Links and teaching materials

Software
Online software
  • (optional) A LAMP service provider
Teaching materials
Teaching materials (older slides)
Optional reading

List of all Coap 3180 pages

Weekly programs

Most important wiki pages

Other teaching resources

Most important software

Retrieved from "https://edutechwiki.unige.ch/mediawiki/index.php?title=Help:COAP-3180&oldid=25237"