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 outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 7/14/2005
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
