[aqw]

Hi!

I have a **** serious problem that is driving me nuts (have been on it for the whole day without finding a good solution).

Let's say I have this category table:

Code:

cat_id parentcat_id parentcat_ids    title 
1         0         1         PHP 
12         0         12         ASP 
3         0         3         JSP 
4         0         4         XML's fall 
5         1         1,5         Books 
6         1         1,6         XML 
7         1         1,7         Ad management 
8         1         1,8         Traffic scripts 
9         7         1,7,9         Programs 
10         12         12,10         Discs 
11         12         12,11         Java 
13         7         1,7,13         Abraham's Report 
14         7         1,7,14         XXL 
15         14         1,7,14,15         Zimmick 
16         14         1,7,14,16         Hattrick 
17         14         1,7,14,17         Junkyfood 
18         9         1,7,9,18         Ziggurat

Out of those categories, I want to get an ordered list (exactly like the "Search in forums" category list at http://www.sitepoint.com/forums/search.php).

I want to acheive this with 1 query.

Here is what I tried:

[MYSQL=""]
SELECT title, @a := parentcat_ids, (select title from cats where cat_id = SUBSTRING_INDEX(@a,',',1)) base_title
FROM cats
ORDER BY base_title, parentcat_ids
[/MYSQL]

This gives:

Code:

ASP                12            ASP         
Discs             12,10         ASP         
Java             12,11         ASP         
JSP                3             JSP         
PHP                1             PHP         
Books             1,5         PHP         
XML                1,6         PHP         
Ad management     1,7         PHP         
Abraham's Report 1,7,13        PHP        
XXL                1,7,14        PHP         
Zimmick            1,7,14,15     PHP         
Hattrick         1,7,14,16     PHP         
Junkyfood         1,7,14,17     PHP         
Programs         1,7,9         PHP         
Ziggurat         1,7,9,18     PHP         
Traffic scripts    1,8         PHP         
XML's fall         4             XML's fall

This is just almost good! In fact, the sole thing that needs to be done now is to sort the category names in each category.

For example, instead of having in the result set:

Code:

Zimmick 1,7,14,15 PHP
Hattrick 1,7,14,16 PHP
Junkyfood 1,7,14,17 PHP

, I want to have:

Code:

Hattrick 1,7,14,16 PHP
Junkyfood 1,7,14,17 PHP
Zimmick 1,7,14,15 PHP

This is where the problem lies...

I've also tried using in the ORDER BY clause:
[MYSQL=""]SUBSTR(parentcat_ids, 1, LENGTH(parentcat_ids) - LOCATE(',', REVERSE(parentcat_ids)))[/MYSQL]
(For example, if parentcat_ids = '10,20,512,63,51', SUBSTR(... gives '10,20,512,63'.)

First thought that it could be of interest but in fact no...

If someone finds out how to acheive this with 1 query (eventually with some subqueries), I am willing to pay you some bucks.

Thank you, you gurus

[aqw]

What I want is: (I've delimited the rows to make them easier to see).

Code:

title                     parentcat_ids        base_title
ASP                     12                       ASP         
Discs                   12,10                   ASP         
Java                    12,11                   ASP         


JSP                     3                         JSP         
 

PHP                      1                         PHP            
Ad management     1,7                        PHP         
Abraham's Report   1,7,13                    PHP       
Programs              1,7,9                      PHP         
Ziggurat               1,7,9,18                  PHP     
XXL                     1,7,14                    PHP            
Hattrick                1,7,14,16                PHP         
Junkyfood             1,7,14,17                PHP            
Zimmick                1,7,14,15                PHP      

Books                   1,5                        PHP        
Traffic scripts        1,8                        PHP        
XML                     1,6                        PHP          


XML's fall         4             XML's fall

Hope this will make it all a little clearer...