Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2004
    Posts
    96
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date sorting problem with MySQL query

    Hi,

    I have a MySQL query to select all the entries in the database and order by date:

    Code:
    $query_get_forum_messages = "SELECT * FROM bookings, users WHERE theuserid = userid ORDER BY date ASC";

    However, all the dates are in the format 27012007 rather than 20070112. The current way causes the dates to list incorrectly, in that 01022007 follows 01012007, where it should be 02012007 following 01012007. Is there any way PHP might be able to help out? There are already a lot of entries and I'm looking to avoid going back and changing the whole system.

    Thanks in advance...

  • #2
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    I don't understand, how is 27012007 the same date as 20070112?

    Are the dates in a date field in the database? If so you can use MySQL's date functions:

    Alternatively, you can use PHP's date() function.

    HTH
    Dan
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,049
    Thanks
    2
    Thanked 317 Times in 309 Posts
    As whizard has pointed out, if your dates are stored in a mysql DATE type field, there are a number functions available to directly manipulate them and ORDER BY and comparisons work (for your existing scheme comparisons only work when the year is the same), and work as quickly as possible - http://dev.mysql.com/doc/refman/5.0/...functions.html

    By not storing them as a DATE type, you are left with using multiple individual string functions to build them into something that can be sorted, either in mysql or in php, which would be slower than if they were a DATE type.

    It is better to correct this type of thing sooner rather than later.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #4
    New Coder
    Join Date
    Sep 2004
    Posts
    96
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've changed the type in the database to date. So bearing in mind I have the list of dates stored in the field in the format DDMMYYYY could you tell me how I would sort by the latest date first? Thanks again

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I've changed the type in the database to date. So bearing in mind I have the list of dates stored in the field in the format DDMMYYYY could you tell me how I would sort by the latest date first? Thanks again
    MySQL stores "date" type values in a compressed format, not a DDMMYYYY format, so you can perform sort and comparisons and calculations on "date" type values to your heart's content (this is the value of using "date" types). Use the "ORDER BY" clause:

    Code:
    SELECT *
    FROM table_name
    WHERE ....
    ORDER BY date_field desc


  •  

    Posting Permissions

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