category structure of a shop?

[foy]

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

How can I achieve that I'm able to click on any category link and get the results for that category retrieved from the tables PLUS the subcategories 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"?

thanks in advance

[ironik]

The method of categorisation your using is called the 'adjacent' method. To get the results you are looking for you are going to have to do multiple database queries to find out which categories are parented to others (say if you've got 4 nested levels, then you have to do 4 seperate queries to get the right category structure.

Do a search on the net for 'modified pre-order tree traversal' (there's a great article over at sitepoint.com).

The basic idea is that you still keep your parent->child adjacent structure, but you add 2 columns with a left and right value and populate it so it corresponds with it's position in the category tree. That way you can find information about the heirarchy of the tree with a single query, and use the information you get to form another single query to find products.

It'll fit perfectly into your current structure, but you're going to have to build something to manage it.

[jd01]

Hi foy,

Here's another idea the general principle you will use is:

1. Connect to the database and select only sub_id from the category table, then store the results in a variable(s).

2. Store and pass these variable(s) with the selection (click) of a category.

3. Reference the passed variable within the selection string to display only matching results (or results that are => or < or whatever you choose).
EG $query = "SELECT * FROM products WHERE cat_id='".$your_variable."'";

Hope this helps.

Justin

Added: If you think this might work for you, let me know and I'll expand on it. I think this an easier version of what is describe above, because you already have defined corresponding variable within the two tables.