Multiple SQL queries using MySQL and PHP

Multiple SQL queries using MySQL and PHP

Something that I’ve had problems with using MySQL/PHP is the limitation of only being able to run one line of SQL at a time. Using something such as Microsoft SQL Server it’s possible to write multiple lines of SQL and run it all in a single database call. Until stored procedures (in MySQL 5) are available (i.e. when it seems that the database engine is ready for a live environment) I’ve put together the following static method ‘hack’ using PHP’s exec() function (assuming you’re using PHP 5’s object syntax – otherwise just paste the code into a regular function):

class MySQLInterface
{
   public static function multipleDbQueries($sql)
   {
      $file = fopen(TEMP_CSV_LOCATION .
         "temp_query.sql","a+");
      fwrite($file,$sql);
      fclose($file);
      exec("mysql -u " . DB_USERNAME .
         " --password=='" . DB_PASSWORD .
         "' " . DB_NAME . " < " .
         TEMP_CSV_LOCATION .
         "temp_query.sql");
   }
}

What this allows you to do is to pass in a SQL string and have it executed as if it was being run from the command line. This is especially useful if you need to disable foreign keys for some reason. e.g

$sql = "SET FOREIGN_KEY_CHECKS=0;
   drop table oldTable;
   SET FOREIGN_KEY_CHECKS=1;"

MySQLInterface::multipleDbQueries($sql)

Downsides of this are

  • Requires command line access to mysql
  • Liable to SQL injection

Since i’m working within an internal system I have control over both of these and the code seems to work particularly well.

0 Replies to “Multiple SQL queries using MySQL and PHP”

  1. Hi, I had to use the same method for executing multiple MySQL statements with PHP from a dump file. OK, it works, but how do you handle MySQL errors? What I do:exec(“mysql -v -u user -ppass dbname < rcfile.sql",$output,$how);So, it works but I get the same output in the browser – even when MySQL error acquires?

  3. and giving the file some unique filename and deleting it at the end of the function would be nice for avoiding overwriting issues. Anyway, the exec() command is usually turned out for security reasons. Any other ideas?

Leave a Reply