Category Archives: Web Development

PHP MySQL PDO and the Multiple Queries Bug

Whilst running multiple queries in one call, via the PHP MySQL PDO driver, I noticed that although PDO was behaving as if all the queries had executed successfully, the entries in the database weren’t all appearing that should have. Obviously something was happening that wasn’t being reported. PDO was setup to throw Exceptions on any errors when the SQL executed so there was clearly a bug somewhere.

Unfortunately it’s a bug in PHP and it seems this bug has existed since at-least April 2012: https://bugs.php.net/bug.php?id=61613

I had three choices:

  1. manually split the queries up into different statements and replace the bind variables with their relevant values (which wasn’t feasible as there were hundreds),
  2. Use str_replace to replace the bind_variables with their relevant quoted values and run the query at the sql prompt (not real world situation due to null values etc.).
  3. Write some code to split the statement into individual queries and  execute said queries individually with the relevant bind variables.

The following is a full example of how to split a long string of multiple sql queries and the relevant bind variables for that query into their separate sections, and execute them one at a time:

<?php
$myConn = new PDO(
	'mysql:host=localhost;dbname=dbname',
	'username',
	'password'
);
$myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$multiSqlStatement = '
INSERT INTO users (username) VALUES (:username);
INSERT INTO groups (name, type) VALUES (:group_name, :group_type);
INSERT INTO logs (entry) VALUES (NOW());
';
$bindVariables = array(
	':group_type' => 'this_type',
	':username' => 'my_username',
	':group_name' => 'Other'
);

$tempSql = preg_replace('/[\n\r\s]+/', ' ', $multiSqlStatement);
$tempSqlParts = explode(';', $tempSql);
foreach ($tempSqlParts as $tempSqlPart) {
    $tempSqlPart = trim($tempSqlPart);
    if (!empty($tempSqlPart)) {
        $tempBindVars = (0 < preg_match_all('/\:[a-z0-9_]+/i', $tempSqlPart, $matches))
        	? array_intersect_key($bindVariables, array_flip($matches[0]))
        	: array();
        $tempStatement = $myConn->prepareStatement($tempSqlPart);
        $tempStatement->execute($tempBindVars);
        $tempStatement->closeCursor();
    }
}

Formatting a Date in PHP using ISO Format

We all know how to format a date in PHP when using the date functions, but what happens if you’ve only got the ISO format? This doesn’t work with PHP’s date functions.

Well I got this exact situation whilst using Zend_Locale in Zend Framework. Because the locale data files utilised are sourced externally the format comes back in ISO format.  A conversion function is provided (Zend_Locale_Format::convertPhpToIsoFormat) however that’s converting the wrong way and won’t help in this situation.

I went ahead and wrote a quick function which will return a formatted date using a provided ISO format, rather than PHP format.  It works like the PHP function and accepts the same parameters.

You can find the function as a gist on github:

<?php

function date_iso($format, $timestamp = null)
{
	if ($timestamp === null) {
		$timestamp = time();
	}

	$convert = array(
		'a' => 'A' , 'B' => 'B', 'D' => 'z', 'ddd' => 't', 'dd' => 'd', 'd' => 'j',
		'EEEE' => 'l', 'EE' => 'D', 'eee' => 'N', 'e' => 'w', 'HH' => 'H', 'H' => 'G',
		'hh' => 'h', 'h' => 'g', 'I' => 'I', 'l' => 'L', 'MMMM' => 'F', 'MMM' => 'M',
		'MM' => 'm', 'M' => 'n', 'mm' => 'i', 'r' => 'r', 'SS' => 'S', 'ss' => 's',
		'U' => 'U', 'ww' => 'W', 'X' => 'Z', 'YYYY' => 'o', 'yyyy' => 'Y', 'yy' => 'y',
		'ZZZZ' => 'P', 'zzzz' => 'e', 'Z' => 'O', 'z' => 'T'
	);

	$values = preg_split(
		'/(a|B|D|d{1,3}|EEEE|EE|eee|e|HH|H|hh|h|I|l|M{1,4}|mm|r|SS|ss|U|ww|X|YYYY|yyyy|yy|ZZZZ|zzzz|Z|z|[^a-zA-Z]+)/',
		$format,
		-1,
		PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
	);
	foreach ($values as $key => $value) {
		if (isset($convert[$value])) {
			$values[$key] = date($convert[$value], $timestamp);
		}
	}
	return implode($values);
}

Tidying HTML source from PHP

