comparing date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bytte
Forum Commoner
Posts: 75
Joined: Sun Nov 23, 2003 8:20 am
Location: Belgium

comparing date

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.
User avatar
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

Code: Select all

tags.
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?
User avatar
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

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.
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!
User avatar
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";
?>
Post Reply