i am adding products. in that i have multiple select categorys option is there. i am selecting multiple categories and imploading that and storing categorys in database product table using categorys ids.

now i am fetching with single catagory id. how to call (in_array) for that query?

"select * from products where cat_id=1"

above is my query....in database cat_id is array value. now how can i retrive products where cat_id=1..

Edited by muralikalpana: n/a

8 Years
Discussion Span
Last Post by diafol

i dont understand, you store category id's as array?? how? more explain please

i was added categorys in database.
after when i am adding products under multiple categorys(multiple select dropdown) . now i am inserting products in database. in product table i am storing category with categoryid's(which is primary in category table) like(1,3,4).

now i am fetching category. when i am fetching category products also display which products is under that category. how to do?


I think your db structure is wrong.
You have a products table and a category table. You have a categories field in the products table which is a comma separated list of all the categories pertaining to a product. Right so far? If so - this is difficult to maintain.

I would sugegst having a link table "product_categories":

id (int[?];PK;unsigned;autoincrement) where ? = the size of the int field
product_id (int[?]) - Foreign Key on product_id in your products table.
category_id (int[?]) - Foreign Key on category_id in your categories table.

Although this looks more involved - it is far easier to maintain. You can then use soemthing like the following:

$r = mysql_query("SELECT * FROM products AS p INNER JOIN products_categories AS pc ON p.product_id = pc.product_id WHERE pc.category_id=1");

This is off the top of my head, so it hasn't been tested. If you're not familiar with 'AS' it pretty much means 'alias'. So you can refer to a table by an abbreviation. I would also advise against the '*' in the mysql query - instead use a list of the fields that you require - you'll need to proceed the fieldnames with 'p.' as they belong to the products table.

Edited by diafol: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.