mysql & php, how to filter rows with upcoming date

[martez]

Hi I have a mysql database that stores upcoming events info. I want to filter records the way that events with past date won't show on the website but only ones that are due to come. Is there any simple mysql command to do it or I have to do some php scripting that would check if given record is past date or not. Any help very appreciated!

 

Thanks

Marcin

[ram4nd]

Umm I would do it with while and if. I would take the data with while and make 1 if in that while. That if will compare that date with the server time.

[IndyTechNerd]

Hi I have a mysql database that stores upcoming events info. I want to filter records the way that events with past date won't show on the website but only ones that are due to come. Is there any simple mysql command to do it or I have to do some php scripting that would check if given record is past date or not. Any help very appreciated!

 

Thanks

Marcin

 

$today = date('m-d-y'); // be sure to format the date the same as your date field in the db
$query = "SELECT * FROM events WHERE event_date >= '$today'";

[martez]

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

 

$query ="SELECT events.*, DATE_FORMAT(event_date, '%m-%d-%Y') as event_date, event_detail.* FROM events LEFT JOIN event_detail ON events.event_id = event_detail.event_id ORDER BY event_date WHERE event_date >= '$today' DESC LIMIT 10";

 

Indy:

I did it the way you suggested and I get no records. All I changed in the query was adding (WHERE event_date >= '$today') Without that piece of code query runs fine giving me a list of all events.

 

any ideas what's not working here?

[IndyTechNerd]

How is your event_date stored normally, without adding DATE_FORMAT(event_date, '%m-%d-%Y')?

 

What I'd do is change the format of $today to match your date string in the db.

 

For example, I have a 'news' table with post dates stored as "Y-m-d H:i:s", so using Jan 1, 2008 as my 'today', I ran this...

SELECT * FROM news WHERE postdate >= '2008-01-01 00:00:00';

and it worked like a charm.

[martez]

Dates are stored in database in Y-m-d format. I changed the query by removing DATE_FORMAT but it still doesn't return me any rows.

 

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

 

$query ="SELECT events.*, event_detail.* FROM events LEFT JOIN event_detail ON events.event_id = event_detail.event_id ORDER BY event_date WHERE event_date >= '$today' DESC LIMIT 10";

 

hmmm...

[IndyTechNerd]

Try moving your ORDER BY event_date to after the WHERE...

 

$query ="SELECT events.*, event_detail.* FROM events LEFT JOIN event_detail ON events.event_id = event_detail.event_id  WHERE event_date >= '$today' ORDER BY event_date DESC LIMIT 10";