ATWD1 Assignment: Code Example (2)

Mapping the data to MySQL

It would have been nice to move on to XSLT, XPATH and XQUERY but it was noticed that many of you are still trying (and struggling) to crunch the original CSV file to feed MySQL.

Let's take a step back to the suggested ER model and consider why this may not be a good idea.

Can you notice some of the design decisions and subtleties in this model?

• field names are short and unique across the model
• foreign key names are reflections of primary key names
• the year is included as part of a composite primary key in the ward_tmode entity - why?

Once the table (SQL) schemas have been mapped to MySQL (use workbench), we will need a program to generate & execute the INSERT statements.

But there's a problem getting at the data given the structure of the original CSV file.

Pulling the (unique) ward details and (unique) transport types from the CSV file isn't too difficult but generating the inserts for the join table "ward_tmode" is much trickier. We will have to generate keys.

Or is it?

We could insist that our first instinct was right and stick with the original CSV file. Processing this isn't impossible after all. We could use fancy PHP or MySQL counters and loops and regular expressions and (possibly) end up writing lots of spaghetti code.

However the effort wouldn't be effective or energy efficient. Remember that according to Larry Wall, one of the three great virtues of a programmer is Laziness. "The quality that makes you go to great effort to reduce overall energy expenditure." Or as we say here in England, "leaving time for the Pub".

We already have a DOM structured document transport_v1.xml holding the data and this (structured) format makes our job of feeding MySQL much easier.

Consider the following script using XPath 1.0 & PDO with prepared statements:

	<?php
	# important
	@date_default_timezone_set("GMT");
	# create PDO object and intialise
	$db = new PDO('mysql:host=localhost;dbname=bristol_transport;charset=utf8', 'root', '');
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
	# create a simpleXML object with file as input
	$xml = simplexml_load_file("transport_v1.xml");
	############### POPULATE ward TABLE ##################################
	# set record counter
	$rec = 0;
	# get a array of wards (unique now)
	$wards = $xml->xpath("//ward");
	# empty the ward table & reset AUTO_INCREMENT
	$stmt = $db->query('TRUNCATE TABLE ward');
	# populate the ward table setting ward_id = $rec
	foreach ($wards as $ward) {
	# increment record counter
	$rec++;
	# insert data
	$stmt = $db->prepare("INSERT INTO ward(`ward_id`, `name`, `lat`, `long`) VALUES(?, ?, ?, ?)");
	$stmt->execute(array($rec, $ward['name'], $ward['lat'], $ward['long']));
	}
	############### POPULATE tmode TABLE #################################
	#re-set record counter
	$rec = 0;
	# get a array of transport modes
	$modes = $xml->xpath("//ward[1]/mode");
	# empty the tmode table & reset AUTO_INCREMENT
	$stmt = $db->query('TRUNCATE TABLE tmode');
	# populate the tmode table setting ward_id = $rec
	foreach ($modes as $mode) {
	# increment record counter
	$rec++;
	# insert data
	$stmt = $db->prepare("INSERT INTO tmode(`tmode_id`, `mode`) VALUES(?, ?)");
	$stmt->execute(array($rec, $mode['type']));
	# build up look-up array for for tmode_id
	$tmode[$rec] = $mode['type'];
	}
	############### POPULATE ward_tmode TABLE ############################
	#re-set record counter
	$rec = 0;
	# get the year value
	$year = $xml->xpath("/transport/@year")[0];
	# empty the ward_tmode table & reset AUTO_INCREMENT
	$stmt = $db->query('TRUNCATE TABLE ward_tmode');
	# populate the ward_tmode table setting id_ward = $rec &
	# using the look-up array $tmode to find id_tmode
	foreach ($wards as $ward) {
	# increment record counter
	$rec++;
	# get a array of all mode entities in the current ward
	$ward_modes = $xml->xpath("//ward[" . $rec . "]/mode");
	# populate table ward_tmode
	foreach ($ward_modes as $wm) {
	# look up key value for id_tmode
	$key = array_search((string) $wm['type'], $tmode);
	# insert data
	$stmt = $db->prepare("INSERT INTO ward_tmode(`id_ward`, `id_tmode`, `year`, `percent`) VALUES(?, ?, ?, ?)");
	$stmt->execute(array($rec, $key, $year, $wm['percent']));
	}
	}
	# close db connection
	$db=null;
	# all done
	?>

view raw xml_to_db.php hosted with ❤ by GitHub

Deconstruct the script for questions, clarifications and criticisms. We will allocate 10 minutes at the begining of Monday's lecture for this purpose.

---

home url: http://www.cems.uwe.ac.uk/~p-chatterjee/2015-16/modules/atwd1/assignment/examples/assignmentcodeexample2.html