Catalog (A Product appearing in Multiple Catagories)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Catalog (A Product appearing in Multiple Catagories)

Post 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 !!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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 !!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post by igoy »

Damn.... I feel so stupid for not going through PHP manual !!! :(
Thanks a lot mate !!!
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Post 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 ??
Post Reply