Problems in date DESC after changing format

[bayruni]

My php application used to display the date as YEAR-MM-DD HH:MM:SS. Not pretty. So, I made it display it in a more friendly way like April 01, 2006, 03:30:29 PM.

I thought everything was great and was getting ready to celebrate, only I noticed that when I added new articles that date was not arranged correctly. If articles were posted in March, they would all be arranged in DESC order. And articles posted in February would appear under those in March. No problem, so far, except that articles posted on April would be listed way at the bottom.

I'm guessing something in my code is making it arrange the articles by date in alphabetical order as well. March > Feb > April...

Here is my query:

PHP Code:

$query_rsArticles = "SELECT *, DATE_FORMAT(article_date, '%M %d, %Y, %r') AS article_date FROM np_articles INNER JOIN np_categories ON article_category_id=category_id ORDER BY article_date DESC"; 


And this bit displays the date under the article:

PHP Code:

<?php echo $row_rsArticles['article_date']; ?>

The date appears fine, just like I want it to be, BUT it is arranged in alphabetical order (months) and I don't want that to happen.

Suggestions, anyone?

[sde]

there is a mysql function called unix_timestamp() that will return the date in that format. then you can use PHP's date() function to format it how you did in the other part of your code.

example:

PHP Code:

<?
$result = mysql_query("select unix_timestamp(article_date) as date from mytable where article_id='1'");

if ($row = mysql_fetch_assoc($result)) {
  $date = date('F d, Y, h:i:s A', $row['date']);
}
?>

here's something i wrote a long time ago. same thing, different example
http://php.codenewbie.com/articles/p...mp-Page_1.html

[bayruni]

I get January 01, 1970, 03:00:00 AM as my date...
Other than adding this code and editing my table names and such, is there anything I need to do in CocoaMySQL? Should I change my article_date column to show timestamp instead of datetime?

And, how will this unix timestamp fix the order of articles (my main problem)?

Edit:
I found that if I display the date as 04/02/06 along with the time, my problem is solved and articles are arranged correctly by date. But I don't want the 04. I want it to show April instead.

[sde]

are you using the date/time field in mysql?

[bayruni]

Yes, I'm using datetime.

[sde]

i'm lost on what exactly your problem is. if there is still a problem, demonstrate a query you use to insert the datetime field.

you've already demonstrated how you get it out. for getting it out, i prefer the unix_timestamp method because PHP has more date formatting options than MySQL.

some go as far as to just store the unix time stamp in an int(14) field. i prefer to keep it in a datetime format in the database so i can utilize the MySQL date functions in my queries.

[sde]

well i think i see your problem. AS article_date you are sorting by the article_date. i believe the order by clause is using the field name you specify after anything you do with it in the query.

try using AS formated_date and order by article_date desc

[bayruni]

You're right. Thanks very much. I fixed it, everything looks right.
I actually wasn't aware that AS article_date could be anything I wanted... I thought it had to match the column name. Well, we live, we learn...