[SOLVED] Sorting and summoning results from Database

[axm]

Hey all, n00b to this forum! :] Ok I have a problem here goes:  I am building a website for a band, the homepage will list up-coming shows they are to play organised so that the closest date is at the top, and a maximum of 5 results are to be displayed.  If the date has been and gone it is to appear in the previous shows section in order of the most recently played.  I am confused as to how I should organise the list by date since the date is entered manually, will MySQL understand the date format?  I am using PHP4 and MySQL 5 I believe.  Any assistance would be much appreciated, many thanks. 

[fenway]

I don't understand your first question... and for the second, you had better be storing the SQL standard date format in your DB.

[rajivgonsalves]

You can see the following

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub

 

or my favorites

 

TO_DAYS function just abstract/add the date you want and use FROM_DAYS with combination with the date format your accpecting

[axm]

Thanks for the replies guys, maybe I didn't make myself clear sorry! There are two sections: Up-Coming Shows is to display the shows that are to be played with the closest show at the top, only 5 results are to be displayed.  The other section is the previous shows section that has all the shows that have been played previously (so once the date has been and gone in the Up-Coming Shows section it will appear here automatically with the most recent at the top).  I hope this makes things clearer!  The other question was how should the date be entered into the database since the results are to be ordered by the date?

 

Many thanks!!

[fenway]

OK... so you need two queries for the first 2 sections.

 

As for the date, like I said before, use a proper DATE field, and convert your input into the standard YYYY-MM-DD format.

[axm]

Thanks Fenway, I have done that.  So how do I only summon results that are in the future and not in the past?

[fenway]

WHERE yourDateTimeFile < NOW()

[axm]

Fantastic cheers Fenway its now working!!!!  Many thanks indeed :]