[SOLVED] Date Comparison - Hide Old Entries

[sintax63]

I have a small table on my website that lists upcoming events (the top 5). The problem is I have to go in and flag events that have already happened so they won't display on the page. Is there a way that the code can take the current date and compare it to the entries it is pulling form the table - and then only show from today on?

 

I will post the code I am using now. Also, my event dates are stored in YYYY-MM-DD format (which I hope is a good thing in this case).

 

Thanks in advance for any help you can give.

 

<?
$query="SELECT * FROM calendar WHERE status='1' ORDER BY date ASC LIMIT 5";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$event=mysql_result($result,$i,"event");

list($year,$month,$day)=split("-",$date);
$display_month=date("m",mktime(0,0,0,$month,$day,$year));

list($year,$month,$day)=split("-",$date);
$display_day=date("d",mktime(0,0,0,$month,$day,$year));

echo "<li class=\"cal\"><a href=\"calendar/view.php?id=$id\" name=\"$id\" onclick=\"return popUp(this.href, 'resizable=yes,width=630,height=420')\">$display_month/$display_day - $event</a></li> \n";

$i++;
}

?>

[fenway]

Try the following:

 

SELECT * FROM calendar WHERE status='1' AND `date` >= NOW() ORDER BY date ASC LIMIT 5

 

A few more comments:

 

-You can have sql produce the pretty date strings that you want using DATE_FORMAT()

-You really should be using a different kind of while loop -- one that goes through while( $row = mysql_fetch_assoc( $result ) ), and then you can use the individual hash keys as the variables -- it will be must easier to read, probably faster, and without "fake" loops.

[sintax63]

Try the following:

 

SELECT * FROM calendar WHERE status='1' AND `date` >= NOW() ORDER BY date ASC LIMIT 5

 

A few more comments:

 

-You can have sql produce the pretty date strings that you want using DATE_FORMAT()

-You really should be using a different kind of while loop -- one that goes through while( $row = mysql_fetch_assoc( $result ) ), and then you can use the individual hash keys as the variables -- it will be must easier to read, probably faster, and without "fake" loops.

 

Fenway -

 

Thanks so much for the help. Would you be able to point me in the right direction as to doing a correct while loop?

I'm kind of learning this as I go...

 

Edit: That string worked perfectly. Thank you so much! 

[fenway]

Incidentally, "date" is a reserved keyword, so you should rename your column, and avoid the backticks.

 

<?
$query="SELECT *, DATE_FORMAT( `date`, '%m/%d' ) AS displayDate FROM calendar WHERE status='1' AND `date` >= NOW() ORDER BY date ASC LIMIT 5";
$result=mysql_query($query);

while( $row = mysql_fetch_assoc( $result ) ) {
$id=$row["id"];
$display_date=$row["displayDate"];
$event=$row["event"];

echo "<li class=\"cal\"><a href=\"calendar/view.php?id=$id\" name=\"$id\" onclick=\"return popUp(this.href, 'resizable=yes,width=630,height=420')\">$display_date - $event</a></li> \n";
}

mysql_close();
?>

[sintax63]

Wow - that is insanely shorter... and makes so much more sense then the code I was using. 

 

Thanks again. Now I need to go and recode all the other loops I have going on my site.  :-\

[sintax63]

Fenway -

 

One more question for you as I'm chewing through this code.

 

DATE_FORMAT( `date`, '%m/%d' ) AS displayDate

 

Is it possible to do the same thing with my 'time' column? I was using that chunck of code in my above post to convert '07:30:00' in to "7:30 a.m." I tried a few variations of this:

 

DATE_FORMAT( `time`, '%g:%i %a' ) AS displayTime

 

... but it wasn't yielding desired results.

[Wildbug]

MySQL's DATE_FORMAT is different than PHP's.  See the formats by following the link.

[fenway]

There is an equivalent TIME_FORMAT() function.

[sintax63]

MySQL's DATE_FORMAT is different than PHP's.  See the formats by following the link.

 

That did just the trick - I was using the variables for for PHP and not MySQL.

 

Everything is up and running just fine now. Thanks guys!