Query help, ORDER BY

[hostfreak]

Alright so I have come to a confusing problem (atleast confusing for me). I have a field in a database, named "edit_date". When an employees file is edited, it inserts the date they were edited into the database like , e.g: August, 31, 2006 .

I made a page that I want to display the employee's edited. I am ordering the results with: "ORDER BY edit_date ASC". As you probably already know, that won't work the way I want it to, because it will firstly order by the month. So say I have "September, 20, 2006" and the above date "August, 31, 2006", it will display "August, 31, 2006" above "September, 20, 2006". Which isn't showing the recently edited the way intended.

I was thinking I could explode the edit date (by ", ") into an array, then order by the month, day and year. But, I am not 100% sure how to combine that with the query? Or if that is even possible at all?

[Ninjakreborn]

ok, use
strtotime($date)
that will put it into a time stamp
then sort that, you don't have to pus ascending on there, because that's the default, it does it in that order automatically.  So what you will do is, pull it from the database.  Pull all of the information from the database.  Then turn all of the array, into timestamps, like I showed you above, then sort.  But still the easiest way, would be to get all the records put in there as time stampts.  Work up a script, have it pull all the dates from the database, turn them into timestamps and put them back IN the database as timestamps instead, it's called a bot, or atleast if you created it, you would be creating a bot, look for that in google.  You might find something helpful.

[hostfreak]

Thanks businessman. So are timestamps generally better to use? I'll have to take your suggestion and make a bot to change all my dates to timestamps if so.

[shoz]

Storing the dates in a column of type [url=http://dev.mysql.com/doc/refman/4.1/en/datetime.html]DATE[/url] will allow you to sort the results properly and also allow you to use the other MYSQL [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html]Date and Time Functions[/url] if needed. If the time of the edit is also relevant then storing the dates in a column of type [url=http://dev.mysql.com/doc/refman/4.1/en/datetime.html]DATETIME[/url] would be a better choice.

Depending on the version of MYSQL you're using the change can be done with queries only. Note also that the results can be ordered without any change (more slowly however).

Post the version of MYSQL you're using and also the part of the script that inserts the date after an edit.

[hostfreak]

I am using mysql version: 4.1.21-standard. For the edit date, all I do is put a hidden field that looks like:
[code]
<input type="hidden" name="edit_date" value="<?php echo date("F, j, Y"); ?>">
[/code]

Then the insert is just:
[code]
if (isset($_POST['submit'])) {
$edit_date = $_POST['edit_date'];
$query = "UPDATE employees SET edit_date = '$edit_date'"
$result = mysql_query($query) OR die(mysql_error());
}
[/code]

Probably not the most efficient way to do it?

[shoz]

[quote author=hostfreak link=topic=108051.msg435300#msg435300 date=1158369972]
I am using mysql version: 4.1.21-standard. For the edit date, all I do is put a hidden field that looks like:
[code]
<input type="hidden" name="edit_date" value="<?php echo date("F, j, Y"); ?>">
[/code]

Then the insert is just:
[code]
if (isset($_POST['submit'])) {
$edit_date = $_POST['edit_date'];
$query = "UPDATE employees SET edit_date = '$edit_date'"
$result = mysql_query($query) OR die(mysql_error());
}
[/code]

Probably not the most efficient way to do it?
[/quote]
Have a backup of your script and database before doing the following.

1) Remove the hidden input field

2) Change the query to the following
[code]
UPDATE employees SET edit_date = NOW()
[/code]

3) Issue the following queries to change the edit_date column type to DATETIME.
[code]
ALTER TABLE employees ADD edit_date_tmp DATETIME
UPDATE employees SET edit_date_tmp = STR_TO_DATE(edit_date, '%M, %e, %Y')
ALTER TABLE employees DROP edit_date
ALTER TABLE employees CHANGE edit_date_tmp edit_date DATETIME
[/code]

The column will now have the dates stored in 'YYYY-mm-dd HH:mm:ss' format. The edit dates already in the database will of course have 00:00:00 for their time.

I'm assuming you don't currently have other code that relies on the format of the edit_date column.