Skip to content

Multiple MySQL queries in a single string, using PHP

April 4, 2011

I recently got stung trying to execute multiple SQL “do something”-style queries in PHP e.g.

$query = "TRUNCATE TABLE `table_a`; TRUNCATE TABLE `table_b`;";
$result = mysql_query($query);

The problem is that PHP doesn’t actually support multiple queries in the same call. I kept getting “there’s an error in your syntax near”.. style error messages, and it drove me MAD – my syntax was great, and I knew it worked fine.

A brief read of the PHP.net manual for mysql_query revealed the answer (emphasis mine):

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that’s associated with the specified link_identifier.

Rather than go through the hassle of breaking my pre-existing SQL query into smaller chunks (my actual query was substantially larger than the example above), I went for the automated solution.

I hereby give you mysql_multiquery()

function mysql_multiquery($sql, $identifier = NULL)
{
  $queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
  foreach ($queries as $query) {
    if (strlen(trim($query)) > 0) 
    {
      if ($identifier) 
        mysql_query($query, $identifier);
      else
        mysql_query($query);
    }
  }
}

Important note: This function does not return results. It’s strictly for executing multiple setup or installation-style queries

Thanks to Dev-Explorer for the inspiration and the RegEx

Advertisements

From → MySQL, PHP

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: