SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,313
    Mentioned
    178 Post(s)
    Tagged
    9 Thread(s)

    How to order MySQL datasets by date

    Hi,

    I'm trying to write a script that fetches a bunch of records from a MySQL database table and displays them in chronological order, starting with the most recent.

    So far I have:
    Code PHP:
    $query = "SELECT DATE_FORMAT(date,'%e.%c.%Y at %k:%i') AS date, title, name, message FROM guestbook ORDER BY date DESC";

    This more or less works, but it displays 9:00 and 11:00 incorrectly (the wrong way round). I'm guessing it would do the same for any time with only one digit before the colon.

    Sample output:
    Date: 12.4.2011 at 9:51
    Date: 12.4.2011 at 11:57
    Date: 12.4.2011 at 11:28
    Date: 12.4.2011 at 11:01
    Date: 11.4.2011 at 23:08
    Date: 11.4.2011 at 22:25
    Date: 11.4.2011 at 17:14

    What I would like:
    Date: 12.4.2011 at 11:57
    Date: 12.4.2011 at 11:28
    Date: 12.4.2011 at 11:01
    Date: 12.4.2011 at 9:51
    Date: 11.4.2011 at 23:08
    Date: 11.4.2011 at 22:25
    Date: 11.4.2011 at 17:14

    Can anyone give me a hand to sort this out?
    Any help gratefully appreciated.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Use another alias for the date in the select, so the order by will use the original date stored in the database.

  3. #3
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,313
    Mentioned
    178 Post(s)
    Tagged
    9 Thread(s)
    Cool, that did the trick.
    Thank you very much for your help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    even better would be to return only the datetime column, unformatted, properly sorted... and do all your formatting where it should be done, in the application layer

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •