Update multiple rows without looping: PHP-MySQL

Question

I have to update the status of multiple rows in a MySQL table using PHP. Practically, there will be around 200 rows that needs status update at once. Currently am using a loop to update the rows.

$status = '0';
$date = 'YYYY-MM-DD';
$result = mysql_query("SELECT * FROM `table` WHERE `status`='$status' AND `date`='$date'") or die(mysql_error());
foreach($result as $row){
    mysql_query("UPDATE `table` SET `status`='1'or die(mysql_error())");
}

This is taking quite some time to get the status updated. Is there a better or rather easier way available to do this instead of looping over 200 times each time?

Thanks in advance!

The above code is just an example and not the actual code.. :) — Prashanth JC
– Prashanth JC, Commented Mar 15, 2015 at 15:10
Sure you can. I do not know about the deprecated mysql_ extension, but there is mysqli_multi_query() and PDO supports it as well — kero
– kero, Commented Mar 15, 2015 at 15:11
You should be able to move the WHERE clause into your UPDATE statement and eliminate to first query and the loop. And in fact you'll want to do that because now the UPDATE addresses the whole table, not specific records. — BigScar
– BigScar, Commented Mar 15, 2015 at 15:13

Gordon Linoff · Accepted Answer · 2015-03-15 15:16:09Z

2

Just put the where statement on the update:

UPDATE `table`
    SET `status` = '1'
    WHERE `status`='$status' AND `date`='$date'";

answered Mar 15, 2015 at 15:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Prashanth JC Over a year ago

This will update all the rows. What if I have entries that belong to other user in the same table that should be omitted from being updated? Can I just add that condition to WHERE of the mysql statement?

Gordon Linoff Over a year ago

@PrashanthJC . . . Yes. You should be able to add that condition to the where clause.

Collectives™ on Stack Overflow

Update multiple rows without looping: PHP-MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related