Hi, I need some help with my college work, with the database I am designing.
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!
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`
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.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.