CODEWITHSUNDEEP
phpmysqlsql-update
ltdev
ltdev

Reputation: 4477

MySQL advanced UPDATE query

I want to set an image as a cover for the album and i do it by choosing it from a radio input that is in a do-while loop. I have put in images table a field named is_cover that takes 1 if the image is set as cover ,or 0 if not.

<input type="radio" name="cover" value="<?php echo $row_images['image_id']; ?>" <?php if($row_images['is_cover'] == 1){ echo "checked=\"checked\""; } ?> />

My question is how can i perform an update query that sets all images is_cover field to 0 and only the image selected gets the value 1.

What i'm trying to say is how can i achieve this:

$is_cover = $_POST['cover'];
$query = "
    UPDATE images 
    SET is_cover = 1 
    WHERE image_id = {$is_cover} 
          AND SET is_cover = 0 
    WHERE image_id <> {$is_cover}
";

Upvotes: 1

Views: 417

Answers (3)

Jocelyn
Jocelyn

Reputation: 11393

Execute these 2 queries:

$query = "UPDATE images SET is_cover = 0 WHERE image_id <> {$is_cover}";

$query = "UPDATE images SET is_cover = 1 WHERE image_id = {$is_cover}";

The first query sets all is_cover to zero. The second query sets is_cover to 1 for the selected image.

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

This should do the trick for you:

UPDATE images SET is_cover = CASE WHEN image_id = {$is_cover} THEN 1 ELSE 0 END;

From my test:

mysql> select * from first;
+------+-------+
| id   | title |
+------+-------+
|    1 | aaaa  |
|    2 | bbbb  |
|    3 | cccc  |
|    4 | NULL  |
|    6 | ffff  |
+------+-------+
5 rows in set (0.01 sec)

mysql> update first set title = case when id > 4 then 'gggg' else title end;
Query OK, 1 row affected (0.01 sec)
Rows matched: 5  Changed: 1  Warnings: 0

mysql> select * from first;
+------+-------+
| id   | title |
+------+-------+
|    1 | aaaa  |
|    2 | bbbb  |
|    3 | cccc  |
|    4 | NULL  |
|    6 | gggg  |
+------+-------+
5 rows in set (0.00 sec)

Upvotes: 4

techie_28
techie_28

Reputation: 2133

You can run 2 Queries here one to set them all to 0 and then setting the one you selected to 1

Upvotes: 0

Related Questions