How to execute more than one update query using 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.

You need multiple queries, you can only execute one statement at time. — dan-lee
– dan-lee, Commented Sep 13, 2012 at 12:29
You can use IN in your query, like UPDATE items SET ActiveItem=1 WHERE id IN ($items_id_comma_separated) — Alexander Larikov
– Alexander Larikov, Commented Sep 13, 2012 at 12:31
On database level, it is executed one by one. So, there's no need to do what you want. You're not gaining anything. — nullpotent
– nullpotent, Commented Sep 13, 2012 at 12:32
take a look at prepared statements, I believe mysql support this, or if you don't need to iterate through - Alexander hit it on the head. — Dave
– Dave, Commented Sep 13, 2012 at 12:32

Andrew · Accepted Answer · 2012-09-13 12:33:16Z

3

You could try using IN:

ids = array(1,2,3....);
$update_query .= "UPDATE items SET ActiveItem=1 WHERE id IN (".implode(',', $ids).")";

answered Sep 13, 2012 at 12:33

Andrew

12.8k1 gold badge37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Debashis Over a year ago

This answer is also correct and want to choose as correct one. But as i can select only one answer as the correct answer, i thought to go with the first one as its describing more. But, i have marked this answer as "useful" too.

Andrew Over a year ago

I would discourange the use of the first item which advocates using user input directly in a database query. That would be highly insecure.

Fluffeh · Accepted Answer · 2012-09-13 12:37:45Z

2

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)."')";

edited Sep 13, 2012 at 12:37

answered Sep 13, 2012 at 12:31

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Collectives™ on Stack Overflow

How to execute more than one update query using mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related