Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
-
bytte
- Forum Commoner
- Posts: 75
- Joined: Sun Nov 23, 2003 8:20 am
- Location: Belgium
Post
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.
-
feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Post
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
-
bytte
- Forum Commoner
- Posts: 75
- Joined: Sun Nov 23, 2003 8:20 am
- Location: Belgium
Post
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?
-
feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Post
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.
-
bytte
- Forum Commoner
- Posts: 75
- Joined: Sun Nov 23, 2003 8:20 am
- Location: Belgium
Post
by bytte »
so the {} signs tell mysql that it's a DATE field and not a math thing?
thanks!
-
feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Post
by feyd »
no.. the quotes tell mysql to compare the string '2004-01-06' against the dates.
-
bytte
- Forum Commoner
- Posts: 75
- Joined: Sun Nov 23, 2003 8:20 am
- Location: Belgium
Post
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";
?>