Page 1 of 1

Allways get next upcoming event!?

Posted: Fri Mar 23, 2007 2:53 am
by jmansa
How can I allways only get the next comming date/event from my calender.
Lets say that today is 23-3-07 and the next event is 27-3-07. Right now it prints only the first event in my calendar but I want to show the next coming event from the db when the date from the first has surpassed!

Code: Select all

$query="SELECT *, DATE_FORMAT(date, '%d-%m%-%Y') AS date FROM calendar WHERE date > NOW() LIMIT 1"; 
/*$query="SELECT * FROM calendar";*/ 
$result=mysql_query($query); 
$calendar = mysql_fetch_array($result);
I tryid this but with no luck... Where is my error? Can anybody please help...

Posted: Fri Mar 23, 2007 3:12 am
by Kieran Huggins
is date already a column name? if so, you can only retrieve it once (with the *) - try a different variable name like `dmyDate`

Posted: Fri Mar 23, 2007 3:57 am
by dude81

Code: Select all

SELECT *, DATE_FORMAT(date, '%d-%m%-%Y') AS date FROM calendar WHERE date > NOW() LIMIT 1
You are formatting the date before comparision in the query. i.e at date>NOW()
what happens here is your date comes 12-02-2006(will be some number mysql db calculates it to be)>2006-02-13(someother number).
Your comparision goes wrong.

Posted: Fri Mar 23, 2007 4:45 am
by jmansa
I tryid with this but with no luck.

Code: Select all

$query="SELECT * FROM calendar WHERE date > NOW() LIMIT 1";
I have done this before, but in asp... My code looked like this then!?!?

Code: Select all

strSQL = "SELECT TOP 1  calendar.*, calender.date FROM calendar WHERE (((calender.date)>=Date())) ORDER BY calender.date;"
I don't want to go back to asp... Please help... Can't see my eeror?!?!

Posted: Fri Mar 23, 2007 7:21 am
by aaronhall
How are you storing 'date'? [s]NOW() will return a unix timestamp, and won't work with DATETIME types or any non-standard string representation.[/s] NOW() will return a value compatible with DATETIME, not with a unix timestamp. (thanks for the correction, mikeq)

Posted: Fri Mar 23, 2007 10:40 am
by mikeq
works fine with a DATE type field.

also running

Code: Select all

SELECT NOW() FROM dual;
see what that returns.

Also tested against a DATETIME field, worked fine.

We are all talking about MySQL aren't we?