PHP MySql Sorting by date.

Posted on 2005-05-10
Last Modified: 2013-12-12
I have a database where the last colum has a "date" inserted. I would like the date to be displayed as MM/Day/YYYY. However i would also like people to change the sorting order from asscending to descending, for that to work properly, i am needing to display my date with YYYY/MM/day, is there anyways around this. I would like to be able to sort by Year then Month, but display the results with Month, then day, then year.

Question by:ohhhnick
    LVL 33

    Accepted Solution

    If the column type is 'date', you can sort it by that column in any order you want, and just display it formatted differently.

    SELECT DATE_FORMAT(dateField, '%m/%d/%Y') AS displayDate FROM theTable ORDER BY dateField DESC;

    SELECT dateField FROM theTable ORDER BY dateField DESC;
    echo date('m/d/Y', strtotime($row['dateField']));
    LVL 9

    Expert Comment

    sort the date as is in descending order

    select * from tablename order by datefield desc

    then in PHP chage the format...

    $date = date('m/d/Y',strtotime($rec['datefield']));

    LVL 9

    Expert Comment

    Sorry snoyes... didn't see you post before sending mine.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to count occurrences of each item in an array.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now