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

MySQL Logo with Black Background

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

5 Most Recent Articles

Manually Triggering Events in ASP.NET from JavaScript

A quick guide for ASP.NET developers on how to manually trigger ASP.NET events from JavaScript.

Advanced Use of MySQL Stored Procedures

An article for users of MySQL databases describing how they can use advanced stored procedures to improve efficiently in their applications.

Using MySQL Stored Procedures and Extending MySQLi in PHP

A guide for LAMP developers to using stored procedures in MySQL and extending the MySQLi class.

Reading and Writing to Excel Spreadsheets in Python

An introduction to using the xlwt and xlrd modules for python to interact with Microsoft Excel spreadsheets.

Interact with the Web Using Python and the HTTP Library

This is an introduction to making HTTP requests from a python script/application using httplib.

Sponsors