Category / Sub Category Database Logic *Great minds needed*

[TomLasky]

Hi everyone,

I'm creating a very powerful web store script in ASP. One of the things I am adding is to allow the user to create their own categories and sub categories for items they wish to sell.

This isn't a new concept or anything but what I'm doing is allowing them to create "Main or Master Categories". An example of a main category would be "Electronic s".

They can now create sub categories under "Electronic s".

If for example they created a sub category under Electronics, labeled "Television s" it would look like this:

Electronics
Televisions

I am also allowing them to now add as many sub categories as they'd like to make it very specific to what they want to display.

They could have Electronics > Televisions > LCD > 32"+ > Vizio

There would be no limit to the amount of sub categories to add.

One problem I am having with the logic is building a hierarchy view in their control panel. I am coding it like this:

In my MySQL database, I have a table named shop_categories

Columns are:

cid
name - name of the categories
level - 0 = master category, 1 - however many are sub categories
parent - the category 1 up from the current
maincat - main category listed under

In this way I am able to see where everything is and which category is linked in a "category relationship".

Creating and saving is always the easy part I know... Grabbing the data and building it back is another problem all together.

Things I want to do:

#1 Display the categories in a tree view style, alphabetically.

#2 Display the categories in an HTML SELECT BOX alphabetically

So for example, #1, If there were main categories Electronics, Automotive, Garden... all with sub categories, it would display:

Automotive
Tires
BF Goodrich
All Season
Firestone

Electronics
Televisions

Garden
Pottery
Clay
Plastic


And for example #2, when I do a For Next loop through the database, the SELECT BOX will be sorted, like in Visual Basic Listbox, you can add items in any order and they sort automatically.


For #1, I was going to do something like:

Query the database and get a record count for how many master categories I have

For x = 0 to RecordCount - 1

Then do another for next loop inside this loop that is looping through the main categories. This secondary loop would get all subcategories for that main category.

For i = 1 to 100

Select name from shop_categories where maincat = recordarray(4,x )

Exit For if no records are returned

----------------------

It would work fine and dandy, however, doing a loop inside of a loop is only capable of getting 1 sub category at a time, since I don't know how many sub categories there will be it can vary from user to user and main category to main category, how can I build this dynamically?

Any advice on the logic to use would be greatly appreciated. I've been going for too many hours straight and I am out of answers to my own brain.

Many Thanks

[CroCrew]

Hello TomLasky,

Just to make sure, you want this done in “Classic ASP” development right?

Thanks,
CroCrew~