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

use $conn->multi_query($sql) php.net/manual/en/mysqli.multi-query.php — Michael Eugene Yuen
– Michael Eugene Yuen, Commented May 5, 2018 at 15:14

tadman · Accepted Answer · 2018-05-05 15:29:37Z

10

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.

edited May 5, 2018 at 15:29

answered May 5, 2018 at 15:20

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jimshot Over a year ago

PDO doesn't support multiple statements as far as I know, So are you suggesting not using multi statements at all?

jimshot Over a year ago

Yeah its too big a job switching to PDO now. Its a legacy that would cost a fortune to change

tadman Over a year ago

That's a feature, not a bug. The multi_query function has been the culprit behind a number of high-profile security breaches in the past in part because not only can you SQL inject things, but you can add in as many statements as you want, making it the perfect playground for attackers. Don't use multi_query for that reason alone, but the other is you must check the success of each statement you run before moving on to the next.

tadman Over a year ago

mysqli can be used safely, but it's just not as friendly to use. bind_param, for example, needs its parameters spelled out carefully. PDO's generic execute function can take an array, making it much easier to use when dealing with optional arguments.

Daniele Murer · Accepted Answer · 2018-05-05 15:35:02Z

1

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.

edited May 5, 2018 at 15:35

answered May 5, 2018 at 15:15

Daniele Murer

2473 silver badges9 bronze badges

6 Comments

tadman Over a year ago

This funciton is extremely dangerous and should not be used.

Daniele Murer Over a year ago

Alternatively, you can also use mysqli_multi_query($connection, $sql);. Documentation @ w3schools.

tadman Over a year ago

mysqli_multi_query is the procedural alter-ego of exactly the same function. You've already linked to the documentation.

jimshot Over a year ago

@tadman why dont you tell everyone why it is extremely dangerous. Because its a concatenation?

Precious Tom Over a year ago

You can use multi_query if you properly escaped strings you can mysqli_real_escape_string($conn, $sql)

|

Collectives™ on Stack Overflow

Using PHP to execute multiple MYSQL Queries

2 Answers 2

4 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Linked

Related