1

I have a value in my database with comma separated data eg.

11,223,343,123

I want to get the data, if it match a certain number (in this example it's number 223).

WHERE wp_postmeta.meta_value IN
('223', '223,%', '%,223,%', '%,223')

I thought I could use wildcard for it, but with no luck. Any ideas of how to do this? Maybe it's better to do this using PHP?

Fischermaen
  • 12,238
  • 2
  • 39
  • 56
Hakan
  • 3,835
  • 14
  • 45
  • 66
  • Don't use comma separated columns in a database: **It's that bad:** http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad – ypercubeᵀᴹ Nov 27 '11 at 21:19
  • If you really can't avoid it, use **`FIND_IN_SET()`** function: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set – ypercubeᵀᴹ Nov 27 '11 at 21:21
  • I am using the value to exlude sertain "Post ID's". So for the pages I want tp exclude I just type in the ID's of these pages. Would it be so much better to have a new row for each ID I want to exclude? (I am new to MySQL) – Hakan Nov 27 '11 at 21:30
  • Read the answer by Bill Karwin on that link. – ypercubeᵀᴹ Nov 27 '11 at 21:32

2 Answers2

2

Storing stuff in a comma separated list usually is a bad idea, but if you must, use the FIND_IN_SET(str,strlist) function.

WHERE FIND_IN_SET('223',wp_postmeta.meta_value)

If you can change your database and normalise it, you would get faster results. Create an extra table that links meta_values to your primary_id in your table.

Konerak
  • 39,272
  • 12
  • 98
  • 118
  • You might want to add that you need to check if the value is > 0. i.e. `WHERE FIND_IN_SET('223',wp_postmeta.meta_value) > 0` – omarello Nov 27 '11 at 21:25
  • @omarello: no, MySQL does this for you. `WHERE 0` is false and doesn't return results, `WHERE 1` (or higher than 1) is true and will return rows. `WHERE NULL` is `NULL` and so doesn't return rows. The query works as stated. – Konerak Nov 27 '11 at 21:27
1

The wp_post_meta table is designed to hold loads of values, and for that simple reason (and because of database normalization, you should not never comma seperated lists as values in databases.

If you absolutely must use it this way, there are some mySQL functions, one being FIND_IN_SET.

Jan Dragsbaek
  • 8,078
  • 2
  • 26
  • 46