I want to display a quote on my site for daily basis. after 24 hours it should change. quotes are stored in MySQL database. how do I do this with PHP
Thanks
Mathew
How about something along the lines of using the following:
quotes
------
quote_id
..
.
quote_rota
----------
quote_rota_id
quote_id
date_shown
Then use a query such as the following to get a random quote that is not one of the past 7 quotes that have been displayed:
SELECT * FROM quotes WHERE id=(
SELECT FLOOR(MIN(quote_id)+(RAND()*MAX(quote_id))
FROM quotes
) AND quote_id NOT IN (
SELECT quote_id FROM quote_rota ORDER BY date_shown DESC LIMIT 7
) LIMIT 1;
// process and retrieve quote_id then store in quote_rota
If you store the result in a file that is populated by a CRON job/ scheduled task, then you could have this lasting as long as you wish (e.g. 24 hours, 7 days, 1 hour, etc)
Alternatively, you could add a column to the quote table to indicate that it is the "current" quote (e.g. active) and then use:
SELECT quote_id FROM quotes WHERE id=(
SELECT FLOOR(MIN(quote_id)+(RAND()*MAX(quote_id))
FROM quotes
) AND quote_id NOT IN (
SELECT quote_id FROM quote_rota ORDER BY date_shown DESC LIMIT 7
) LIMIT 1;
// process and retrieve quote_id then update "current" flag
Schedule this task as often as required and then use something like the following to get the quote:
SELECT * FROM quotes WHERE active = 1
The easiest way would be to store the day a quote is supposed to be shown on the site as a column in the mySQL table, and then make a query for that specific quote like
$query = "SELECT * FROM quotes WHERE date = CURDATE();";
if you don't store the date in your database, it becomes more difficult and complex - you'd have to display random entries but keep track of which one was shown when so the same quote doesn't come up twice in short succession. All doable, but possibly more complex than necessary.
You need to setup a cron tab with your server that runs every 24 hour. However, setting it up depends on what server you have. http://en.wikipedia.org/wiki/Cron for more information about cron. IF you're using cPanel, www.upstartblogger.com/how-to-create-a-cron-job-in-cpanel might help you.
Many ways.
One way could be to have 2 more columns in your quotes table 'isdisplayed' and 'displayedwhen'.
Using these 2 extra fields you can keep track of which quote is displayed and for how long it has been displayed for.
If you don't want to use any cache solution and you don't want to attach date of future use for each quote, you can do something like:
SELECT id, quote FROM quotes WHERE date_used = NOW();
if this returns null, do something like:
$count = SELECT COUNT(*) FROM quotes WHERE date_used = '0000-00-00'
to fetch number of unused quotes, then select one quote randomly:
$rand = rand (0, $count);
$quote = SELECT id, quote FROM quotes WHERE date_used = '0000-00-00' LIMIT $rand, 1
UPDATE quotes SET date_used = NOW() WHERE id = $quote['id']
If you have a set of quotes, you may use modulus to ensure the difference between current date and a reference date will fall into the range of number of quotes in your db table.
SELECT *
FROM `quotes`
WHERE id = DATEDIFF( CURDATE( ) , '2010-05-01' ) MOD (SELECT COUNT(*) FROM `quotes`)