Category Archives: PHP

MySQL, PHP, Web Development

PHP MySQL PDO and the Multiple Queries Bug

Leave a comment

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();
    }
}

PHP, Web Development, Zend Framework

Formatting a Date in PHP using ISO Format

Leave a comment

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);
}

PHP, Web Development

Tidying HTML source from PHP

Leave a comment

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__);