Php/MySQL help - random daily pick?

Question

Hello, 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 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

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).

Answer 2

You can try something like this:

$total = 'SELECT COUNT(*) FROM table;';
$query = 'SELECT * FROM table ORDER BY id ASC LIMIT 1 OFFSET ' . (date('Ymd') % $total) . ';';

Answer 3

I think you'll need to update the random picked record with "today" field = 1..

Something like this:

// ------------
// Run this 3 commands once a day

// Reset all records
mysql_query("UPDATE `table` SET `today` = 0");

// Pick one
$sql = mysql_query("SELECT `id` FROM `table` ORDER BY RAND() LIMIT 1");
$id = mysql_result($sql, 0, 'id');

// Update the record
mysql_query("UPDATE `table` SET `today` = 1 WHERE `id` = {$id}");

// ------------

// Now you can find again your "random found record":
$query = mysql_query("SELECT * FROM `table` WHERE `today` = 1");

Answer 4

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 5

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)) 

Answer 6

I'd suggest caching with APC or memcached or Zend_Cache, e.g. something like this

$dailyPick = '';
$cacheKey = 'dailyPick'. date('dmY');
if($cache->has($cacheKey)) {
    $dailyPick = $cache->get($cacheKey);
} else {
    $dailyPick = $db->query(/* your query */);
    $cache->save($cacheKey, $dailyPick);
}

This way you don't have to hit the db at all if the daily pick was already fetched once and you also do not need a cron job.

Note that is example code. You have to adjust it to work with the API of your cache backend. You cannot copy and paste the code and expect it to work. This is just to illustrate how the program flow should look like.

Answer 7

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); 
}