[Resolved] display past 3 days using timestamp

mikegilmore81

Hi guys, I'm new to PHP and MySQL and I have a question.
I am trying to create a weblog that will only show the past three days entries. I have used a timestamp on the insert page, but cannot get it to only show me the past three days entries on the weblog page.
It's a simple setup so far, two pages(addentry.php and weblog.php), one database(weblog) with three tables.

This is the MySQL code I used to create the tables:

CREATE TABLE weblog (
     entrydate TIMESTAMP PRIMARY KEY,
     entrytitle VARCHAR(100),
     entrytext TEXT);

This is my entry page, addentry.php:

<HTML>
 <HEAD>
  <TITLE>Prayer Request Entry</TITLE>
 </HEAD>
 <body background="http://www.mttopmin.org/images/bkgnd_main.jpg" style="background-attachment: fixed;" bgproperties="fixed">

<?php
  if ($HTTP_POST_VARS['submit']) {
    mysql_connect("localhost","my_username","my_password");
    mysql_select_db("ronparr_prayer");
    $entrytitle=$HTTP_POST_VARS['entrytitle'];
    $entrytext=$HTTP_POST_VARS['entrytext'];
    $query ="INSERT INTO weblog (entrytitle,entrytext)";
    $query.=" VALUES ('$entrytitle','$entrytext')";
    $result=mysql_query($query);
    if ($result) echo "<b>Successfully Posted!</b>";
    else echo "<b>ERROR: unable to post.</b>";
  }
 ?>

  <H1>Prayer Request</H1>
  <form method="POST" action="addentry.php">
   <b>Title:</b><br>
   <input type="text" name="entrytitle"><br>
   <b>Weblog Entry:</b><br>
   <textarea cols="60" rows="6" name="entrytext"> 
   </textarea><br>
  <input type="submit" name="submit" value="Submit">
  </form>
 </BODY>
</HTML>

This is the display page, weblog.php:

<html>
 <head><title>Prayer Requests</title></head>
 <body background="http://www.mttopmin.org/images/bkgnd_main.jpg" style="background-attachment: fixed;" bgproperties="fixed">
  <h1>Prayer Requests</h1>
 <dl>
 <?php
   mysql_connect("localhost","my_username","my_password");
   mysql_select_db("ronparr_prayer");
   $query ="SELECT entrytitle, entrytext,";
   $query.=" DATE_FORMAT(entrydate, '%M %d, %Y @ %h:%i:%s') AS date";
   $query.=" FROM weblog ORDER BY entrydate DESC LIMIT 10";
   $result=mysql_query($query);
   while (list($entrytitle,$entrytext,$entrydate) = 
    mysql_fetch_row($result)) {
       echo "<dt><b>$entrytitle ($entrydate)</b></dt>";
       echo "<dd>$entrytext</dd><br><hr><br>";
   }
 ?>
 </dl>
 </body>
</html>

Can someone please help me? I only want the display page to show the past three days. I know it's something simple, but like I said, I am new to this and I'm making everything harder than it needs to be.
Thanks in advance for all your help.

liquorvicar

$query ="SELECT entrytitle, entrytext,";
   $query.=" DATE_FORMAT(entrydate, '%M %d, %Y @ %h:%i:%s') AS date";
   $query.=" FROM weblog WHERE DATE_SUB(CURDATE(),INTERVAL 3 DAY) <=entrydate ORDER BY entrydate DESC LIMIT 10";

mikegilmore81

Thanks a bunch, it works like a charm. I'm using the form for a church website so they can submit prayer requests. I think they will be very happy now. Only thing I need to work out now is the timezone issue. It displays the time posted 3 hours earlier. If I post at 5pm, it shows that it was posted at 2pm. Not a big deal I guess, but something I would like to work on.

Thanks again.

liquorvicar

well presumably that means your server's timezone is different from your own.

there is a DATE_ADD command in MySQL like the DATE_SUB command which you could add to your SELECT to pull out the right time, or you could use PHP's strtotime command:

$realtime = strototime('+3 hours',$unix_timestamp_from_db);