[ThorstenA]

This is how I fetch the ordered forumlist with MySQL. Do you have any suggestion to make it easiser? This code is part of another script, as the results are from each user the same, it will be cached and is therefore very fast.

SELECT l1do.displayorder AS l1do, l2do.displayorder AS l2do, l3do.displayorder AS l3do,  forum.forumid, l1do.forumid AS forumidl1, l2do.forumid AS forumidl2, l3do.forumid AS forumidl3, forum.title,

(length(parentlist) - length(REPLACE(parentlist, ",", ""))) AS levels 

FROM forum 

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l3do 
ON l3do.forumid = IF((length(parentlist) - length(REPLACE(parentlist, ",", "")))>2, 
REVERSE(mid(REVERSE(parentlist),    
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1, 
IF(LOCATE(",",REVERSE(parentlist),  LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1)<1,LENGTH(parentlist)+1,LOCATE(",",REVERSE(parentlist),  LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1)) 
- LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)-1 
)),0) 

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l2do 
ON l2do.forumid = IF((length(parentlist) - length(REPLACE(parentlist, ",", "")))>1,  
REVERSE(mid(REVERSE(parentlist),  
LOCATE(",",REVERSE(parentlist),  
LOCATE(",",REVERSE(parentlist))+1)  +1,  
IF( LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)<1,LENGTH(parentlist)+1, LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist),  
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)) -  
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1)-1  
)),0)

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l1do 
ON l1do.forumid = REVERSE(mid(REVERSE(parentlist), 
LOCATE(",",REVERSE(parentlist))+1, 
IF(LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1)<1,LENGTH(parentlist)+1,LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1)) 
- LOCATE(",",REVERSE(parentlist))-1 
)) 

GROUP BY forum.forumid 
HAVING levels <4 
ORDER BY l1do,l2do,l3do