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
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
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";
?>