0

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!

Prashanth JC
  • 537
  • 1
  • 6
  • 20
  • The above code is just an example and not the actual code.. :) – Prashanth JC Mar 15 '15 at 15:10
  • Sure you can. I do not know about the deprecated `mysql_` extension, but there is [`mysqli_multi_query()`](http://php.net/manual/de/mysqli.multi-query.php) and [PDO supports it as well](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – kero Mar 15 '15 at 15:11
  • 2
    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 Mar 15 '15 at 15:13

1 Answers1

2

Just put the where statement on the update:

UPDATE `table`
    SET `status` = '1'
    WHERE `status`='$status' AND `date`='$date'";
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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? – Prashanth JC Mar 15 '15 at 15:26
  • 1
    @PrashanthJC . . . Yes. You should be able to add that condition to the `where` clause. – Gordon Linoff Mar 15 '15 at 15:31