Page 1 of 1

>= NOW() problem???!

Posted: Sun Apr 01, 2007 9:53 am
by jmansa
I'm trying to make an event calendar where I want 2 show only the next upcoming event. I allmost got it to work but I have 2 problems!

1. By using the script below I got it to show the next coming event, but i changes on the date of the event, and I want it to change on the first day after the event date. Ex. If I have a event the 5 of march 2007 and the next event is the 8 of march I want to be able to show the 5 of march event also on the 5 of march and then it changes to the next event on the 6 of march!

Code: Select all

$query="SELECT *, DATE_FORMAT(event_date_start, '%d-%m%-%Y') AS date FROM calendar WHERE event_date_start >= NOW() LIMIT 1";
2. In my db the dates are inserted as this: 2007-04-05. That means that the above script doesn't regonize the date and list them by "id". So if I create a new event later on but the date is in between to other events, the script doesn't show the right next upcomming event by date but by "id".

I think the main problem is in issue 2, but I can't figure this out!

Hope the help is not far away. THANX!

Posted: Sun Apr 01, 2007 10:11 am
by feyd
Maybe you should use CURDATE()

Re: >= NOW() problem???!

Posted: Sun Apr 01, 2007 10:18 am
by timvw
jmansa wrote: 1. By using the script below I got it to show the next coming event

Code: Select all

SELECT *, DATE_FORMAT(event_date_start, '%d-%m%-%Y') AS date FROM calendar WHERE event_date_start >= NOW() LIMIT 1

[/quote]

Not really, the query returns 1 row from all the rows where event_date_start is greater than or equal to now... 

If you know there will always be one event starting on a given date, you can simply add an ORDER BY event_date_start ASC clause so that you get the event that will start the earliest...

If there are events that start on the second date you'll have to add additional conditions to the ORDER BY clausse...

[quote="jmansa"]
the above script doesn't regonize the date and list them by "id"
[/quote]

I don't understand what you're trying to say here...

Posted: Sun Apr 01, 2007 10:22 am
by feyd
An example

Code: Select all

mysql> select '2007-04-01' >= NOW(), '2007-04-01' >= '2007-04-01 10:21:25', '2007-04-01' >= CURDATE();
+-----------------------+---------------------------------------+---------------------------+
| '2007-04-01' >= NOW() | '2007-04-01' >= '2007-04-01 10:21:25' | '2007-04-01' >= CURDATE() |
+-----------------------+---------------------------------------+---------------------------+
|                     0 |                                     0 |                         1 |
+-----------------------+---------------------------------------+---------------------------+
1 row in set (0.01 sec)

Posted: Sun Apr 01, 2007 1:08 pm
by jmansa
Thanks alot... The ORDER BY event_date_start ASC did the trick... Couldnt see the tree for the forest...