使用PHP执行多个MYSQL查询 [英] Using PHP to execute multiple MYSQL Queries

问题描述

我正在尝试使用PHP运行连续的MYSQL语句，如下面的代码片段所示(该代码片段仅将一行复制到另一行，并通过tmp表重命名id).

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).

我收到重复的语法错误消息.我已经尝试了无数次迭代.而且代码看起来像是我在PHP手册中研究的代码，以及关于SO的其他myql问题(不包括php维度).

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).

任何人都可以阐明为什么我的php语法不正确吗?

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消息后退- 建立表格时发生错误:您的SQL语法有错误；检查与您的MariaDB服务器版本相对应的手册，以在'UPDATE tmp SET id = 100 WHERE id = 1INSERT INTO event_categoriesBU SELECT * FROM t'的第1行附近使用正确的语法

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.

您可以这样做:

$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.

您不使用multi_query的原因是因为该函数不支持占位符值.如果需要在此查询中引入某种类型的用户数据，则需要使用bind_param以便安全地进行操作.没有占位符值，您将面临SQL注入错误，并且其中一个错误足以使您的整个应用程序容易受到攻击.

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.

值得注意的是，PDO比mysqli更具灵活性和适应性，因此，如果您对mysqli的投入不多，则值得考虑进行切换.

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.

这篇关于使用PHP执行多个MYSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！