I need to write a php code to query a MySQL database and build a form. I have been able to write the code but I'm looking for a more elegant solution. This database is for publications and one table holds all of the different topics the publications could fall under. The topics have an heirachical relationship - there are main topics and sub-topics. Some main topics have sub-topics and some sub-topics have there own sub-topics. Right now there are only three levels but there could be a fourth in the future. Here is a diagram to make it more clear...
Main Topic
Here is the table structure...
Topics
topicid | topic | parentid
The parentid is used to show the relationship between main topics and sub-topics. For all main topics the parentid = 0 and the parentid of their sub-topics is the topicid.
1 | maintopic1 | 0
2 | sub-topic1 | 1
3 | sub-topic2 | 1
4 | sub-topic3 | 3
Currently, the program uses nested queries to print the list of topics. First, all maintopics are selected then while looping through them a check is done for any sub-topics it might have. If any are found it prints them and then loops through checking for any third level sub-topics it might have. This works fine but if a fourth level were added in the future another nested query would have to be hard coded. How can I structure the program to handle future modifications to the DB? Is there a more elegant way of writing/structuring this program? Any thoughts would be appreciated. Thank you.
