>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
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.
Roberto Rodríguez Garrido wrote:
I’m programming an online shop, but I have a big problem with categories, I
detail the database structure:
CAT_ID | CAT_NAME | RELATION
1 | Computers | 0
2 | Notebooks | 1
3 | Tablets | 1
4 | Notebooks IBM | 2
5 | Notebooks HP | 2
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
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