Roberto,

>The problem came when I want to show all computers and I g oto 
>the id 1 for show all and any product have stored this CAT_ID, 
>because all have the last “subcategory” id.

You've coded tree-like relationships between 'cat_id' and 'relation' in your categories table, but raw SQL doesn't do recursion--outside stored routines, it doesn't have a construct for a loop which stops after a data-determined number of iterations.

It seems to me you have two solutions.

One, break out your recursive cat_id-relation relationship into multiple lookup tables, eg computertypes(1=notebooks,2=tablets,&c), manufacturers(1=IBM,2=HP,&c). This will simplify your queries enormously.

Two, somewhat harder, possible only in 5.0.4 and later, but made more difficult by bugs remaining in 5.0.7, treat your products table as a nodes table, and your categories table as an edges table, and write stored procedures to traverse your relationship tree. If you feel you must go for this option, I can send you a copy of a recent chapter of ours on doing this in MySQL.

PB

-----

Roberto Rodríguez Garrido wrote:

Hi,

 

I’m programming an online shop, but I have a big problem with categories, I
detail the database structure:

 

Categories:

 

CAT_ID | CAT_NAME | RELATION

 

1                    |  Computers  | 0

2                    | Notebooks   | 1

3                    | Tablets        | 1

4                    | Notebooks IBM | 2

5                    | Notebooks HP  | 2

 

 

PRODUCTS:

 

PROD_ID | PROD_NAME | CAT_ID

 

1                          | Notebook HP 102 | 5

2                          | Notebook HP 103 | 5

 

 

Like you can see in the products table I store the ID of a category that
have some relations in its table:

 

Id 5 - Notebooks HP -> Id 2 – Notebooks -> Id 1 – Computers

 

The problem came when I want to show all computers and I g oto the id 1 for
show all and any product have stored this CAT_ID, because all have the last
“subcategory” id.

 

Can you help me to know how to do that?

 

Thanks for all.


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005