sorting dates

[Woodburn2006]

i have a db full of events and i need to display the next event on a page. is there any way i can sort the dates after i have got them from the db or do i need to sort them in the sql query and how would i do it?

 

thanks

[Wes1890]

Well if you have a column in your table for storing dates (example, storedate), you can store the time() value in that column.

 

Then, when selecting your data from the DB, just add ORDER BY storedate ASC at the end of your query (ASC stand for Ascending, if you want them in Descending order, change ASC to DESC)

[obsidian]

i have a db full of events and i need to display the next event on a page. is there any way i can sort the dates after i have got them from the db or do i need to sort them in the sql query and how would i do it?

 

thanks

What format are the dates in? If they are stored as a DATETIME or other valid TIME type, you would be best off to sort them in a query. If you are looking for all upcoming events, something like this should get you started:

SELECT *
FROM events_table
WHERE event_time > NOW()
ORDER BY event_time

[Woodburn2006]

ive only just set the table up so i can change it to any format i need to. i am in the uk so if i was to save it as DATETIME would i need to do it the american way or uk way?

 

e.g american way for 19th Nov would be: 11/19/2007

uk would be: 19/11/2007

 

 

[cooldude832]

no store your dates as Unix timestamps it makes so much more sense, in a single number you have year, month, day, hour, minute, second.  So querying for results in an order are very easy, one note on the NOW() is that it will return now in a unix time stamp so if you say have an event that starts at 8AM today but it is now 10 AM then you will not get that event, i prefer to query today with the hours, minutes, seconds all being zero.

[Wes1890]

It  would go by the server's time probably. So you should have any problems there, but if you do, just format it when you display it on your page by using the Create_date() functions in php

[Woodburn2006]

how do i store it as unix timestamps, im quite new to using dates etc in my sites? and how do i use the NOW() function to retrieve them?

[Wes1890]

Here you go my friend:

 

http://www.devshed.com/c/a/MySQL/Practical-Date-Time-examples-with-PHP-and-MySQL/

 

or, search with google:

 

http://www.google.com/search?source=ig&hl=en&rlz=&q=using+dates+and+php+mysql&btnG=Google+Search

[obsidian]

no store your dates as Unix timestamps it makes so much more sense, in a single number you have year, month, day, hour, minute, second.

 

Why? If you're allowing MySQL to do the work for you, you have any number of DATE/TIME functions at your disposal to do the calculations. Storing as a timestamp has its place, but IMHO, this is not it.

 

ive only just set the table up so i can change it to any format i need to. i am in the uk so if i was to save it as DATETIME would i need to do it the american way or uk way?

 

e.g american way for 19th Nov would be: 11/19/2007

uk would be: 19/11/2007

 

Neither, actually. DATETIME has a constant format of YYYY-MM-DD HH:MM:SS. You would then just parse it into the format of your choosing upon retrieval using either the MySQL DATE_FORMAT() function or the PHP strtotime() and date() functions.

[cooldude832]

I guess i just like using a single method for a time, I have rewritten the time function a bit for myself so I can return formated times or deformatted times very easily.

[obsidian]

I guess i just like using a single method for a time, I have rewritten the time function a bit for myself so I can return formated times or deformatted times very easily.

 

True. Also, IMHO, there's nothing inherently wrong with one over the other, but each has its place, and the more I can let MySQL do for me, the better

[Woodburn2006]

cool thanks alot, i have stored all my dates in the datetime format, how would i retrieve only the next event from this?

[cooldude832]

True. Also, IMHO, there's nothing inherently wrong with one over the other, but each has its place, and the more I can let MySQL do for me, the better

 

I'm starting to transition too, in the past I just wrote "sloppy" queries that grabbed tons of "junk" data and let php do the rest, I have now refined it a lot and dont' waste resources, but I must say the most important thing to databasing is having a strong sense of how to design a database to do its work.

[obsidian]

cool thanks alot, i have stored all my dates in the datetime format, how would i retrieve only the next event from this?

 

Try this:

SELECT * 
FROM table
WHERE my_datetime > NOW()
ORDER BY my_datetime
LIMIT 1