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.

Gnutt · Accepted Answer · 2010-01-27 12:40:28Z

15

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.

answered Jan 27, 2010 at 12:40

Gnutt

3,9674 gold badges22 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alix Axel Over a year ago

+1 Dude, I had no idea RAND() accepted arguments, this is a brilliant idea!

Alix Axel Over a year ago

@Izumi: Try $query = "SELECT * FROM table ORDER BY rand(" . date('Ymd') . ") LIMIT 1;";

Izumi Over a year ago

What do you mean by 'expensive' ?

Gnutt Over a year ago

by expensive Jacco means, it takes more power from your system than other, perhaps leener approaches.

Izumi Over a year ago

Can anyone explain to me why the codes get broken and it says that the fetch is not working properly...

|

John Parker · Accepted Answer · 2010-01-27 12:32:42Z

5

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

answered Jan 27, 2010 at 12:32

John Parker

54.5k11 gold badges133 silver badges131 bronze badges

1 Comment

Jacco Over a year ago

This is the best approach. The random selection is done only once. after this, showing your daily pick is a fast and simple select query.

Alix Axel · Accepted Answer · 2010-01-27 12:36:53Z

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

answered Jan 27, 2010 at 12:36

Alix Axel

155k99 gold badges406 silver badges509 bronze badges

1 Comment

Alix Axel Over a year ago

Gnutt's solution is much more elegant!

Thiago Belem · Accepted Answer · 2010-01-27 12:37:44Z

0

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

answered Jan 27, 2010 at 12:37

Thiago Belem

7,8325 gold badges47 silver badges65 bronze badges

1 Comment

Izumi Over a year ago

I just don't understand what do you mean by 'running them once a day'... and won't this delete my table? or it creates a new table called 'today'? I'm sorry... I just don't understand... sighs and looks down

Collectives™ on Stack Overflow

Php/MySQL help - random daily pick?

4 Answers 4

6 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related