Category and sub-category logic

[Ryan A]

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)

then if you click on "cars" you would either see a sub category or all the
listings there

I have never done a project like this so am a bit confused but I am pretty
sure quite a few of you must have done this because its a bit common on the
net and while I am kind of new to php, most of you guys are fossils :-D

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?

eg:

Auto
--Cars
--Bikes

Banking
--Finances
--Loans

Women
--Boring but REALLY good looking
--Great fun but not-so-good-looking


Any ideas? I searched hotscripts and google but found crappy programs like
phpyellow and zclassifieds which are really no good to me. If you know of
something that already does the above kindly share it with me.

Thanks,
-Ryan

-------------------
-----------------
The government announced today that it is changing it's emblem to a condom
because it more clearly reflects the government's political stance. A condom
stands up to inflation, halts production, destroys the next generation,
protects a bunch of pricks, and gives you a sense of security while you're
actually getting screwed.
-------------------
-----------------

[David Otton]

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.