>= NOW() problem???!

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
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

>= NOW() problem???!

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Maybe you should use CURDATE()
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Post by jmansa »

Thanks alot... The ORDER BY event_date_start ASC did the trick... Couldnt see the tree for the forest...
Post Reply