Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2006
    Posts
    41

    Arrow Mysql Natural Sorting

    Hi!
    The correct order for alphanumeric characters is this:
    Untitled - Set #17
    Untitled - Set #18
    Untitled - Set #2
    Untitled - Set #3
    But a human would order that this way:
    Untitled - Set #2
    Untitled - Set #3
    Untitled - Set #17
    Untitled - Set #18

    So I have this mysql query in a php page:
    ORDER BY a.title
    the result is:
    Untitled - Set #17
    Untitled - Set #18
    Untitled - Set #2
    Untitled - Set #3

    I have modified the mysql query this way:
    ORDER BY substring_index(a.title,\'#\',1), cast(substring_index(a.title,\'#\',-1) as unsigned)
    the result it:
    Untitled - Set #2
    Untitled - Set #3
    Untitled - Set #17
    Untitled - Set #18

    But it works only if the names contain # before the number!
    Is there a way to make it work always? Also when there isn't # before numbers? For example if they are colled simply set 1, set 2, set 3, set 10, set 12 etc.? Thanks
    Last edited by AvrilBoi; 09-02-2006 at 07:09 PM.

  2. #2
    They have a similar problem solved here :

    http://codewalkers.com/archives/sqlhelp/106.html

    hope that helps.
    Cheap web hosting
    Any CMS pre-installed
    Joomla Hosting

  3. #3
    Join Date
    Jun 2006
    Posts
    41
    ^ Thanks, but it is not solved at all... I think the final solution is "substring", and I've already done it, but works only with names which contain # before the number... any ideas to make it work always?

  4. #4
    OK I will create a database and test it with some queries. Will tell you the result in a bit.

    Update :
    I have created a test table with :
    Untitled - Set 2
    Untitled - Set 3
    Untitled - Set 12
    Untitled - Set 17

    Using this query :
    Code:
    SELECT * 
    FROM test
    ORDER BY SUBSTRING( title, 15 ) +0
    LIMIT 0 , 30
    you get the desired result. Keep in mind that this only works with "Untitled - Set" string. If you change it you'll have to change the 15 as well. Good luck
    Last edited by maiahost; 09-03-2006 at 08:19 AM.
    Cheap web hosting
    Any CMS pre-installed
    Joomla Hosting

  5. #5
    Join Date
    Jun 2006
    Posts
    41
    Thanks again!
    But this only works with "Untitled - Set *", and the one I posted only works with "Untitled - Set #*"... so the problem is still unsolved... I mean, I could have used the query I posted before, but I asked for an universal query which doesn't work only for "Untitled - Set *" or "Untitled - Set #*"...
    If that's not possibile, thanks anyway for the query you provided!

  6. #6
    Is there a way to make it work always? Also when there isn't # before numbers? For example if they are colled simply set 1, set 2, set 3, set 10, set 12 etc.? Thanks
    I had the impression that you needed exactly this but ... Unfortunately I'll have to say you should change the design of the database by adding an extra field with the set number. This way you can modify the info easily.
    Cheap web hosting
    Any CMS pre-installed
    Joomla Hosting

  7. #7
    Join Date
    Jun 2006
    Posts
    41
    I can't change that... it is a gallery, and the names are the names of the albums, and there are some albums called
    Untitled - Set #1
    others called "under the bed photoshoot" or something like that... and I would have liked to order them alphabetically, and also to apply a natural sorting for the albums which contain also numbers... but that's not possibile I see...
    thanks

  8. #8
    well in that case you can simply change the id (if they have it) and order by id. Not really sure if that will help but it's worth a shot.
    Cheap web hosting
    Any CMS pre-installed
    Joomla Hosting

  9. #9
    Join Date
    Jun 2006
    Posts
    41
    I know I could change by the album id, but I would like to add albums in a "natural way" and let the php/mysql make the natural sorting, you know... today I add "yellow photoshoot", tomorrow I add "Set #3", and "Set #3" will come automatically after "Set #2" that I put yesterday...
    I'm using this for now:
    ORDER BY substring_index(a.title,\'#\',1), cast(substring_index(a.title,\'#\',-1) as unsigned)
    Last edited by AvrilBoi; 09-03-2006 at 12:58 PM.

  10. #10
    OK if it works for you that's fine. Another thing you can try is to give them 001 002 003 004 etc names so it sorts correctly.
    Cheap web hosting
    Any CMS pre-installed
    Joomla Hosting

  11. #11
    Join Date
    May 2004
    Location
    NYC
    Posts
    793
    Quote Originally Posted by AvrilBoi
    I know I could change by the album id, but I would like to add albums in a "natural way" and let the php/mysql make the natural sorting, you know... today I add "yellow photoshoot", tomorrow I add "Set #3", and "Set #3" will come automatically after "Set #2" that I put yesterday...
    I'm using this for now:
    ORDER BY substring_index(a.title,\'#\',1), cast(substring_index(a.title,\'#\',-1) as unsigned)
    Maybe add a timestamp field? That way, when you add an album it automatically gets a timestamp, and you can sort on that field when you get the records back, regardless of what the album is called.

Posting Permissions

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