如何使用mysql执行多个更新查询

Question

I am trying to execute multiple update queries at a time. Please have a look into below mentioned code.

Actual code block:

$relist_item_id  = $_REQUEST['relist_item_id'];   // an array
$relist_item_num = count($relist_item_id);

for($j = 0; $j < $relist_item_num; $j++){
  $item_id = $relist_item_id[$j]; 
  $update_query = "UPDATE items SET ActiveItem=1 WHERE id=$item_id";
  mysql_query($update_query);
}

The above code will execute each query one by one. But i want to execute all the query at a time and so i have tried the below code.

$update_query = '';
for($j = 0; $j < $relist_item_num; $j++){
  $item_id = $relist_item_id[$j]; 
  $update_query .= "UPDATE items SET ActiveItem=1 WHERE id=$item_id;";
}
mysql_query($update_query); 

The above mentioned code is not executing/ updating the record. But, i want to execute in similar way. Is it possible to do so?

Using "SQL" in phpmyadmin, i have checked that we can execute more than one query at a time i.e. several update query can be executed at a time. For eg. the below mentioned update query will be executed at a time.

UPDATE items SET ActiveItem=1 WHERE id=1;UPDATE items SET ActiveItem=1 WHERE id=5;UPDATE items SET ActiveItem=1 WHERE id=10;UPDATE items SET ActiveItem=1 WHERE id=12;UPDATE items SET ActiveItem=1 WHERE id=15;UPDATE items SET ActiveItem=1 WHERE id=16;UPDATE items SET ActiveItem=1 WHERE id=20;

I would also like to know that is there any limit on the query like we can execute maximum of 20 queries at a time or so?

My actual target is to save the execution time as there can be a huge for loop and it may take many time to execute each and every query one by one. My above approach may not be correct. But, your idea can help me to do so.

Thanks in advance.

Answer 1

Why not use an in function like this:

$relist_item_id  = $_REQUEST['relist_item_id'];   // an array
$query="UPDATE items 
    SET ActiveItem=1 WHERE id in (".implode(', ', $relist_item_id).")";

Assumes that the values are numeric and don't need to be encapsed in single quotes. If they are, this should work:

$query="UPDATE items 
    SET ActiveItem=1 WHERE id in ('".implode("', '", $relist_item_id)."')";