In June 2011 I decided the site famous to Leeds United fans leedsfans.org.uk had been down for WAY too long.  It proved an invaluable resource to Leeds United fans on the history of the club over the years, however the sites host and admin Jabba (Jon) had given up on the project for whatever reason with no immediate intention of reviving it.
I found the last copy taken of the site on archive.org and promptly wrote a simple script to scrape all of the content archive.org had stored into a folder so that I could at-least re-host the static content. I did try to contact Jabba to see if I could take over the original domain, but unfortunately I’ve had no response. I then registered the most similar yet cheap domain name I could find (leeds-fans.org.uk) and put all the content I’d obtained back on the web so people could find it again.

Anyway after looking through the source I decided to try and at-least make the HTML valid until I get a chance to eventually re-design and re-launch the site. This is relatively easy with libtidy installed on your system and php compiled with it available.

I wrote a PHP script to go though a folder of static X/HTML pages, run them through libtidy, keep the doctype and save this back to the file.

If such a script will be useful to you here’s the source also available as a gist on github:

<?php
	defined('TIDYDIR_EXTENSION') || define('TIDYDIR_EXTENSION', 'html');
	function tidyDir($directory) {
		$htmlFiles = glob($directory.DIRECTORY_SEPARATOR.'*.'.TIDYDIR_EXTENSION);
		$filenameRegEx = '#^(.+?)\.([^\.]+?)$#';
		$htmlTidy = new tidy();
		foreach ($htmlFiles as $entry) {
			if (preg_match($filenameRegEx, $entry, $matches)) {
				$filename = $matches[1];
				$extension = $matches[2];
				$htmlContents = file_get_contents($entry);
				$doctype = (preg_match('#\A\s*(\<[\s\S]+?\>)[\s\S]*#', $htmlContents, $matches))
							? $matches[1]."\n"
							: '';
				$htmlTidy->parseString($htmlContents);
				if (0 < $htmlTidy->getStatus()) {
					if ($htmlTidy->cleanRepair()) {
						$correctedHTML = $doctype.$htmlTidy->html()->value;
						echo 'saving ',$filename,'.',$extension,"\n";
						if (!file_put_contents($filename.'.'.$extension, $correctedHTML)) {
							echo 'failed saving ',$entry,"\n";
						}
					} else {
						echo 'FAILED TO CLEAN UP ',$entry,"\n";
						die;
					}
				} else {
					echo 'Goody, ',$entry,' is valid html ',"\n";
				}
			}
		}
		$d = dir($directory);
		while (false !== ($entry = $d->read())) {
			if (0 !== strpos($entry, '.') && is_dir($directory.DIRECTORY_SEPARATOR.$entry)) {
				echo 'calling tidyDir on ',$directory,DIRECTORY_SEPARATOR,$entry,"\n";
				tidyDir($directory.DIRECTORY_SEPARATOR.$entry);
			}
		}
	}
	tidyDir(__DIR__);

HTML5 Storage and Objects

Yep, HTML5’s new localStorage and sessionStorage functionality is great, you can store information you want client side, meaning you don’t have to transfer the data via XHRs to the server, or store it in cookies, or in flash storage to keep it between page views.

There is one downside however. Objects, you can’t store them. It only accepts strings, thus I did what everyone’s doing, used JSON to store the object in storage. This however is a bit, well, annoying. You’ve got to remember all the time to run a JSON.parse or JSON.stringify on the data your using or you’ll simply loose it without any errors being thrown

This lead me to simply write a wrapper for both types of storage so I didn’t have to remember wherever I was using them.

Here’s the source also available in a gist on github:

if (typeof HTML5 == 'undefined') {
	var HTML5 = {};
}
/**
 * Wrapper class to deal with easily storing values in local storage
 * without having to constantly use JSON.parse and JSON.stringify everywhere
 * you want to save an object.
 *
 * @param {String} index the base index to use in the localStorage global object
 * @author Tom Chapman
 */
