Php/MySQL help - random daily pick?

Question

I'm trying to get a pick from my DB that would last for a day (daily pick). I use the following code:

$query = 'SELECT * FROM table ORDER BY rand() LIMIT 1

But as you can see it only gives me a random pick from the table, and every time I refresh the page it gets me a new random pick. How can I make the pick to last for a whole day?

Thanks in advance <3

---

I'm trying this:

$query = "SELECT * FROM table ORDER BY rand(" . date("Ymd") . ") LIMIT 1";

But I get the following error: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource. This is the part that gets broken:

$results = mysql_query($query); 

while($line = mysql_fetch_assoc($results)) 

---

So... it should look like this, right? (I mean, choosing the daily random pick?)

$dailyPick = 'SELECT * FROM table ORDER BY rand() LIMIT 1'; 

$cacheKey = 'dailyPick'. date('dmY'); 
if($cache->has($cacheKey)) { 
    $dailyPick = $cache->get($cacheKey); 
} else { 
    // hit database 
    $dailyPick = $cache->save($cacheKey); 
} 

---

I'm trying this now:

$dailyPick = 'SELECT * FROM table ORDER BY rand() LIMIT 1';  

$cacheKey = 'dailyPick'. date('dmY');  
if($cache->has($cacheKey)) {  
    $dailyPick = $cache->get($cacheKey);  
} else {  
    // hit database  
    $dailyPick = $cache->save($cacheKey);  
}  

However, it gets me a mistake that I'm using the 'has' function on a non-object.

Answer 1

If you set the SEED for the rand to an integer value that changes daily, that would solve your problem

$query = "SELECT * FROM table ORDER BY rand(" . date("Ymd") . ") LIMIT 1";

Would do the trick.

Answer 2

A sane means of doing this would be to automatically generate the pick of the day content via a cron job that was setup to run once a day.

As such, the cron job would execute the SQL you provided and store the appropriate content in a flat file/database table, etc. (or perhaps even just store the choosen id in another table for future lookup purposes).