multiple row UPDATE (mySQL)

[artheus]

Hi!

I want to update data on multiple rows in my table. Such as this :

$mysql->query("UPDATE categories SET open=0");

foreach($p as $value) {
    $mysql->query("UPDATE categories SET open=1 WHERE id=" . $value);
} 

So what I want to do is to set "open" to 1, on rows with specific id's, and all the others shall have open=0;
So if I've got a table like this

 _ID___OPEN_
|  1  |  1  |
|  2  |  0  |
|  3  |  0  |
|  4  |  1  |
|  5  |  0  |

And the ID's I want to update is 2 and 5, the table should look like below after the query.

 _ID___OPEN_
|  1  |  0  |
|  2  |  1  |
|  3  |  0  |
|  4  |  0  |
|  5  |  1  |

Is this possible in one query?

Cheers and Thanks,
Artheus

[skywalker2208]

Use mysql handy IN()

Look something like this

UPDATE categories SET open=1 WHERE id IN (1,2,4,5) 

[qforever]

Here is:

$ids=array();
foreach($p as $value) {
$ids[]=$value;
}
$mysql->query("UPDATE categories SET open=1 WHERE id IN (".implode(",",$ids).")");

[skywalker2208]

Here is:

$ids=array();
foreach($p as $value) {
$ids[]=$value;
}
$mysql->query("UPDATE categories SET open=1 WHERE id IN (".implode(",",$ids).")");

If $p is already an array then you don't need to loop through $p to store it in another array.

[qforever]

If $p is already an array then you don't need to loop through $p to store it in another array.

It's obvious. Just an example. Nothing more.