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
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005
