MySQL to Structured Data (EXPERIMENT)

README.md

Found this little experiment in the depths of one of my projects; I've removed it and dumped it here in case I want it in the future

mysqltest.php

<?php
	/**
	 * MYSQL TEST
	 * 
	 * A test to see if we can do complex, nested JOINS and output JSON data to match it
	 *
	 * The assumptions
	 *		the database is properly relational
	 *		we're joining on PKs (that might be important later)
	 *		we want to go multiple tables deep
	 */

	// Need to measure things
	function getTime() {
		$a = explode (' ',microtime());
		return(double) $a[0] + $a[1];
	}

	// Texty outputs
	header('Content-type: text/plain');
	
	// Connect to the database
	$DBH = new PDO('mysql:host=localhost;dbname=database;charset=UTF-8', 'username', 'password');

	/**
	 * Static test, single depth
	 *
	 *	jobs
	 *		office
	 *		contract
	 *		invoice
	 *		site
	 */

	// Time it
	$Start = getTime();
	// This is what we want
	$SQL = 'SELECT j.*, o.*, c.*, i.*, s.* FROM `jobs` AS j
				LEFT JOIN offices   AS o ON o.id = j.office
				LEFT JOIN contracts AS c ON c.id = j.contract
				LEFT JOIN invoices  AS i ON i.id = j.invoice
				LEFT JOIN sites     AS s ON s.id = j.site
			LIMIT 1;';
	$STH = $DBH->prepare($SQL);
	$STH->execute();

	// We can see the table and name for every column by looking at the meta
	$tables	= array();
	$names	= array();
	foreach(range(0, $STH->columnCount() - 1) as $column_index) {
		$tmp		= $STH->getColumnMeta($column_index);
		$tables[]	= $tmp['table'];
		$names[]	= $tmp['name'];
	}

	// Now we know what the columns are, we can rebuild our object
	$rows    = $STH->fetchAll(PDO::FETCH_NUM);
	$objects = array();
	foreach($rows as $row){
		$object = array();
		foreach($row as $k => $v){
			// The table and column name
			$table	= $tables[$k];
			$name	= $names[$k];
			// Which table?
			if($table == 'j'){
				// If the table is the job, it's in the "ROOT"
				$object[$name] = $v;
			} else {
				// Otherwise, it's a child
				$object[$table][$name] = $v;
			}
		}
		$objects[] = $object;
	}
	print_r($objects);

	// How long did that take?
	$End = getTime();
	echo "Time taken = ".number_format(($End - $Start),10)." secs";