Multiple Level of Subcategories

[hxinen]

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

Assuming I want this to happen:

Main Category:
Level1

Level1
>Level1.1
>Level1.2
  >>Level1.2.a
  >>Level1.2.b
    >>>Level1.2.b.a
       >>Level1.2.b.a.a
    >>>Level1.2.b.b

How do I design this?
Thank you!

[hericles]

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.

[hxinen]

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`

[diafol]

https://www.daniweb.com/web-development/php/threads/476800/how-to-get-the-total-pair-of-left-and-right-value-base-on-my-database

https://www.daniweb.com/web-development/php/threads/479889/build-menu-list-from-array

Maybe something there? As an alternative to the self-referencing parent.

[scarcella]

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.