Php / MySQL Help - a random daily choice?

Question

I am trying to get a selection from my database that will last all day (daily selection). I am using the following code:

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

But, as you can see, this only gives me a random selection from the table, and every time I refresh the page, I get a new random selection. How can I make a choice to last all day?

Thanks in advance <3

---

I am trying to do this:

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

But I get the following error: mysql_fetch_assoc (): the provided argument is not a valid MySQL result resource. This is the part that will break:

 $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) 

---

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

 $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 to do it 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 becomes a mistake for me that I use the has function for a non-object.

Answer 1

If you set the SEED for rand to an integer that changes daily, this will solve your problem.

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

Would do the trick.

Answer 2

A reasonable way to do this would be to automatically generate a selection of the contents of the day through a cron job that was configured to run once a day.

Thus, the cron job will execute the SQL you provided and store the corresponding content in a flat file / database table, etc. (or even just save the selected identifier in another table for future search purposes).

Answer 3

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 4

I think you will need to update the randomly selected entry using the "today" = 1 field ..

Something like that:

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