Page 1 of 1

comparing date

Posted: Thu Jan 06, 2005 4:07 pm
by bytte
Is it possible to compare date fields with mysql?
To find the next exposition I do this query:

Code: Select all

$sql = "SELECT ID FROM expositions WHERE  date_start > " . $date_start . " ORDER BY date_start ASC LIMIT 1";
Where date_start is a DATE field.
The variable $date_start is the date of the current exposition.

However, it returns the same ID as the current exposition.

Posted: Thu Jan 06, 2005 4:17 pm
by feyd

Code: Select all

$sql = "SELECT ID FROM expositions WHERE date_start > '{$date_start}' ORDER BY date_start ASC LIMIT 1";
and please use

Code: Select all

tags.

Posted: Thu Jan 06, 2005 4:22 pm
by bytte
Thanks. And sorry for not using the php tags.
May I ask you; why doesn't the first one work and what's the difference between the two?

Posted: Thu Jan 06, 2005 4:27 pm
by feyd
your date comes out into the query as somthing like

Code: Select all

2005-01-06
without quotes, that's math. 2005 minus 1 minus 6.. I'm going to guess that the "current" id is infact the first id when the dates are sorted.

Posted: Thu Jan 06, 2005 4:29 pm
by bytte
so the {} signs tell mysql that it's a DATE field and not a math thing?
thanks!

Posted: Thu Jan 06, 2005 4:30 pm
by feyd
no.. the quotes tell mysql to compare the string '2004-01-06' against the dates.

Posted: Thu Jan 06, 2005 4:31 pm
by bytte
Allright, I see, so this would work as well:

Code: Select all

<?php
$sql = "SELECT ID FROM expositions WHERE  date_start > '" . $date_start . "' ORDER BY date_start ASC LIMIT 1";
?>