PHP Store - Products in multiple categories?

[tonymcquaid]

Hey everyone,

 

I've recently put together a quick store script for a friend using the tutorial over at http://www.phpwebcommerce.com/

 

My question:

Does anyone know how I would make it so each product could be added to multiple categories (example: place the same TV inside "Electronics" and "Televisions" without having to add a second instance of the item?

 

I'm stumped, as I've never actually run into code/scripting that does this. Any help would be amazing.

 

Thanks,

[Psycho]

According to that tutorial you have a category table (to describe the categories) and a product table which includes a field to specify the category id. SO, you are left with a 1-to-1 relationship where a product can be assigned to one, and only one, category. But, you want a one to many relationship.

 

The solution is to create an intermediary table (e.g. prod_cat) and remove the category id field from the product table. The new table would only need two fields: the category id and the product id. You can then associate a product to as many categories as you wish. Howevever, you will have to review all your existing queries that pull data from the product table and modify them as needed to JOIN the new table.

[Psycho]

Here's an example of what an existing query might look like and how it would be modified:

 

Example existing query to get details for a single product (including the category)

SELECT * FROM products
JOIN category on products.cat_id = categories.cat_id
WHERE prod_id = '$id'

 

New query where categories are in an associative table

SELECT * FROM products
JOIN prod_cat ON products.prod_id = prod_cat.prod_id
JOIN categories ON prod_cat.cat_id = categories.cat_id
WHERE prod_id = '$id'

Note this will return multiple records based upon how many categories the product is associated with. Use the first record to get the product details and all the records to get all the category names

[ajbest]

This is an amazingly concise response. Exactly what I needed for my web based point of sale / e-commerce site. Thank You mjdamato!