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.