Performing multiple MySQL queries in PHP

August 7th, 2010 by Alex Leave a reply »

Yes, i mean multiple. Usually this is used when you have for ex. big export file from phpMyAdmin with multiple CREATE TABLEs, INSERTs etc.
I came across the solution provided with some website i can’t remember
Basically, split the queries at the point where there are semicolons and query each split.

$sql = " 
CREATE TABLE IF NOT EXISTS `zcta` ( `zip` char(5) NOT NULL, `city` varchar(64) default NULL, PRIMARY KEY  (`zip`)) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
INSERT INTO `zcta` (`zip`, `city`) VALUES
('00211', 'Portsmouth'),
('00212', 'Portsmouth');
";

Something like that, now we split the string where the ; is and make sure it isn’t the value inside the query (this weird regexp does it all)

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

Comments are closed.