Mysql Select Query help

Hello All,

i am working with a shop script that i have inherited and i want to add the ability to have products in multiple categories.

To do this i have created an extra field in the product table called cat_id_2.

Now on the list products page I have the follwing call at the moment, which pulls all of the products for the current category...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
		FROM tbl_product pd, tbl_category c
		WHERE pd.cat_id = c.cat_id  AND pd.cat_id IN $children AND pd_status=1
		ORDER BY pd_name";

Code (markup):

I tried to change this myself and added the extra paret to the WHERE clause and came up with this...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
		FROM tbl_product pd, tbl_category c
		WHERE (pd.cat_id = c.cat_id OR pd.cat_id_2 = c.cat_id )  AND pd.cat_id IN $children AND pd_status=1
		ORDER BY pd_name";

Code (markup):

But for some reason, instead of putting a product in two categories it puts the same product in the category twice, - its wierd.

Can anyone see anything wrong with what i have tried?

I hopew I have explained this ok

Thanks in advance

Mike

 

Before your change, a product can only be in one category. Category and Product has a one-to-many relationship, i.e. one category has many products and one product can only be in one category. After your change, Category and Product has a many-to-many relationship, i.e. one cateogry has many products and one products can be in many categories.
I don't know if you can solve the problem just by changing your query without changing the structure of your tables, i.e. maintaining your many-to-many relationship in a separate table with product ids and category ids.
You have changed one part of your query but pd.cat_id IN $children still only mentions one category id, not sure that will work.