date order

[tribalmaniac]

at the moment, i'm using this code:

$postSql = mysql_query("SELECT *, DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") FROM `post` WHERE `threadid` = '$id' ORDER BY `date` ASC");

but it orders the items by month first, then the date/time

This is for a forum so the order needs to be oldest to newest, any ideas?

Thanks

[Fumigator]

If your field named "date" is an actual date-type field (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html), it will sort perfectly. This is one really good reason to store your dates in date-type fields (as opposed to char-type fields).

[tribalmaniac]

If your field named "date" is an actual date-type field (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html), it will sort perfectly. This is one really good reason to store your dates in date-type fields (as opposed to char-type fields).

hey, thanks for the reply!
Ive changed the MySQL format to DATETIME, and I'm entering dates as follows:

2006-10-02 06:06:12

But I'm not too sure about how to convert this back to the format:

F dS, Y h:i A

And then how to convert that back to the MySQL format?

[Fumigator]

The way it works is it always stores the date in a certain format. You need not worry about this format-- it can be thought of as "internal stuff". Most relational databases always makes sure the value stored in a date, time, timestamp, or datetime field is a valid value-- that is, the value is a valid date or time. (I should note unfortunately MySQL allows some invalid dates to be stored, such as feburary 31st, but the general rule in relational databases is date and time fields always have valid date and time values in them.)

Once you store a date in a field, you can then format that date any way you like using the date_format() function. The actual date stored doesn't change, but the value that is retrieved from the database is formatted the way you prefer. Even if you insert or update a date field and try to "force" a particular date format into the field, the actual stored date will look like all the other dates in all the other date-type fields. Some databases (such as DB2) will even pack a date value in such a way that it's not even readable as a date value just to save space.

So use that date_format() function just like you did in the code you posted, and it will come out looking like you want it to look. Hope this clears things up for you.

[tribalmaniac]

hey, thanks again for the reply :)

My code looks like this:


$postSql = mysql_query("SELECT *, DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") FROM `post` WHERE `threadid` = '$id' ORDER BY `date` ASC");
while ($post = mysql_fetch_array($postSql, MYSQL_ASSOC))
{

$postid = $post["id"];
$postcontent = $post["content"];
$postdate = $post["date"];
$author = $post["authorid"];
echo($postdate);
}



but the date still comes out in this format:
2006-10-02 03:37:36

[guelphdad]

now the problem is obvious, it is because you are selecting date and not your formatted date. in order to select that you need to give your formatted date column an alias and refer to that.


$postSql = mysql_query("SELECT *,
DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") as formatted FROM `post`
WHERE `threadid` = '$id' ORDER BY `date` ASC");
while ($post = mysql_fetch_array($postSql, MYSQL_ASSOC))
{

$postid = $post["id"];
$postcontent = $post["content"];
$postdate = $post["formatted"];
$author = $post["authorid"];
echo($postdate);
}



also get out of the habit of using select * only select the columns you are displaying.

Also to note that %F and %A specifiers do not exist in date formatting in mysql. Perhaps you are thinking of different ones in PHP. Check the date and time functions (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) area of the manual to choose the appropriate specifiers to display your date like you want.