Show different database record every 24 hours

Question

I am building a simple app with HTML5 and PHP and I need to show a different quote every day. So I have a database table with all the 365 quotes and authors in it, but I can't seem to find a way to show a different database record every 24 hours. I am fairly new to programming and searched the whole internet for an answer but couldn't find anything similar to this.

So I have my html and php code to connect to the database and show the latest record. How can I show the user another database row every 24 hours?

This is what my code looks like now:

<?php

$con=mysql_connect('localhost','xxxx','xxxx');

if(!$con)
{
    die ("Failed to connect: " . mysql_error() ) ; 
}
mysql_select_db("xxxx",$con);

$sql = "SELECT * FROM quotes LIMIT 1" ; 
$myData=mysql_query($sql,$con) ;

while($record = mysql_fetch_array($myData)) {

  echo  "<h1>" . $record['quote'] . "</h1>";
  echo  "<br><p> - " . $record['author'] . " - </p>";

  }

mysql_close($con);

?>

Thanks in advance guys!

what is the problem using one database with multiple tables? — abh, May 29 '15 at 14:37
I would suggest you somehow save the "index" of today, then you have to write code to find out if its time for a new quote, increase that index and change your query to look like: `"SELECT * FROM quotes LIMIT 1," . index` — simon, May 29 '15 at 14:42
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). Additionally you need to add error checking and quit assuming your queries and connections all work. — Jay Blanchard, May 29 '15 at 14:52
I would suggest to compare the time when the page is hit with the server time. a kind of $current_date=("Ymd") create by a job on your server(let imagine thrown at 00:00:01 everyday UTC time), then create another variable new_date= date("") and a condition if $new_date > $current date "your code to change the quote" and replace the $current_date value . This is just a quick idea of how I would start my think — keikoku92, May 29 '15 at 14:53
By the way, when you test my answer, don't forget to change the date of your computer while the page is refreshing, to see how the thought of the day changes! (remember, you can only use date of today, tomorrow and after tomorrow). — Jose Manuel Abarca Rodríguez, May 29 '15 at 15:56

tmm · Answer 1 · 2015-05-29T15:48:07.990

4

Add a column to your table named e.g. id. Iterate through all the rows in the table and add values from 1 to 365. Then you can do:

$sql = "SELECT * FROM quotes WHERE id = " . date('d');

edited May 29 '15 at 15:48

answered May 29 '15 at 15:31

tmm

163
6

Thanks, this works great, but the problem is I want another quote every day of the year and with this methode I can only have a new quote every day of the month. The second month it will start again with "1" – Senne Vandenputte May 29 '15 at 20:59
1

Nevermind, I used date('z') (+1) which returns the day of the year, thanks! – Senne Vandenputte May 29 '15 at 21:04

Jose Manuel Abarca Rodríguez · Answer 2 · 2015-05-29T15:55:39.350

You can refresh the page every 86400 seconds (seconds in a day), when the page automatically reloads use PHP to get current date and use the date to display thought of the day.

Next example refreshes page every 3 seconds, get current date with PHP and display the thought of the day (I only added 3 thoughts for today 5/29, tomorrow 5/30 and after tomorrow 5/31). Create a text file in your www directory, name it "refresh_every_24h.php" and run it from you browser :

<?php
$database = array( "5/29" => "Jon Skeet : «I'm the king!»" ,
                   "5/30" => "Jester : «I know more assembly than you»" ,
                   "5/31" => "Tómax : «I'm here to make points, baby»" );
?>
<!--
NEXT "META" REFRESHES PAGE EVERY 3 SECONDS, YOU CHANGE IT BY 86400 (SECONDS PER DAY).
-->
<meta http-equiv="refresh" content="3;URL=http://localhost:8099/refresh_every_24h.php" />
<html>
  <head>
    <title></title>
  </head>
  <body>
    Thought of the day:
    <br/>
<?php
$arr = getdate( time() ); // GET CURRENT SYSTEM TIME.
echo $database[ strval( $arr[ "mon" ] ) . // USE MONTH AND
                "/" .                     // DAY TO FIND
                strval( $arr[ "mday" ] )  // THOUGHT OF THE DAY.
              ];
?>
  </body>
</html>

To change the filename for any other just take care of changing it in the next line, it's configured to run on MY localhost, you change the host too for your own:

<meta http-equiv="refresh" content="3;URL=http://localhost:8099/refresh_every_24h.php" />

Don't forget to change the date of your computer while the page is refreshing, to see how the thought of the day changes! (remember, with this example you can only use date of today, tomorrow and after tomorrow).

Show different database record every 24 hours

2 Answers2