mysql_query help

I have products database like:

id: 5
catid: 2,4,26,33,46
name: product one
etc...

For each store product catid stores multiple categories id separated with commas as one product might appear in several categories. How can I view the products from for example category with id 26?

My firs开发者_C百科t idea was:

mysql_query("SELECT * FROM products WHERE catid LIKE '%26%'");

but its not working well as it also shows products from 2 and 46 category.

Use the FIND_IN_SET() function: SELECT * FROM products where FIND_IN_SET(26, catid) > 0

The mysql find_in_set() function will do this.

However, it will never be fast. Mysql indices are not useful for finding a match in the middle of a string. They only work if you're matching against the whole string, or a substring starting from the beginning. A full scan of the data will be required for every query.

If you're not too far along in development, and can change the data model to use a mapping table that looks something like:

create table product_cat (
  rel_id primary key,
  product_id int,
  cat_id int,
  unique (product) product_id,
  index (cat_product) cat_id, product_id
)

Then, put a row in the table for each category/product relation, and join against that table. It's possible to make such queries fast.

This will do it.

SELECT * FROM products WHERE catid IN (26);

This should accomplish what you are looking for.

This can also be used to expand your selection.

SELECT * FROM products WHERE catid IN (26,27);