show dates in the future

dripdrown · #1 (**permalink**) 06-01-05, 03:54 PM

hello everyone and anyone. i've been teaching myself some php/mysql the past few weeks and have made good progress in my opinion. i'm failing with what i would consider to be a simple task. i have records in my database that contain dates. i want to be able to display all dates from the current date and into the future. that way when events that are dated pass by, they will be removed from the listing. conversely, i'm working on a page that does the opposite. display all dates from the current date into the past so people can view events that have already happened. i'm not even sure where to start with this. if you anyone is willing to lend a helping hand it'd be very much appreciated! Thanks.

Anthony

dennispopel · #2 (**permalink**) 06-01-05, 05:13 PM

Hello,

That depends on the format you store dates in. Maybe I'm mistaken, but I would advocate using Unix timestamp. So doing you will be able to select future or past events with a very simple query like

$sql = "SELECT * FROM events WHERE date > " . time();

or

$sql = "SELECT * FROM events WHERE date < " . time();

You can do some manipulations with the mktime() function to get the 00:00 of any day, or the 1st day of any month and so on. If you however opt to store dates in the MySQL date/time format, refer to the MySQL docs on the date and time handling.

dripdrown · #3 (**permalink**) 06-01-05, 05:41 PM

currently i've got the following

$query="SELECT * FROM tblSHOWS ORDER BY date ASC";

i edited it to say

$query="SELECT * FROM tblSHOWS ORDER BY date ASC WHERE date >" .time();

getting some sort of error now. forgive my ignorance, i'm probably screwing something up, but this is my first database driven website so i'm still very much a novice.

Anthony

Jaffizzle · #4 (**permalink**) 06-01-05, 05:47 PM

$query="SELECT * FROM tblSHOWS WHERE date >" .time()." ORDER BY date ASC";

try that

Acecool · #5 (**permalink**) 06-01-05, 06:01 PM

Greater than or equal to..
>=

dripdrown · #6 (**permalink**) 06-01-05, 10:28 PM

i think i missed something completely. The date/time is stored in each record. and my code looks like this.

<?php
include("db_info.php");
mysql_connect("mysql.thisismyserver.net",$username ,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tblSHOWS WHERE date >" .time()." ORDER BY date ASC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close(); ?>

<table border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#CCCCCC" align="left">
<th width="85" height="20">  Date</th>
<th width="120" height="20">Location</th>
<th width="125" height="20">Venue</th>
<th width="270" height="20">Bands</th>
</tr>

<?php $i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$location=mysql_result($result,$i,"location");
$venue=mysql_result($result,$i,"venue");
$desc=mysql_result($result,$i,"desc");
?>

<tr align="left">
<td width="85" height="20">  <? echo $date; ?></td>
<td width="120" height="20"><? echo $location; ?></td>
<td width="125" height="20"><? echo $venue; ?></td>
<td width="270" height="20"><? echo $desc; ?></td>
</tr>

<?php
$i++;
}

echo "</table>";
?>

it's still showing all dates... any suggestions?

Anthony

dennispopel · #7 (**permalink**) 06-02-05, 01:35 AM

Hi,

How do you insert shows into the table? Possibly you calculate wrong dates so that all of them appear to be in far future?

dripdrown · #8 (**permalink**) 06-02-05, 01:52 AM

well, after a quick trip to the library and some heavy reading i got it. i'm not exactly sure how or that i understand how it works... but here goes.

i changed just the one line of code...

$query="SELECT * FROM tblSHOWS WHERE TO_DAYS( date ) - TO_DAYS( NOW( ) ) >= 0 ORDER BY date ASC";

there you have it... i solved my own problem... thanks to everyone for your advice / help

Anthony