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,938
    Mentioned
    214 Post(s)
    Tagged
    12 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,496
    Mentioned
    163 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,938
    Mentioned
    214 Post(s)
    Tagged
    12 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,251
    Mentioned
    59 Post(s)
    Tagged
    3 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

    rudy.ca | @rudydotca
    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
  •