Hi, I need some help with my college work, with the database I am designing.

Assuming I want this to happen:

Main Category:


How do I design this?
Thank you!

Recommended Answers

All 4 Replies

Create a table for the categories that holds the category name, its ID and its parent ID. Top level categories would have a parent ID of 0.
For example, your demo would look like:

ID, Name,    ParentID
1  Level1       0
2  Level1.1     1
3  Level1.2     1
4  Level1.2.a   3
5  Level1.2.b   3
6  Level1.2.b.a 5

You can now track all children of a level via the database and extend this to as many levels as you need.

Thank you, that was very helpful.

How do I inner join them?
Instead of showing the numbers under ParentID, how do I show the Name.

Thank you!

Figured it out. Thank you so much!

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4, t5.name as lev5, t6.name as lev6, t7.name as lev7
FROM `criteria` AS t1
LEFT JOIN criteria AS t2 ON t2.`parent_id` = t1.`criteria_id` 
LEFT JOIN criteria AS t3 ON t3.`parent_id` = t2.`criteria_id` 
LEFT JOIN criteria AS t4 ON t4.`parent_id` = t3.`criteria_id`
LEFT JOIN criteria AS t5 ON t5.`parent_id` = t4.`criteria_id`
LEFT JOIN criteria AS t6 ON t6.`parent_id` = t5.`criteria_id`
LEFT JOIN criteria AS t7 ON t7.`parent_id` = t6.`criteria_id`

The best solution for this, would be to use @hericles's database schema. Then to use what's called recusrion to then spit this out recursvily with nested ul's and li's. The SQL query that @hxinen posted is anything but optimzed or dynamic. Just grab a list, then loop through the records, and nest them. Then loop that nested array until array['children'] is empty, where you can spit out a closing ul. If you want code for this just ask, and i'll post a working exapmple for you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.