Re: Category and sub-category logic

On Fri, 15 Aug 2003 00:20:58 +0200, you wrote: >Hi, >I am thinking of making a program for personal use which does a very simple >thing, just displays listings under categories, >eg: >under main category "Auto" there would be "cars","bikes" etc >under "banking" there would be "financing","loans" etc (I as admin >create >master and sub-categories) >So far the logic I have worked out is: >Create a "master_category" table for the main categories, a "child_table" >for the subs, a "the_listings" table for the details which will have a >reference (number or word) field which will be to keep a reference as to >which category/sub-category it belongs to.. > >Tell me if my logic is wrong or I missed anything > >The part where I am confused is, on the front page (say index.php) how do I >display all the categories and sub categories in the correct order? You need a pig's ear on the database table. It's really simple once you've seen it done. Basically, each row in the database contains a reference to its parent's primary key. Call our table "category". categoryid parentid title 1 0 Auto 2 1 Cars 3 1 Bikes 4 0 Banking 5 4 Finances 6 4 Loans SELECT title FROM category WHERE parentid = 0 gives us the root elements in the tree (Auto, Banking) while SELECT title FROM category WHERE parentid = 4 gives us the children of Banking (Finances, Loans). The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). The fun thing about this structure is writing the delete function.