php date ordering problem

Hi, this is frustratingly simple but i can't work out the problem

I want to find the next upcoming event in the database... by looking at the event date. I'm doing this by storing todays date as $todaysdate and then in the WHERE clause looking for "gigdate >= $todaysdate". The gigdate field is formatted as 'date' in the database (so stored as 2012-12-01 etc..).


$todaysdate = date("Y-m-d");
$findnextgig = mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= $todaysdate");

if($nextgig = mysql_fetch_array($findnextgig)){
echo $nextgig['gigid'];
}
else
{
echo "<p>no upcoming gigs</p>";
}


I have 3 piece of test data in the table, each 2011, 2012, and 2013 dates. And it keeps showing the ID for the 2011 date?

Hi
To use mathematical operators, like '<' to compare two values, they must be numerical. So, in this case try use Timestamp:
$todaysdate = time();
$findnextgig = mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND UNIX_TIMESTAMP(gigdate) >= $todaysdate");

web five

you can use mathematical operators with the values in date/datetime fields but you need to quote your date string. 2012-04-14 doesn't say the 14th of April 2012, it says 2012 minus 4 minus 14


$todaysdate = date("Y-m-d");
$findnextgig = mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= '$todaysdate' order by gigdate limit 0,1");

Thank you Nancy that works perfectly.

(..bit of a facepalm moment now you've pointed out the subtracting of the numbers).

You don't need PHP for the actual date, and I'd drop that too. Assuming that you are using a subset of the DATETIME datatype in MySQL (which appear to be the case), you can simply use:

SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= CURDATE() order by gigdate limit 0,1

And put the burden on MySQL. That will compare just the date parts of the gigdate to today. If gigdate includes a time, you can use the NOW() function instead to ignore records where gigdate has passed the time for today. I'd do this in something like a listing of upcoming (since its no longer upcoming but passed), and I'd use curdate() for something like a calendar view (since its not complete until the day is done).

Sweet, thank you.