php 页面多mysql查询_使用PHP执行多个MYSQL查询

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

解决方案

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.