为什么当我将多个查询发送到 mysqli_query 时会发生错误? [英] Why error occurs when I send multiple queries into mysqli_query?

问题描述

同样的请求在Adminer中没有错误，但是在php中是

The same request in the Adminer has no errors, but in php is

您的 SQL 语法有错误；检查手册对应于您的 MariaDB 服务器版本以使用正确的语法靠近 'SET @lastID = last_insert_id();插入p_messages(letter_id, user_id, messa' 在第 1 行).

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 'SET @lastID = last_insert_id(); INSERT INTO p_messages(letter_id, user_id, messa' at line 1).

PHP:

$DB->query("INSERT INTO p_letters(user_1_id, user_1_name, create_date) VALUES ('".htmlspecialchars($accountId)."', '".htmlspecialchars($username)."', now()); SET @lastID = LAST_INSERT_ID(); INSERT INTO p_messages(letter_id, user_id, message) VALUES (@lastID, '".htmlspecialchars($accountId)."', '".htmlspecialchars($text)."');");

SQL:

INSERT INTO p_letters(user_1_id, user_1_name, create_date) VALUES ('acc583bfa62de6f66.05116379', '212312313', now()); SET @lastID = LAST_INSERT_ID(); INSERT INTO p_messages(letter_id, user_id, message) VALUES (@lastID, 'acc583bfa62de6f66.05116379', 'Проверка');

推荐答案

您应该使用单独的 API 调用来运行查询.

You are supposed to run your queries with separate API calls.

$DB->query("INSERT INTO ...");
$DB->query("SET @lastID = LAST_INSERT_ID()");
$DB->query("INSERT INTO ...");

请注意，这里实际上不需要第二个查询，因为可以直接使用 LAST_INSERT_ID().

note that you don't actually need the second query here as LAST_INSERT_ID() can be used directly.

此外，对于任何数据库交互，您都不应该使用名为HTML 特殊字符"的函数.您必须改用准备好的语句.

Besides, you should never use a function named "HTML speacial chars" for any database interaction. You have to use prepared statements instead.

请注意，使用 multi_query 的建议是不合理且具有误导性的，会导致很多问题.

Note that a suggestion to use multi_query is unjustified and misleading, causing a lot of problems.

这篇关于为什么当我将多个查询发送到 mysqli_query 时会发生错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！