Catalog (A Product appearing in Multiple Catagories)
Moderator: General Moderators
Catalog (A Product appearing in Multiple Catagories)
I'm creating a product catalog using PHP/MySQL.
User can browse through categories and products listed under selected category. So I created two tables named Cats and Prods. Cats table contains id, name and picture for catagory and Prods table contains with product info with cat_id[/id] it belongs to. Now the problem is that some products belong belong to multiple category. As per my table design currently, there is only way to add one category for given product. I can use comma separated values to contain multiple categories for product, but I'm sure that's not a very sophisticated way to do it.
For example, when I want to remove certain category.. I'll have to search through whole (approx 1000+ products at start) database and remove entries, which is a BIG load on system.
What would be the best way to design table for such scenario??
I'm dead thinking about it and pulling my hairs on this one..... in dire need of someone to enlighten me and rescue me from my limited knowledge !!
User can browse through categories and products listed under selected category. So I created two tables named Cats and Prods. Cats table contains id, name and picture for catagory and Prods table contains with product info with cat_id[/id] it belongs to. Now the problem is that some products belong belong to multiple category. As per my table design currently, there is only way to add one category for given product. I can use comma separated values to contain multiple categories for product, but I'm sure that's not a very sophisticated way to do it.
For example, when I want to remove certain category.. I'll have to search through whole (approx 1000+ products at start) database and remove entries, which is a BIG load on system.
What would be the best way to design table for such scenario??
I'm dead thinking about it and pulling my hairs on this one..... in dire need of someone to enlighten me and rescue me from my limited knowledge !!
That does sound like good solution..... but......
1) How I can use this structure for browsing or searching data ?
2) If I have to delete certain category, how will I update both (tblCats and tblProductCats) tables ??
I'm sorry but asking such silly questions, but right at this moment my brains has stopped working absolutely... so any help is appreciated !!
maybe I need to sleep over this tonight and start thinking fresh in the morning !!
1) How I can use this structure for browsing or searching data ?
2) If I have to delete certain category, how will I update both (tblCats and tblProductCats) tables ??
I'm sorry but asking such silly questions, but right at this moment my brains has stopped working absolutely... so any help is appreciated !!
maybe I need to sleep over this tonight and start thinking fresh in the morning !!
Hi begby.... you solution works like charm so far.... but there is another problem that I'm facing.
For now, I created table and inserted data for existing products manually in database. But for new products I'm kinda lost, as how do I insert product with same ID into two tables, at the same time?
I've created a form for adding new products into database. My products table's ProdID field is has an auto_increment property. Upon submitting it enters the data into Products table and assign ID automatically. In the second query it also enters data into the mediating table for category (I'm using checkboxes for selected categories in form), but how do I retrieve the prodID of currently entered product so that the same ID is used to enter into mediating category table?
For now, I created table and inserted data for existing products manually in database. But for new products I'm kinda lost, as how do I insert product with same ID into two tables, at the same time?
I've created a form for adding new products into database. My products table's ProdID field is has an auto_increment property. Upon submitting it enters the data into Products table and assign ID automatically. In the second query it also enters data into the mediating table for category (I'm using checkboxes for selected categories in form), but how do I retrieve the prodID of currently entered product so that the same ID is used to enter into mediating category table?
You can obtain the last generated id from mysql via the LAST_INSERT_ID() function (or via php with the mysql_insert_id function).igoy wrote:Hi begby.... you solution works like charm so far.... but there is another problem that I'm facing.
For now, I created table and inserted data for existing products manually in database. But for new products I'm kinda lost, as how do I insert product with same ID into two tables, at the same time?
I've created a form for adding new products into database. My products table's ProdID field is has an auto_increment property. Upon submitting it enters the data into Products table and assign ID automatically. In the second query it also enters data into the mediating table for category (I'm using checkboxes for selected categories in form), but how do I retrieve the prodID of currently entered product so that the same ID is used to enter into mediating category table?
I'm facing another problem.
If I delete one category from database, I'm deleting recors from both the tables (tblCat and tblProductCats) using query something like this
Now products which comes under only category "25" is not accessible in browsing list, which is fine. But If want to browse only such products (which does not come under any category, like ones from deleted category "25") what should I do?
I tried using following SQL Query, but MySQL goes bonkers and return with TimeOut.
I thought that this should return products from tblProducts which are not listed in tblProductCats (since those records are deleted from tblProductCats while deleting category "25"). But it's not happening so. That means I'm doing something terribly wrong... What am I doing wrong? how can I achieve this?
If I delete one category from database, I'm deleting recors from both the tables (tblCat and tblProductCats) using query something like this
Code: Select all
DELETE * FROM tblCat WHERE catID = 25
DELETE * FROM tblProductCats WHERE catID = 25I tried using following SQL Query, but MySQL goes bonkers and return with TimeOut.
Code: Select all
SELECT * FROM tblProducts A, tblProductCats B WHERE A.prodID != B.prodIDCode: Select all
SELECT * FROM tblProducts p
WHERE NOT EXISTS(
SELECT * FROM tblProductCats c
WHERE c.prodID = p.prodID)You can also do a left outer join on productCats where catID is null.