trouble sorting my dates in order

[Smad]

hi all,

In my DB there are date entries which have been manually entered for example, Jan 10th - 14th 2005, Mar 5th - 9th 2005 etc etc, Now i have been trying to come up with a way so that when chosen to sort by date it will query the DB and print them in order of date. Can someone point me in the right direction here please as all the methods i am trying dont seem to be working :(

TIA

Smad

[willis1480]

Well, I am no MYSQL genius by any means, but you should really consider doing the dates in a date format. You should have an interface that adds dates in this format written in php or something. This way you can have a real nice user interface for inserting dates.

If that is not a possibility then I would recomend writing a php script that will grab the data from db and then order them.

Build an array will all your db information in it. You should parse the dates as they come (change them to a good format) in to be in and then write a script to insert these dates back into either a new db or the origional. ( this would be a script to convert your "bad" dates to good ones, making it easy to search your db).

[tomda]

Indeed,

Your date field in your database should be in "timestamp" format, that is "yyyy-mm-dd hh:mm:ss". The value by default is "0000-00-00 00:00:00"

You need to write a php script that change your month (Jan, Feb, etc...) in digit format.

To create the timestamp, you should use something like:
$timestamp = "".$year."-".$digit_month."-".$day." ".$hour.":".$minute.":".$second."";

*************
Now if you date is coming from a form and data are immediatly placed in your database after clicking the submit button, then it is very easy, just set now() as default value in your date field. Your timestamp will be automatically added to your db.

[Smad]

thanks for the help guys

it does look like i will need to redo the form so it inserts proper dates into the DB, any chance anyone knows of a good calendar they can recommend which i can incorporate in my form as the dates are future ones. so a dynamic calendar would be ideal.

[Smad]

all sorted now regarding that.. do have a problem left

i am using DATE_FORMAT(mycolumn, '%d %m %Y') asc to sort the dates but its mixing 2004 with 2005, date format in DB is 01-01-2001 for example. How can i make it take notice of the year too please

TIA

[coopster]

Two issues, you really should store the date in the yyyy-mm-dd format. It is a good practice and going to save you headaches down the road. While you are at it, save it as a

date

type column. Most databases have lots of nice built-in functions as well as the standards that allow date manipulation, but only when the column is stored as type

date

.

Second, you can SELECT the date in one format, but sort it in another.

SELECT 
 DATE_FORMAT(mydate, '%d %m %Y') AS formatted_date 
 FROM table 
 ORDER BY DATE_FORMAT(mydate, '%Y %m %d') DESC 
;

However, you won't need to do this if you store the column as type

date

as described earlier:

SELECT 
 DATE_FORMAT(mydate, '%d %m %Y') AS formatted_date 
 FROM table 
 ORDER BY mydate DESC 
;

[Smad]

ok my database now uses date 0000-00-00, my only problem now is that from the form the date is generated by isjavascript and comes into my processing page as m d Y, i have tried to convert it but its going crazy. from a global var $_POST['mydate'] how can i redo it simply so it changes it to Y m d. the various date() attempts seem to mess up the date.

Thanks

[tomda]

My advice, forget javascript!
Specially if you use it to generate the date when the form has been submitted.

As said above, if data are immediatly inserted in your database after submitting the form, use the now() statement to put the current date in the db in a timestamp format.

Otherwise, use PHP to generate you date. Javascript is old fashion and a client-side script to get a date is a bad idea (what is Javascript is disable?).

[Smad]

well it has to display a calendar so the date can be selected then inserted, i couldnt find a php version of this. :(

[coopster]

If js is the only option to enter a date, then tomda is correct, anybody that doesn't have js activated in their browser will not be able to use your page to select a date. Just be aware of that.

You could modify the script on the javascript side to pass the date in the format you want. Otherwise, you can put it together on the server-side using substr() functions.

$date = '07042004'; // example incoming date 
$month = substr ($date,0,2); 
$day = substr ($date,2,2); 
$year = substr ($date,4,4); 
$newdate = $year.$month.$day;