1

I'm using pure php and not any CMS such as Wordpress.

I have a product table where there is a cat field for Categories of the product.

I have a page view that is based on a category. So, for instance I want to see all mobile phone with brand of Nokia. It means 

SELECT * FROM product_table WHERE cat = nokia.

This works just in cases where the product is not multi-category. If the following product is categorized like this: Nokia, black, new, expensive, top-rated; then I need to use regexp in my SQL statement. For instance something like this:

SELECT * FROM product WHERE cat *= Nokia.

My cat field of product table is a string with each cat separated by comma.

Is it correct? What should I do?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • There are [lots of questions related to this](http://stackoverflow.com/search?q=%5Bmysql%5D+match+value+in+comma+separated). `FIND_IN_SET()` is offered by MySQL, but [the real answer is _not_ to store your data this way and instead properly normalize the table](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). – Michael Berkowski Aug 31 '13 at 12:25
  • http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like – Madara's Ghost Aug 31 '13 at 12:25
  • The [MySQL docs on `FIND_IN_SET()`](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) – Michael Berkowski Aug 31 '13 at 12:30

1 Answers1

1

You can you plain like clause

SELECT * FROM product_table WHERE cat like '%Nokia%'

Or use mysql regex feature, if you like

SELECT * FROM product_table WHERE cat REGEXP '.*Nokia.*'

And, yes, your data model is ill - you should use 1 or 2 more tables instead of comma separated strings in cat field.

ADDITION:

SELECT * FROM product_table WHERE (cat like '%Nokia,%' or cat like '%,Nokia%')

in ANSI SQL, or

SELECT * FROM product_table WHERE FIND_IN_SET('Nokia',cat)

with MySQL-specific feature - all 4 queryies given above are ineffective like hell and can not be optimized without changing data model.

Assuming you have two tables products(product_id,product_name,...) and categories(cat_id,cat_word,...) you can simple relate them throug third associative table product_in_categories(product_id,cat_id)

And an example of effective category-selection query with new data model:

SELECT * FROM products p
WHERE exists(select 1 from categories c, product_in_categories pc 
  where cat_word='Nokia' and c.cat_id=pc.cat_id and pc.product_id=p.product_id)
mas.morozov
  • 2,666
  • 1
  • 22
  • 22
  • Sorry what is your suggestion? I have a table called category but I do not know how to relate them. –  Aug 31 '13 at 12:31