使用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 服务器版本相对应的手册，以在第 1 行的UPDATE tmp SET id=100 WHERE id = 1INSERT INTO event_categoriesBU SELECT * FROM t"附近使用正确的语法

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屋！