category structure of a shop

[commun1]

heya,

let's see where to start...

someone told me I should use a simple MySQL table for storing my category structure, so that it is flexible and easy to control.

my table is:

cat_id, sub_id

the sub_id always contains the cat_id of the category which is one level above the category which is refering to it...

e.g. if I have the category "Dog" with the cat_id = 1 and I have the category "Dog Food" within "Dog" with the cat_id = 2 and the sub_id = 1, then "Dog Cups" within "Dog Food" and the cat_id = 3, sub_id = 2 and so on ...

what I'm up to is how can I get a page to display all articles within "Dog" when I click on its level but display only the particular articles within "Dog Food" when I click deeper into the structure?

also how do I get all the cat_id's below "Dog" into my array? let's say there's another group "Dog Items" within "Dog" with cat_id = 4, sub_id = 1 and I'd like to have all cat_id's named above based upon the depth level im in?

Hope my try on explaining this is not too confusing...

I'm using an array which stores the cat_id's in the right order... or at least it should but however it doesn't work...

 

[scorpion4377]

Erm. That's kind of confusing. Can we see your code? Sometimes the code itself speaks better.

 

[commun1]

hmmm, okay...

my problem is that I don't yet have any code ready since I don't know how to handle it right...

let's see, I'll try again ;-)

so I have 2 MySQL-Tables for a start.

the first one with the category data:

cat_id, sub_id, category_name

TABLE "CATEGORY"
1, 0, "category 1"
2, 1, "category 2"
3, 2, "category 3"
4, 3, "category 4"
5, 1, "category 5"
6, 3, "category 6"

all these categories are within category 1.
why? because the sub_id always refers to the cat_id of the higher category or the category that comes before the one's following...

now I have a second table with articles in it:

TABLE "PRODUCTS"
id is AUTO_INCREMENT and cat_id is the same as cat_id from the CATEGORY table.

id, cat_id, product_name

1, 1, "product 1"
2, 4, "product 2"
3, 4, "product 3"
4, 2, "product 4"

now let's assume I have a html page with links in it that reflect the category structure

an example for above data would be:

>>category 1 - >>category 2 - >>category 3 - >>category 4

I should be able to click on any category link and get the results for that category retrieved from the tables PLUS the categories that hide behind the level I'm actually on, so I should get ALL FOUR products when I click ">> category 1", only "product 2","product 3","product 4" when I click "category 2" and only "product 2" and "product 3" when I click "category 4".

hope that helps...

 

[nego78]

Hi


My advice
You should rebuild your category table
Read this

http://www.sitepoint.com/article/hierarchical-data-database/

Next
Select LFT AND RGT FROM current category
Then
Select All categories within current category
And
Select * FROM PRODUCTS WHERE cat_id IN (VALUESFROMABOVEQUERY)

gry online