hierarchal display of items

[A_Smith1982]

Hello,

Getting problem in showing 2000 aprox. items in hierarchy lying in a mysql table. Please tell me any other way to show items in hierarchy.

Please check out, i have seen this page showing items exactly like i want. What will be the PHP coding for it or there will be some procedure in mysql?

http://citiex.com/category/computerandelectronics/

Thanks.

[matto]

good morning,

you need 2 tables: one for the items that are in your categories, and one for the categories:

table "category":
id integer primary key, not null
id_parent integer, null
title varchar(255)

table "item":
id integer primary key, not null
id_category integer, not null
name varchar(255)
[etc]

now you store:
a main category like this:
main categories have no parent, so the id_parent stays empty (NULL)
a subcategory like this:
as its parent it has defined a main category (its id is in the id_parent field)

how to query the hierarchical list:
(1) query all main categories:
select id,title from category where id_parent is NULL order by title; output main categories
(2) iterate through these. for each main category do:
select id,title from category where id_parent=[CURRENT MAIN CATEGORY ID] order by title; output subcategories
(3) now you could query the "items" belonging to the subcategory: for each subcategory: select id,name from item where id_category=[CURRENT SUBCATEGORY ID] order by name

hope this hints will help you along.