CODEWITHSUNDEEP
mysql
patrick
patrick

Reputation: 11721

conditional set in MySQL

I have a database of funny images, but this question relates to everything with a posting date. If I am updating a page I'm first checking if there's an entry set up for today, if not I take one that has been in the database the longest, like this:

$result=mysql_query("SELECT * FROM amazingjokes.img WHERE listed=CURDATE()");
if(!mysql_num_rows($result)){
   $result=mysql_query("SELECT * FROM amazingjokes.img WHERE isnull(listed) 
                        ORDER BY  added ASC");
   $row=mysql_fetch_array($result);
   $mysql_query("UPDATE amazingjokes.img SET listed=CURDATE() 
                 WHERE id=$row[id]");
}

Basically what this does is "get today's image and select the longest pending one when there's no image for today". Is it possible to do this in a single query and how would that be done?

Upvotes: 2

Views: 279

Answers (3)

patrick
patrick

Reputation: 11721

The solution I ended up with was one query, with a second one if there's no date set for today's image:

$img=mysql_fetch_array(mysql_query("
  SELECT * FROM amazingjokes.img
  WHERE listed=curdate() OR isnull(listed) 
  ORDER BY listed DESC, added ASC LIMIT 1"
));
if($img[0]["listed"]==null) 
  mysql_query("UPDATE amazingjokes.img SET listed=curdate() 
               WHERE id=".$img[0]["id"]);

This selects everything with no date or with today's date. the "order BY listed" makes sure that if there is an image selected for today it will be on top, the "added ASC" in the order will ensure the longest pending one is on top of the list if there's no image for today. Thanks to @Stephan for putting me in the right direction!

Upvotes: 1

Stephan
Stephan

Reputation: 8090

You can try something like this:

update amazingjokes.img 
    set listed=curdate() 
where 
    id IN (
        select 
            id 
        from 
            amazingjokes.img 
        where 
            isnull(listed) 
        order by 
            added asc
    )

And to replace the hole 3 queries you can try :

 update amazingjokes.img  
    set listed=curdate()
 where 
    id IN (
        select 
            id 
        from 
            amazingjokes.img 
        where 
            listed=curdate() 
        order by 
            added asc
    )
    AND (
        select 
            count(*) 
        from 
            amazingjokes.img 
        where 
            listed=curdate()
    ) = 0

Upvotes: 2

Rowland Shaw
Rowland Shaw

Reputation: 38130

If you coalesce the NULL to a value, whilst it doesn't update the source data (you probably want to do that still), you could change the select to bring back those with today's date or no date with:

SELECT * 
FROM amazingjokes.img 
WHERE COALESCE( listed, CURDATE() ) = CURDATE()

Upvotes: 2

Related Questions