Help:COAP-3180/week5: Difference between revisions

The educational technology and digital learning wiki
Jump to navigation Jump to search
Line 42: Line 42:
/* Made by Daniel K. Schneider, TECFA Jan 2010. This is freeware
/* Made by Daniel K. Schneider, TECFA Jan 2010. This is freeware
   Will connect to a MySQL database, execute an SQL statement and
   Will connect to a MySQL database, execute an SQL statement and
   then return the result as valid XML
   then return the result as valid XML.
  */
  */
error_reporting(E_ALL);
error_reporting(E_ALL);
Line 48: Line 48:
// ---------------------------  Configuration section
// ---------------------------  Configuration section


// Fill in according to your db settings
// Fill in according to your db settings (example is for a local and locked "play" WAMP server)
// Make sure to keep the ; and the ""
// Make sure to keep the ; and the ""
$host      = "localhost";
$host      = "localhost";
Line 54: Line 54:
$pass      = "";
$pass      = "";
$database  = "exam";
$database  = "exam";
// replace by a real *.xsl file, e.g.
// $xslt_file = "exam.xsl";
$xslt_file  = FALSE;
// If true, will output XML without XSLT
$raw        = TRUE;


// Replace by a query that matches your database
// Replace by a query that matches your database
$SQL_query = "SELECT * FROM question,response_item WHERE question.id = response_item.id_question";
$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
// -------------------------- no changes needed below
Line 70: Line 68:
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");


$left = "<";
// we produce XML
$right = ">";
header("Content-type: text/xml");
 
$XML = "<?xml version=\"1.0\"?>\n";
if ($xslt_file or $raw) {
if ($xslt_file) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>\n";
  // we produce XML
  header("Content-type: text/xml");
  $XML = "<?xml version=\"1.0\"?>\n";
  if (!$raw) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>";
}
else {
  // we produce HTML. All XML tags are replaced by printable entities
  $XML = "Don't forget to create an XSLT file .... <p>";
  $XML .= "<pre>\n";
  $left = "&lt;";
  $right = "&gt;";
}


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


  $XML .= $left . "/result" . $right . "\n";
// output the whole XML string
 
echo $XML;
echo $XML;
if (!$xslt_file && !$raw) echo "</pre>";
?>  
?>  
</source>
</source>


Revision as of 22:16, 8 February 2010

Week 5 - COAP 3180

ATTENTION: Monday week 6 class is cancelled. Replacement has to be negociated..

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
  • Demonstration of the Wordpress extension "Formidable"
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

(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 this code to a mowes/web/ directoty
  • call it for example: mysql-to-xml.php
  • configure and test
<?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;
?>

(4) Edit an XML file (Wednesday)

(5) Modify an XSLT file

  • Filter data with XSLT

Homework 5

Details to be announced on Wednesday ...

  • Pull out data in XML format from a database
  • Produce an XSLT file that will display the data (any maybe do some filtering)
  • Associate the XSLT file either server-side or client side.

The instructor will provide a php file that does the extraction.

Submission Dates

  • This homework is due at start of wed week 6
  • 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

Tips for the homework

Links and teaching materials

Software
  • A good XML editor, e.g. XML exchanger light
Online software
  • none
Teaching materials
Teaching materials (older slides)
Optional reading
Retrieved from "https://edutechwiki.unige.ch/mediawiki/index.php?title=Help:COAP-3180/week5&oldid=25239"