使用PHP在一个SQL查询中执行多个UPDATE [英] Perform multiple UPDATEs in one SQL query using PHP

问题描述

所以我看了

So I have had a look at HERE, but it seems a little convoluted for the simplicity of what I am doing.. At maximum I would be dealing with a hundred items to update (and most of the time its going to be more like 40)

目前我有这样的东西

$sql_update = '';
for($x = 0; $x < count($nodes); $x++){
  if($nodes[$x]['loaded'] == 'true'){
    if($nodes[$x]['changed'] == 'true'){
        $sql_update .= 'UPDATE `genetic_decomp`.`tbl_node2view` SET `x` = "'.$nodes[$x]['location']['x'].'", `y` = "'.$nodes[$x]['location']['y'].'" WHERE `tbl_node2view`.`id` = "'.$nodes[$x]['id'].'";'; 
        $sql_update .= 'UPDATE `genetic_decomp`.`tbl_nodes` SET `name` = "'.$nodes[$x]['name'].'", `type` = "'.$nodes[$x]['type'].'" WHERE `tbl_nodes`.`node_id` = "'.$nodes[$x]['id'].'";';
    }
  }
}
if($sql_update != ''){
    $sql_result=mysql_query($sql_update,$connection) or exit("Sql Error".mysql_error());
}

现在，当我得到它以echo $sql_update格式打印输出，然后将输出粘贴到MAMP的SQL框中时，它就可以正常工作.通过并更新了我想要的两个表中的行

Now when i get it to print out the output in just an echo $sql_update and then paste the output into the SQL box in MAMP it works fine.. goes through and updates the lines in the two tables i want

但是，当我运行上面的代码时，它会吐回来:

however when i run the above code it spits back:

Sql Error
You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `genetic_decomp`.`tbl_nodes` SET `name` = "lala", `type` = "p" WHERE `tbl' at line 1

我在做什么错? 有更好的方法吗?

what am i doing wrong? is there a better way of doing this?

推荐答案

您的SQL在语法上看起来是正确的(除非我错过了一些简单的东西).实际的问题是因为您正在使用 mysql_query() -不支持多个陈述;因此，您无法使用此方法在一个查询中运行两个UPDATE查询.

Your SQL looks syntactically correct (unless I've missed something simple). The actual problem is because you're using mysql_query() - which does not support multiple statements; therefore, you can't run two UPDATE queries in one with this method.

从手册中:

mysql_query()发送一个唯一查询(多个查询不是 支持)

mysql_query() sends a unique query (multiple queries are not supported)

同一点，不推荐使用mysql_方法，因此我(和社区)建议您更新代码以使用 PDO 方法-两者都在一个语句中支持多个查询.

On the same note, the mysql_ methods are being deprecated so I (and the community) would suggest you update your code to use mysqli_ or PDO methods - both of which support multiple queries in a single statement.

如果您需要坚持使用mysql_query()(而不是重组整个应用程序)，只需拆分查询并背对背运行它们即可.

If you need to stick with mysql_query() (instead of restructuring your entire application), just split the queries and run them back-to-back.

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