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

Leave a Reply