I was writing some code earlier today that involved writing data to two separate MySQL tables. The second INSERT statement needed to contain the automatically generated ID (auto_increment) of the first INSERT statement, so I wanted all the queries to run one after another.
Thinking it made the most sense to just build one long query and execute it all at once, I wrote code similar to the following:
// Build a query with multiple INSERT statements $q = "INSERT INTO sessions VALUES(NULL, '$name', '$desc', '$stime');"; $q .= "INSERT INTO events VALUES(LAST_INSERT_ID(), '$event', '$e_desc');"; // Execute query mysql_query($q, $conn) or die(mysql_error());
Upon running the code I received this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO events VALUES(LAST_INSERT_ID(), '24', 'my event1', 'button')' at line 1
So, as I normally do when this kind of problem arises, I echoed the query that was being executed and, hoping to get more information on the error, I ran it directly from phpMyAdmin. Here is the SQL I ran:
INSERT INTO sessions VALUES(NULL, 'Raam', 'example', '2008-04-24 21:59:08'); INSERT INTO events VALUES(LAST_INSERT_ID(), '24', 'my event1', 'button');
Your SQL query has been executed successfully
OK, so my SQL is fine.
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 .
Ah, so multiple queries are not supported with the
mysql_query() function. That's most likely a security feature, but quite annoying none the less. The bottom line is, you cannot run multiple queries with
PHP5 has the
mysqli_multi_query() function, which does allow you to run multiple queries (I know, I know, I should be coding for PHP5 by now).