List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 14 2005 6:29pm
Subject:Re: Problem with some querys
View as plain text  
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
Thread
Problem with some querysRoberto Rodríguez Garrido14 Jul
Re: Problem with some querysPeter Brawley14 Jul