Retrieve record by date please help

[geroid]

Hi

Can anyone help me with this problem. I have records in a database and I want to display them by a date condition. I want a record to be displayed if the date is later than yesterdays date. That is to say only records from todays date onwards should be retrieved and the older ones left behind. Records should not be displayed that are older than todays date or future dates.

Any ideas what to add as a where clause to my code?

 

$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  date_format(event_date, '%M %D, %Y ') as formatteddate, event_time from $table order by event_num desc";
$results = mysql_query($query, $link) or die("Sorry, We could not connect to the database"); 

[watsmyname]

  Quote

Hi

Can anyone help me with this problem. I have records in a database and I want to display them by a date condition. I want a record to be displayed if the date is later than yesterdays date. That is to say only records from todays date onwards should be retrieved and the older ones left behind. Records should not be displayed that are older than todays date or future dates.

Any ideas what to add as a where clause to my code?

 

$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  date_format(event_date, '%M %D, %Y ') as formatteddate, event_time from $table order by event_num desc";
$results = mysql_query($query, $link) or die("Sorry, We could not connect to the database"); 

Do something like this

<?php
$yesterday= mktime(0, 0, 0, date("m"), date("d")-1, date("y"));
$yesterday= date("Y-m-d",$yesterday);
$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  date_format(event_date, '%M %D, %Y ') as formatteddate, event_time from $table WHERE DATE_FORMAT(event_date,'%Y-%m-%d')>$yesterday order by event_num desc";
?>

[abazoskib]

  Quote

$query = "select event_num, event_name, venue_name, streetaddress1, town, county, event_description_ire, event_description_eng,  date_format(event_date, '%M %D, %Y ') as formatteddate, event_time from $table WHERE DAYOFYEAR(event_date) > DAYOFYEAR(DATE_SUB(NOW(), INTERVAL 2 DAY)) order by event_num desc";
$results = mysql_query($query, $link) or die("Sorry, We could not connect to the database"); 

That should work for most of the year. I think it might failt on January 1st, so best way to combat that is check for dayofyear, and year.

[watsmyname]

I missed ")" in my query, i have edited my post check it

[geroid]

Thanks for the replies. They have given me some ideas. I'll experiment with that.