Is it possible to set a MySql statement to query a database on a 24 Hour period and return random items in the database daily?

Question

I have built a recipe cookbook web service and part of the web service is giving users a random recipe of the day. The recipe of the day function is to simply just output a new recipe daily. I have built the Sql statement as follows:

<?php include_once 'dbconfig.php'; ?>

<?php
    $sql="SELECT * FROM recipe_2 ORDER BY RAND() LIMIT 1";
    $result_set=mysql_query($sql);
    $link_address = 'view.php';
    while($row=mysql_fetch_array($result_set))
    {
    } 
?>

But this will generate a new recipe everytime a user visits or refreshes the site. Is there a way to improve this code by adding a 24hr timer to the SQL statement that keeps a static recipe on a daily or 24 hour duration? Thanks

you can make a table with a date column and a reciepe id for that day. so everytime user visit a website you can show the todays reciepe from that table. if reciepe does not exists for that day you can create on that script — Murat Cem YALIN, Dec 01 '15 at 09:19

score 1 · Answer 1 · edited May 23 '17 at 12:30

1

Yes, wrap it in an event that is scheduled to fire off say, daily. You determine the interval.

The event begin and end block can be thought of as a scheduled stored procedure. Do whatever you want in that event, such as clearing tables, running selects. Inserts. Importing data. Etc.

See this Write-up for a few examples, and links.

Mysql Manual page on Create Event.

edited May 23 '17 at 12:30

Community

1
1

answered Dec 01 '15 at 09:22

Drew

24,851
10
43
78

Thanks drew for the fast response, I will have a look into these links. – JayLav Dec 01 '15 at 09:34
Many ways to skin the cat. Choose one you like – Drew Dec 01 '15 at 09:35

score 1 · Answer 2 · answered Dec 01 '15 at 09:33

If the recipe changes daily only, then generate the random number only once a day using either mysql's built-in event scheduler or an operating system level one (e.g. cron or windows task scheduler) and save the results. Users will be served from the saved version.

If you use mysql's event scheduler, then save the generated random id of the recipe in a table and the recipe of the day should query the recipe table using this saved id. You can even save the date in the table to store the history of the recipe of the day, so that the users can view them and you can also make sure that the same recipe is not selected twice.

If you use os based task scheduler, then you can even generate the static page for the recipe of the day overwriting an html file. You can simply link to the same page on your site. Just make sure, you set an appropriate expiry day in the headers, so that the browsers get the fresh version every day, instead of loading the html from the cache.

Thanks for the elaborate response shadow. – JayLav Dec 01 '15 at 09:41 — JayLav, Dec 01 '15 at 09:41

score 0 · Accepted Answer · answered Dec 01 '15 at 09:20

0

Calculate the random number using a seed that changes on a daily basis:

$seed = strtotime(date('Y-m-d'));
$sql="SELECT * FROM recipe_2 ORDER BY RAND($seed) LIMIT 1";

answered Dec 01 '15 at 09:20

Amarnasan

14,939
5
33
37

Although this requires only a simple edit of the existing code, it is not too efficient from a performance point of view. Also, there is no option to preserve history or to make sure that the same recipe is not selected twice. – Shadow Dec 01 '15 at 09:35
Thanks Amarnasan, I have developed a solution for preserving recipe selection but thanks again your solution worked like a charm!!! Ive been fighting with this for hours. Thanks so much. – JayLav Dec 01 '15 at 09:47

Is it possible to set a MySql statement to query a database on a 24 Hour period and return random items in the database daily?

3 Answers3