PHP SQL - Tree Menu

Author

Tree Menu - MySQL

Gosiek

2005-09-01, 3:56 am

Hello everyone,

My idea is to creat tree menu (see http://www.allegro.pl/category_map.php),
for example:

Computer
PC
Intel
AMD
Laptop
Intel
AMD
Books
Albums
Fantasy
Computer Science
Programming
Networks
Comiks
...

and so on.

And I'm not sure how to creat the tables. I had two ideas:
1. Creat first table with main category: id_main_category,name; Second
table with under category: id_under_category, id_main_category, name;
Third table with next under category: id_under_category2,
id_under_category, name and so on.
2. Creat one table with:
Id, name, parent_id:
1 Computer 0
2 Laptop 1
3 AMD 2
4 INtel 2
5 PC 1
6 INTEL 2
7 AMD 2
8 Books 0
9 Computer Sciense 1
10 Programing 2
11 Networks 2

But this two ways are not very good. In first one I will have to write
long query with many select in select if I would like to select every
under category for Computers. I second one I don't have any relationship
and I don't know how could I select some data.

Can you give me any advice how to resolve this problem?

Best rgds,
Gosiek

Ps. As always sorry for my english.

ZeldorBlat

2005-09-01, 6:57 pm

I usually do it the second way. Your top level should have a null
parent_id. Then, to get the top level, do something like:

select m.id, m.name, coalesce(c.children, 0)
from menu m
left outer join (select parent_id, count(*) children
from menu
group by parent_id) c on m.id =
c.parent_id
where m.parent_id is null

So, this gets you a result with the top level menus, their id, their
name, and the number of children that have (if any). That way, you
know whether or not you need to check the next level.

So walk through this first result, displaying the menu. If at anytime
you find that children > 0, run the same query above but substitute
"where m.parent_id is null" with "m.parent_id = [whatever the current
menu id is]" to get all the children.

Might be helpful to write a recursive function to do this for you.

Sponsored Links