Please improve my fetched forumlist by MySQL

[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

[Lynne]

I'm not sure what you are doing with it, but you know it's already cached and you can see how it is used on the bottom of the index.php page. Not sure if that helps out with what you are doing....

[ThorstenA]

I have profile field values connected with forums and depending which profile field a viewer has ("I am driving porsche"), he sees the connected forum with subforums ("Porsche" "-- Porsche Discussion" "-- Porsche 911") on top of the forum list.

Instead of

General Forums
-- Discussion
Porsche
-- Porsche Discussion
-- Porsche 911 ...

He sees

Porsche
-- Porsche Discussion
-- Porsche 911
General Forums
-- Discussion ...

The script searches for lots of profile fields and depending on them puts the for the user most interesting on the top. The script also takes user behavior into consideration:

• subscribed forums
• moderated forums
• forums where you read threads
• forums where you posted

All these forums are on the top, depending whether they might be of interest for the user or not.

In Mysql I can easily query all this together and get the perfect forum order for me instead of doing all this sorting stuff in the cache forum php list which maybe is not made for things like this.

[Marco van Herwaarden]

Please be aware that this might need multiple licenses.

[ThorstenA]

Thanks for the thought. This approach presents the forums in the same order. It is developed to present the forums in a different order (which I actually do). I am unsure how this might need multiple licences, as I only use this on one domain with the database vbulletin provides me with. There is nothing changed in the database structure.

Do you see something I could do better in the sql query? Any thoughts would be great