bug in parsing SQL commands

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jasongr
Forum Contributor
Posts: 206
Joined: Tue Jul 27, 2004 6:19 am

bug in parsing SQL commands

Post by jasongr »

Hello

I tried to write a function that takes a parameter content of an SQL file and executes the statements in it, one after the other.
Here is an example of such content

Code: Select all

# Drop all the tables 

DROP TABLE IF EXISTS `table1`;
DROP TABLE IF EXISTS `table2`;

# Create empty tables

CREATE TABLE `table1` (
  `ID` TINYINT(1) unsigned NOT NULL,
  `Param` varchar(255) default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `table2` (
  `ID` TINYINT(1) unsigned NOT NULL,
  `Param` varchar(255) default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`ID`, `Param`) VALUES (1, 'ABC');
INSERT INTO `table2` (`ID`, `Param`) VALUES (1, 'abc');
Here is my function:

Code: Select all

function runBatchOnConnection($connection, $batchContent) {
  $commands = explode(';', $batchContent);
  foreach ($commands as $command) {
    $command = trim($command);
    if ($command == '') {
      continue;
    }
    $command = stripslashes($command);
    $result = mysql_query($command, $connection);
    if ($result === false) {
      trigger_error("Error in executing query: " . $command . "\r\n" .  mysql_error($connection), E_USER_ERROR);		
      exit;
    }
  }		
}
The problem with this function is that it fails on an SQL statement which contains a ';'. For example:

Code: Select all

INSERT INTO `table2` (`ID`, `Param`) VALUES (2, ';');
Can anyone show me how it could be fixed?

regards
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

2 solutions: regular expressions, or use the [mysql_man]LOAD[/mysql_man] facilities.
Post Reply