MySQL multiple statements separated by; in one mysql_query()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL multiple statements separated by; in one mysql_query()

Post by batfastad »

Hi everyone

This has been driving me insane for the past few hours and I just can't see why this is happening.

Basically I have a table in our intranet database which is a magazine production schedule.
And people can edit the dates (stored as Unix timestamps) whenever they need to.

But I'm trying to get it so that these changes can be replicated on our various website schedules which are stored in an almost identical table in a database on our web host's server.

My idea was to write a script that does the following...
  • - Gets the desired production schedule data from the intranet database
    - Loop through those records appending to a variable with each iteration to construct a big MySQL query - with each statement separated with a ;
    - Truncate the table on the remote database and perform the big query
But I'm getting MySQL syntax errors #1064 at a random point within the 2nd row of the query.

Basically my script loops through the local records and builds a query which eventually looks like this...

Code: Select all

TRUNCATE TABLE `online_production_schedule`;
INSERT INTO `online_production_schedule` SET `publication`="Magazine 10, April 2007 - \'Show\' Review 1", `art_stamp`=1173398400, `publication_stamp`=1174867200;
INSERT INTO `online_production_schedule` SET `publication`="Fashion 23, Spring", `art_stamp`=1174003200, `publication_stamp`=1175817600;
INSERT INTO `online_production_schedule` SET `publication`="IDN 51, April", `art_stamp`=1174003200, `publication_stamp`=1175817600;
INSERT INTO `online_production_schedule` SET `publication`="Magazine 11, May 2007 - \'Show\' Review 2", `art_stamp`=1174003200, `publication_stamp`=1177632000;
INSERT INTO `online_production_schedule` SET `publication`="AMD 94, May - REVIEW part 2", `art_stamp`=1174608000, `publication_stamp`=1178236800;
INSERT INTO `online_production_schedule` SET `publication`="Magazine 12, June 2007 - \'Show\' Review 3", `art_stamp`=1175817600, `publication_stamp`=1180310400;
INSERT INTO `online_production_schedule` SET `publication`="IDN 52, May", `art_stamp`=1175817600, `publication_stamp`=1177632000;
INSERT INTO `online_production_schedule` SET `publication`="AMD 95, June", `art_stamp`=1176422400, `publication_stamp`=1180310400;
INSERT INTO `online_production_schedule` SET `publication`="IDN 53, June", `art_stamp`=1177027200, `publication_stamp`=1180310400;
INSERT INTO `online_production_schedule` SET `publication`="Fashion 24, Summer", `art_stamp`=1177027200, `publication_stamp`=1180310400;
INSERT INTO `online_production_schedule` SET `publication`="Magazine 13, July 2007", `art_stamp`=1178236800, `publication_stamp`=1182729600;
The actual query is exactly the same as above, but will be dealing with up to 100 rows and no more. I thought this would give adequate performance as the script will only be run once every few weeks when updates are made to the database

The variables from the local table are all run through mysql_real_escape_string() before being added to this main query var and no matter how I look at the query, it looks fine.

Also if I just copy and paste this query into phpMyAdmin on the remote server and run it, it works fine!

I'm thinking now it might be a problem with how I'm executing the query within PHP...

Code: Select all

// CONNECT TO REMOTE SQL SERVER FOR ONLINE SCHEDULE
mysql_connect('mysql.domain.com', 'username', 'password');
@mysql_select_db('database_name') or die('Unable to select database');

// SQL QUERY FOR ONLINE SCHEDULE UPDATE
$sql_result = mysql_query($sql_query);
$sql_error_msg = mysql_error();
$sql_error_code = mysql_errno();
Is there a problem with having multiple MySQL statements separated by the semi-colon when using PHP's mysql_query() function?

I thought that using the semi-colon to separate SQL statements was valid, but perhaps it just doesn't work within that PHP function.

Is there another way I can execute this query in one go?
I assume that looping through the records and each time running just one query in PHP is a pretty inefficient way of doing things.

Thanks
Ben
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You didn't consult the manual :(
http://de2.php.net/mysql_query wrote:mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
But take a look at http://de2.php.net/mysqli_multi_query
Or e.g. at the code phpmyadmin is using to split the querries.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

Ah ok, that'll explain it.

On the manual page for mysql_query() there is the following function which I have got working to solve the problem...

Code: Select all

function mysql_exec_batch ($p_query, $p_transaction_safe = true) {
  if ($p_transaction_safe) {
      $p_query = 'START TRANSACTION;' . $p_query . '; COMMIT;';
    };
  $query_split = preg_split ("/[;]+/", $p_query);
  foreach ($query_split as $command_line) {
    $command_line = trim($command_line);
    if ($command_line != '') {
      $query_result = mysql_query($command_line);
      if ($query_result == 0) {
        break;
      };
    };
  };
  return $query_result;
}
It does have the limitation that each individual query can't contain a semi-colon, but it seems to be working perfectly for my uses.


Thanks for the suggestions and info
Ben
Post Reply