HTML5.localStorage = function(index)
{
	/**
	 * @type {Mixed}
	 * @private
	 */
	var localValues;

	/**
	 * @type {String}
	 * @private
	 */
	var localIndex;

	/**
	 * Class level constructor
	 *
	 * @constructor
	 * @param {String} index
	 * @private
	 */
	var __init = function(index) {
		if (typeof index != 'string' || index === null) {
			throw new Error('A string index must be provided to HTML5.localStorage');
		}
		localIndex = index;
		var currentLocalValue = localStorage.getItem(index);
		if (typeof currentLocalValue != 'undefined' && currentLocalValue !== null) {
			try {
				localValues = JSON.parse(currentLocalValue);
			} catch (err) {
				localValues = currentLocalValue;
			}
		} else {
			localValues = {};
		}
	}(index);

	return {
		/**
		 * Returns all vars or index from the localValues
		 *
		 * @param {String} [index] the index inside the object in use
		 * @return {Mixed}
		 */
		get: function(index) {
			return (typeof index == 'undefined')
					? localValues
					: ((typeof localValues[index] != 'undefined')
							? localValues[index]
							: null);
		},

		/**
		 * Set localValues or an index in localValues
		 *
		 * @param {Mixed} value the value to assign to the object, or if index provided the index inside the object
		 * @param {String} [index] the index inside the object in use
		 * @return {Mixed}
		 */
		set: function(value, index) {
			if (typeof index == 'undefined' || index === null) {
				localValues = value;
			} else {
				if (typeof localValues != 'object') {
					throw new Error();
				}
				localValues[index] = value;
			}
			localStorage.setItem(localIndex, (typeof localValues != 'string' && typeof localValues != 'number')
													? JSON.stringify(localValues)
													: localValues);
		},

		/**
		 * Removes either the whole object from the localStorage or the index provided
		 *
		 * @param {String} [index] the index inside the object in use
		 */
		remove: function(index) {
			if (typeof index == 'undefined') {
				localStorage.removeItem(localIndex);
			} else if (typeof localValues[index] != 'undefined') {
				delete localValues[index];
				localStorage.setItem(localIndex, (typeof localValues != 'string' && typeof localValues != 'number')
													? JSON.stringify(localValues)
													: localValues);
			}
		}
	};
};
if (typeof HTML5 == 'undefined') {
	var HTML5 = {};
}
/**
 * Wrapper class to deal with easily storing values in session storage
 * without having to constantly use JSON.parse and JSON.stringify everywhere
 * you want to save an object.
 *
 * @param {String} index the base index to use in the localStorage global object
 * @author Tom Chapman
 */
HTML5.sessionStorage = function(index)
{
	/**
	 * @type {Mixed}
	 * @private
	 */
	var sessionValues;

	/**
	 * @type {String}
	 * @private
	 */
	var sessionIndex;

	/**
	 * Class level constructor
	 *
	 * @constructor
	 * @param {String} index
	 * @private
	 */
	var __init = function(index) {
		if (typeof index != 'string' || index === null) {
			throw new Error('A string index must be provided to HTML5.sessionStorage');
		}
		sessionIndex = index;
		var currentLocalValue = sessionStorage.getItem(index);
		if (typeof currentLocalValue != 'undefined' && currentLocalValue !== null) {
			try {
				sessionValues = JSON.parse(currentLocalValue);
			} catch (err) {
				sessionValues = currentLocalValue;
			}
		} else {
			sessionValues = {};
		}
	}(index);

	return {

		/**
		 * Returns all vars or index from the sessionValues
		 *
		 * @param {String} [index] the index inside the object in use
		 * @return {Mixed}
		 */
		get: function(index) {
			return (typeof index == 'undefined')
					? sessionValues
					: ((typeof sessionValues[index] != 'undefined')
							? sessionValues[index]
							: null);
		},

		/**
		 * Set sessionValues or an index in sessionValues
		 *
		 * @param {Mixed} value the value to assign to the object, or if index provided the index inside the object
		 * @param {String} [index] the index inside the object in use
		 * @return {Mixed}
		 */
		set: function(value, index) {
			if (typeof index == 'undefined' || index === null) {
				sessionValues = value;
			} else {
				if (typeof sessionValues != 'object') {
					throw new Error();
				}
				sessionValues[index] = value;
			}
			sessionStorage.setItem(sessionIndex, (typeof sessionValues != 'string' && typeof sessionValues != 'number')
													? JSON.stringify(sessionValues)
													: sessionValues);
		},

		/**
		 * Removes either the whole object from the sessionStorage or the index provided
		 *
		 * @param {String} [index] the index inside the object in use
		 */
		remove: function(index) {
			if (typeof index == 'undefined') {
				sessionStorage.removeItem(sessionIndex);
			} else if (typeof sessionValues[index] != 'undefined') {
				delete sessionValues[index];
				sessionStorage.setItem(sessionIndex, (typeof sessionValues != 'string' && typeof sessionValues != 'number')
													? JSON.stringify(sessionValues)
													: sessionValues);
			}
		}
	};
};