Results 1 to 11 of 11
Thread: Mysql Natural Sorting
-
09-02-2006, 06:56 PM #1Junior Guru Wannabe
- Join Date
- Jun 2006
- Posts
- 41
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.? ThanksLast edited by AvrilBoi; 09-02-2006 at 07:09 PM.
-
09-03-2006, 03:54 AM #2Web Hosting Guru
- Join Date
- May 2006
- Posts
- 279
They have a similar problem solved here :
http://codewalkers.com/archives/sqlhelp/106.html
hope that helps.
-
09-03-2006, 06:38 AM #3Junior Guru Wannabe
- 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?
-
09-03-2006, 08:09 AM #4Web Hosting Guru
- Join Date
- May 2006
- Posts
- 279
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
Last edited by maiahost; 09-03-2006 at 08:19 AM.
-
09-03-2006, 08:30 AM #5Junior Guru Wannabe
- 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!
-
09-03-2006, 08:43 AM #6Web Hosting Guru
- Join Date
- May 2006
- Posts
- 279
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
-
09-03-2006, 08:53 AM #7Junior Guru Wannabe
- 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
-
09-03-2006, 12:17 PM #8Web Hosting Guru
- Join Date
- May 2006
- Posts
- 279
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.
-
09-03-2006, 12:54 PM #9Junior Guru Wannabe
- 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.
-
09-03-2006, 03:26 PM #10Web Hosting Guru
- Join Date
- May 2006
- Posts
- 279
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.
-
09-03-2006, 05:57 PM #11the cloud is a lie
- Join Date
- May 2004
- Location
- NYC
- Posts
- 793
Originally Posted by AvrilBoi