Page 1 of 1

Catalog (A Product appearing in Multiple Catagories)

Posted: Thu Apr 05, 2007 3:10 pm
by igoy
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 !!

Posted: Thu Apr 05, 2007 3:31 pm
by Begby
tblProducts
prodID
prodName
prodPrice
etc.

tblCats
catID
catName

tblProductCats
catID
prodID

Example. Say you have a product with prodID 25 and you want it to be in categories 23 and 73 then you will two records in tblProductCats like

prodID catID
25 23
25 73

Posted: Thu Apr 05, 2007 3:36 pm
by igoy
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 !!

Posted: Thu Apr 05, 2007 3:40 pm
by Begby
Get all products in category 25

SELECT * FROM tblProducts p LEFT JOIN tblProductCats c ON c.prodID = p.prodID WHERE c.catID = 25


Delete category 25

DELETE * FROM tblCat WHERE catID = 25
DELETE * FROM tblProductCats WHERE catID = 25

- I think you need some sleep if you didn't figure out #2

Posted: Fri Apr 06, 2007 12:35 am
by igoy
Amazing how simple things can be..... I kinda thought about #2 on my own when I woke up this morning... but I never gave JOIN a thought. Thanks a lot for your help, I believe this should work just fine. :D

Posted: Sun Apr 08, 2007 5:01 pm
by igoy
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?

Posted: Sun Apr 08, 2007 5:13 pm
by timvw
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?
You can obtain the last generated id from mysql via the LAST_INSERT_ID() function (or via php with the mysql_insert_id function).

Posted: Sun Apr 08, 2007 5:38 pm
by igoy
Damn.... I feel so stupid for not going through PHP manual !!! :(
Thanks a lot mate !!!

Posted: Mon May 14, 2007 3:51 am
by igoy
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

Code: Select all

DELETE * FROM tblCat WHERE catID = 25
DELETE * FROM tblProductCats WHERE catID = 25
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.

Code: Select all

SELECT * FROM tblProducts A, tblProductCats B WHERE A.prodID != B.prodID
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?

Posted: Mon May 14, 2007 9:25 am
by Begby

Code: 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.

Posted: Mon May 14, 2007 12:59 pm
by igoy
Thanks a lot again for solution man. Sorry if I'm asking too much, but could you please explain me a bit about your Query and what I was doing wrong with my query ??