Using PHP to execute multiple MYSQL Queries

Question

I am trying to use PHP to run consecutive MYSQL statements as shown in the code snippet below (which just copies one row to another and renames the id via a tmp table).

I am getting a repeated syntax error message. I've tried numerous iterations. And the code looks like code I've researched in the PHP Manual and other myql questions on SO (which do not include the php dimension).

Can anyone shine a light on why my php syntax is incorrect?

 include("databaseconnect.php");// This obviously works. Used a zillion time

$sql ="CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id 
 = 1;";
$sql.="UPDATE tmp SET id=100 WHERE id = 1;";
$sql.="INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100;";


if ($conn->query($sql) === TRUE) 
 {
  echo "Table row copied successfully. Do something with it";
 } 
 else 
 {
  echo "Error creating table: " . $conn->error;
  //close connection etc
 }

PHP Message back:

Error creating table: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tmp SET id=100 WHERE id = 1INSERT INTO event_categoriesBU SELECT * FROM t' at line 1

Answer 1

Don't run a bunch of queries at once. Usually the success of one depends on all the other operations having been performed correctly, so you can't just bulldozer along as if nothing's gone wrong when there's a problem.

You can do it like this:

$queries = [
  "CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id = 1",
  "UPDATE tmp SET id=100 WHERE id = 1",
  "INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100"
];

foreach ($query as $query) {
  $stmt = $conn->prepare($query);
  $stmt->execute();
}

Don't forget to enable exceptions so that any query failures will stop your process instead of the thing running out of control.

The reason you don't use multi_query is because that function does not support placeholder values. Should you need to introduce user data of some kind in this query you need to use bind_param in order to do it safely. Without placeholder values you're exposed to SQL injection bugs, and a single one of those is enough to make your entire application vulnerable.

It's worth noting that PDO is a lot more flexible and adaptable than mysqli so if you're not too heavily invested in mysqli, it's worth considering a switch.

Answer 2

You can't execute more than one SQL statement with query($sql), you must use multi_query($sql). Your script will then become something like this:

if ($conn->multi_query($sql) === TRUE) 
{
    echo "Table row copied successfully. Do something with it";
} 

See the documentation for a complete example.

---

However, note that, as other users have well explained in the comments, executing multiple queries at the same time with this method can be potentially dangerous and should be avoided when possible, especially when handling user inputs.

You have better control of the execution flow if you execute one query at a time and check its result, rather than grouping all of them in a single call.