While Loop fetch array question

Hi,

I'm trying to take a information from an SQL table and then display it in date order.

I've managed that bit - but I want to be able to only show items where date is todays date or newer (ie I don't want to show old events - I want them to dissapear once the event has happened)

<?php
include("sql_link.php");

$secdata = mysql_query("SELECT * FROM FrontPage ORDER BY Date")
or die(my_sql_error());

$secinfo = mysql_fetch_array( $secdata );
			
while($secinfo = mysql_fetch_array( $secdata ))
{
echo "<hr width='100%'>";
echo "<h1>" . $secinfo['Event_Title'] . "&nbsp;&nbsp;&nbsp;<sub>" . $secinfo['Show_Type'] . "</sub></h1>";
echo "<h1><sup><i>&nbsp;&nbsp;&nbsp;" . $secinfo['Date'] . "</sup>&nbsp;<sub>" . $secinfo['Time'] . "</i></sub></h1>";
echo "<p>" . $secinfo['Synopsis'] . "</p>";
echo "<p><b>Box Office: </b>" . $secinfo['Box_Office'] . "</p>";
}
?>

PHP:

I'm fairly sure it should be so simple - but I can't work it out


Many Thanks,

Andrew

 

Try removing the first fetch_array function.

$secdata = mysql_query("SELECT * FROM FrontPage ORDER BY Date")
or die(mysql_error());

while($secinfo = mysql_fetch_array( $secdata ))
{
echo "<hr width='100%'>";
echo "<h1>" . $secinfo['Event_Title'] . "&nbsp;&nbsp;&nbsp;<sub>" . $secinfo['Show_Type'] . "</sub></h1>";
echo "<h1><sup><i>&nbsp;&nbsp;&nbsp;" . $secinfo['Date'] . "</sup>&nbsp;<sub>" . $secinfo['Time'] . "</i></sub></h1>";
echo "<p>" . $secinfo['Synopsis'] . "</p>";
echo "<p><b>Box Office: </b>" . $secinfo['Box_Office'] . "</p>";
}
?>

PHP:

 

Hi,

Maybe you need something like this in your SQL statement:

$secdata = mysql_query("SELECT * FROM FrontPage WHERE Date >= curdate() ORDER BY Date")
or die(my_sql_error());

Does that help?

Gary

 

Kind of - I have a date value on one of the events that is 2008-06-14... but now it seems to only show items in 2009???

Any Ideas?

 

Andrew,

You say in your original post that you wanted records with "date is todays date or newer" so records in 2009 are correct. I guess you want today's ones first so use "ORDER BY Date DESC"

I never get ASC or DESC the right way round so if DESC doesn't work tryt ASC!!!

Gary

 

Having had a play - it seems that only events with a date after 2008-07-31 seem to work? And also, if I have events with the same date - only one will show.

 

Do you have any way to verify the date and time on your server?

Gary

 

Are you storing the date in the database as one field? You could try this:

<?php

$today = date('Y-m-d');

$secdata = mysql_query("SELECT * FROM FrontPage WHERE Date >= '$today' ORDER BY Date") or die(mysql_error());

while($secinfo = mysql_fetch_array($secdata))
{
	echo "<hr width='100%'>";
	echo "<h1>" . $secinfo['Event_Title'] . "&nbsp;&nbsp;&nbsp;<sub>" . $secinfo['Show_Type'] . "</sub></h1>";
	echo "<h1><sup><i>&nbsp;&nbsp;&nbsp;" . $secinfo['Date'] . "</sup>&nbsp;<sub>" . $secinfo['Time'] . "</i></sub></h1>";
	echo "<p>" . $secinfo['Synopsis'] . "</p>";
	echo "<p><b>Box Office: </b>" . $secinfo['Box_Office'] . "</p>";
}

?>

PHP:

I had this problem
Easy way to remember:
Ascending stairs - you start at the bottom (or the lowest) step.
Descending stairs - you start at the top (or the highest) step.