Dev Explorer
Advice, tutorials and tips for beginner and experienced software/web application developers
Execute Multiple MySQL Queries from One String in PHP
Date Published: 01/03/2009 20:08A 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.
5 Most Recent Articles
A quick guide for ASP.NET developers on how to manually trigger ASP.NET events from JavaScript.
An article for users of MySQL databases describing how they can use advanced stored procedures to improve efficiently in their applications.
A guide for LAMP developers to using stored procedures in MySQL and extending the MySQLi class.
An introduction to using the xlwt and xlrd modules for python to interact with Microsoft Excel spreadsheets.
This is an introduction to making HTTP requests from a python script/application using httplib.
LeaderGL
02/03/2009 11:31
If some "string" had a ";" into it...your method crash. Example: INSERT INTO tblTable (strOne, strTwo, strThree) VALUES ( 'Hi guys, how are you? I'm fine;', 'String 2', 'String 3' ); ....
Dev Explorer
02/03/2009 14:22
That's an excellent point. Rather than a straight explode like I've done here I'll change it to a simple preg_split. I'm at work right now so I'll modify it later. Thanks for bringing this to my attension.
Dev Explorer
02/03/2009 15:44
I haved changed the previously used explode function for a regular expression I've written to combat the issue which LeaderGL kindly pointed out. If anyone else has any suggestions they feel would improve the article above, please feel free to let me know.
Luciano
24/03/2009 21:06
Your solution is great. Thanks a lot. I think there's a problem with the regular expression. "/; (?=([^'|^\']*['|\'][^'|^\']*['|\'])*[^'|^\']*[^'|^\']$)/" Because it detects all semicolons. So I tryed this: "/s; (?=([^'|^\']*['|\'][^'|^\']*['|\'])*[^'|^\']*[^'|^\']$)/" There's a necesary little trick: You have to leave a blank space before each semicolon used as separator (" ;") I don't know if it's the best solution but it worked for me.
ac1982
25/03/2009 08:36
I have question, is each query send by php using the function mysql_query a unique request to the server? If that is true then sending multiple queries to the server more than 100 queries at the same time using a for loop what will happen to the server?
Dev Explorer
25/03/2009 09:09
When mysql_query() is used in PHP the script will wait for the response from the server for each request. This means that the server will experience 100 consecutive requests as opposed to 100 concurrent requests when using a for loop.
ac1982
25/03/2009 09:16
so I was right. Do you know if mysqli have succeed in creating a function for multiple queries?
Dev Explorer
25/03/2009 09:27
I believe so yes, http://uk.php.net/mysqli_multi_query
gorkau
07/06/2009 15:20
I did the same using a little trick that works for me: $queries = explode(";n", $sql); It works for me since all my $sql statemens are separated by a semicolon and a newline character. Works also for phpmyadmin exported files.
simplifier
11/06/2009 08:36
$sql = array(); $sql[] = 'CREATE TABLE ...' $sql[] = 'INSERT INTO ... VALUES ...' -- or --- $sql = array( 'CREATE TABLE ...', 'INSERT INTO ... VALUES ...'); ------ foreach($sql as $query){ mysql_query($query); } no error prone splitting, no performance loss for regexes, just pretty, well maintainable code...
Majk
23/08/2009 13:18
how about that $queries = array(); $queries[] =
James in UK
08/09/2009 08:06
Good tip. As Majk suggests, I think building the array yourself is less prone to error and just as readable. Plus it allows you to check the result from each query... you *are* checking the result from each query aren't you (good "defensive programming" practice). $queries = $results = array(); $queries = array( "query 1.......", "query 2.......", ) foreach ($queries as $query){ if (strlen(trim($query)) > 0) { $results[] = mysql_query($query); } } A slight improvement would be to use a for loop counter with quoted array indexes (rather than foreach) to be absolutely certain the $results array lines up with the $queries. (Yes, I know it does now but in 2 or 3 years time when the code has been expanded and hacked around it might not - it's good programming practice to *never* assume anything! I've been a programmer for over 30 years and speak from experience). for ($i = 0; $i < count($queries); $i ) { if (strlen(trim($queries[$i])) > 0) { $results[$i] = mysql_query($queries[$i]); } }
Jettt
14/09/2009 22:08
Why not using array? Just push queries into stack and then pop'em until stack becomes empty... Parsing SQL is not your task, SQL server does this job for you ;)
roberto riqueza
12/10/2009 19:28
esta bueno la pagina
Scott
05/11/2009 15:57
This code seems absolutely pointless to me. Duh, I can loop through a string a parse it based on semi colons.
Marek
06/01/2010 17:09
almost great, unfortunately chockes on pipe sign inside string
Rafal
28/02/2010 22:40
works great with sql=array(); sql[]="insert into something1.....;"; sql[]="insert into something1.....;"; sql[]="insert into something1.....;"; and then: foreach($sql as $query) { mysql_query($query); } of course check response!:) Cheers guys, and thanks.