Dev Explorer

Execute Multiple MySQL Queries from One String in PHP

Date Published: 01/03/2009 20:08

A common problem when building database driven web applications with LAMP (Linux, Apache, MySQL and PHP) is that the MySQL functions to execute SQL queries (mysql_query, mysql_unbuffered_query) do not allow the execute of multiple queries from one string. In other database technologies it is possible to define multiple separate queries within one string by simply separating them with a semicolon (;) but with LAMP this is not possible. Writing out individual function calls can often feel somewhat heavy handed and leave your code looking like a mesh of SQL and function calls. To combat this problem I have written a very simple, elegant solution using an array and a for each loop.

How its done

To execute multiple queries with ease simply write out all your queries into one string separated by semicolons (;). This allows you to keep all your queries in one place and lay them out as you please. This string can then be split by the semicolon using a simple regular expression. The regular expression used ignores semicolons that are within strings inside the query along with semicolons within strings within those strings. To execute the queries use a for each loop to iterate through the string array and execute each part individually. Here is an example of this idea in practice executing various SQL statements.

<?php
$sql = "
CREATE TABLE tblTable (
strOne VARCHAR(50) NOT NULL,
strTwo VARCHAR(50) NOT NULL,
strThree VARCHAR(50) NOT NULL
);
INSERT INTO tblTable
(strOne, strTwo, strThree)
VALUES ('String 1', 'String 2', 'String 3');
UPDATE tblTable
SET
strOne = 'String One',
strTwo = 'String Two'
WHERE strThree = 'String 3';
";
$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
foreach ($queries as $query){
   if (strlen(trim($query)) > 0) mysql_query($query);
}
?>

Its as easy and as simple as that. No need for function call after function call cluttering up your code files just keep all your SQL grouped together in one string. Using this method also means you can add or remove queries without having to rewrite all the function calls.

Comments

Sorry comments are currently disabled for maintenence

HOME
ABOUT ME
CONTACT ME