Jump to content

Query help, ORDER BY


hostfreak

Recommended Posts

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.