RE: Category System schema

2003-07-29 Thread Peter Lovatt
you need at least 3 fields

CategoryID
ParentID
Name

CategoryID is the key, and identifies the category
ParentID is the CategoryID of the parent category
Name is the category name

When you add a sub category you set its ParentID to the CategoryID 

To show all sub categories of CategoryID 99

SELECT * FROM table WHERE ParentID = 99

HTH

Peter




-Original Message-
From: Alex Pilson [mailto:[EMAIL PROTECTED]
Sent: 29 July 2003 06:06
To: [EMAIL PROTECTED]
Subject: Category System schema


Does anyone know the best schema to achieve a multi-level category system?

I need a person to be able to add categories on the fly, but then 
also specify sub categories and even possibly sub-categories of 
sub-cats. I have found two possible ways inside one table with using 
parentID, groupID, etc. But the issue I am now running into is that I 
have to sort on groupID to get the items to group correctly when 
showing in a HTML drop down list. Which means I can't sort 
alphabetically. If I do the order is not right...using Lasso 6 with 
some looping code to indent the subs in the list. On top of that I 
would also like the ability to assign a priority field for listing in 
that order as well. There has to be some kind of solution to this 
that I don't see.

Any ideas? Thanks!
-- 
---
 Alex Pilson
 FlagShip Interactive, Inc.
 [EMAIL PROTECTED]
---

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



Category System schema

2003-07-28 Thread Alex Pilson
Does anyone know the best schema to achieve a multi-level category system?

I need a person to be able to add categories on the fly, but then 
also specify sub categories and even possibly sub-categories of 
sub-cats. I have found two possible ways inside one table with using 
parentID, groupID, etc. But the issue I am now running into is that I 
have to sort on groupID to get the items to group correctly when 
showing in a HTML drop down list. Which means I can't sort 
alphabetically. If I do the order is not right...using Lasso 6 with 
some looping code to indent the subs in the list. On top of that I 
would also like the ability to assign a priority field for listing in 
that order as well. There has to be some kind of solution to this 
that I don't see.

Any ideas? Thanks!
--
---
Alex Pilson
FlagShip Interactive, Inc.
[EMAIL PROTECTED]
---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]