Help:COAP-3180/week5

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

• copy code below to a mowes/www/ directoty
• call the file for example: mysql-to-xml.php
• configure and test
• Alternatively, get it from: http://tecfa.unige.ch/guides/xml/examples/mysql-php-xml/mysql-to-xml-raw.php.txt (and rename the file to *.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

• Simple XSLT (adaptation of some code)
• Filtering data with XSLT, e.g. creating rules that will do nothing
• Copy/paste the following code to a file called mysql-result.xsl. This file must sit next to the PHP file above.
• Alternatively, get this from http://tecfa.unige.ch/guides/xml/examples/mysql-php-xml/mysql-result-template.xsl

<?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)

• http://tecfa.unige.ch/guides/xml/examples/mysql-php-xml/

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

• A decent enough XML editor, e.g. XML exchanger light
  • Instructions for download and installation
• Apache/PHP/MySQL

Online software

• (optional) A LAMP service provider

Teaching materials

• XML (exam topic, must know)
• DTD tutorial (understand how to use/read a DTD only)
• XSLT Tutorial - Basics (exam topic, must know)
• XPath tutorial - basics (exam topic, must know partly)
• XQuery tutorial - basics (week 6 preview, exam topic, must know)
• PHP-MySQL tutorial -basics (good to know, but will not be tested)
• PHP - MySQL - XML tutorial - basics (must be able to use this code, i.e. configure)

Teaching materials (older slides)

• http://tecfa.unige.ch/guides/te/files/xml-intro-edit.pdf
• http://tecfa.unige.ch/guides/te/files/xml-ns.pdf (optional)
• http://tecfa.unige.ch/guides/te/files/xml-xslt.pdf (exam topic)

Optional reading

• Donald Bourret (2005). XML and Databases, http://www.rpbourret.com/xml/XMLAndDatabases.htm
• Darshan Singh, Essential XQuery - The XML Query Language, http://www.yukonxml.com/articles/xquery/
• XQuery http://en.wikipedia.org/wiki/XQuery (Wikipedia)