英文:
SQL UPDATE works in MySQL fails in PHP
问题
我有以下查询：
UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
如果我直接在MySQL中尝试，它会按预期工作。
但是，当我通过PHP尝试时，它给我以下错误：
错误：UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
您的SQL语法有错误；请检查与您的MariaDB服务器版本相对应的手册，以了解在第1行附近使用正确的语法
我觉得这与引号有关，但我似乎无法弄清楚。
提交的代码：
$query = "UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';";
$db = $conn;
$execute = mysqli_query($db, $query);
if ($execute == true) {
$success = 1;
} else {
echo "错误：" . $query . "<br>" . mysqli_error($db);
}
表 names：
name_id name rsvp_status
13 John 0
15 David 0
17 Bill 1
..
英文:
I have the following query:
UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
If I try that directly in in MySQL it works as expected.
When I try it via PHP it gives me the following error:
Error: UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
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 names SET rsvp_status = '1' WHERE name_id = '15'' at line 1
I get a feeling it has to do with the quotations but I cannot seem to figure it out.
Submit code:
$query = "UPDATE names SET rsvp_status = '1' WHERE name_id = '13'; UPDATE names SET rsvp_status = '1' WHERE name_id = '15';";
$db = $conn;
$execute = mysqli_query($db, $query);
if ($execute==true) {
$success = 1;
} else {
echo "Error: " . $query . "<br>" . mysqli_error($db);
}
Table names:
name_id name rsvp_status
13 John 0
15 David 0
17 Bill 1
..
答案1
得分: 3
问题是您在SQL中提交了多个查询：
UPDATE names SET rsvp_status = '1' WHERE name_id = '13';UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
您可以在一次性使用 [mysqli::multi_query][1] 运行多个查询，但这通常是一个危险的想法... 在这种情况下，通常最好在循环中运行查询：
$db = $conn;
$queries = [
"UPDATE names SET rsvp_status = '1' WHERE name_id = '13';",
"UPDATE names SET rsvp_status = '1' WHERE name_id = '15';"
];
foreach($queries as $query){
$execute = mysqli_query($db, $query);
if ($execute==true) {
$success = 1;
} else {
echo "Error: " . $query . "<br>" . mysqli_error($db);
break; //如果MySQL出错，您可能希望停止
}
}
正如上面的评论中有人提到的，您还可以将此简化为单个查询 (UPDATE names SET rsvp_status = '1' WHERE name_id IN ('13', '15');)，但假设您可能对 rsvp_status 有不同的值或需要多个查询，我建议使用循环。
如果您不打算像示例中那样硬编码这些值（您几乎肯定不会硬编码这些值），还应确保对值进行转义，您的错误处理也可以进行一些清理，但除此之外，这是一个更好的方法。
英文:
The issue is that you're submitting more than one query in the SQL:
UPDATE names SET rsvp_status = '1' WHERE name_id = '13';UPDATE names SET rsvp_status = '1' WHERE name_id = '15';
You can technically run multiple queries at once using [mysqli::multi_query][1] but it's generally a dangerous idea... you're almost always better off running the queries in a loop in a situation like this:
$db = $conn;
$queries = [
"UPDATE names SET rsvp_status = '1' WHERE name_id = '13';",
"UPDATE names SET rsvp_status = '1' WHERE name_id = '15';"
];
foreach($queries as $query){
$execute = mysqli_query($db, $query);
if ($execute==true) {
$success = 1;
} else {
echo "Error: " . $query . "<br>" . mysqli_error($db);
break; //you probably want to stop if mysql is erroring
}
}
As someone commented above, you can also simplify this into a single query (UPDATE names SET rsvp_status = '1' WHERE name_id IN ( '13',15);) but assuming you might have different values for rsvp_status or otherwise need multiple queries, I'd recommend that loop.
You should also make sure you're escaping your values if you're not planning on hard-coding them like the example (you're almost certainly not going to hard-code those values) and your error handling could be cleaned up a bit, but otherwise this is a better approach.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论