使用PHP在一个SQL查询中执行多个UPDATE

Question

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)

Currently I have something like this

$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());
}

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?

Answer 1

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.

From the manual:

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

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.

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