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.

Advertisements
This entry was posted in MySQL, PHP, Programming. Bookmark the permalink.

3 Responses to Multiple SQL queries using MySQL and PHP

  1. Anonymous says:

    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?

  2. and giving a unique filename and then deleting the file would be nice as well, for avoiding overwriting issues

  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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s