Find Rows in Vertical Line-separated values in MySQl?

Question

Let say i have a field 'category' with the value '1|2|3'. I want to search in mysql such that it will return all rows matching my search parameter into the values of the category. for example:

$cat_id = 1;

SELECT * FROM `myTable` WHERE cat_id is equal or found in category with values '1|2|3'...

something like that..i do not know how to put it in correct sql query.

Any Ideas? thanks in advance.

Answer 1

this must be a table "category", not field.

SELECT * from mytable, cattable, where cattable.id=1 and cattable.mid=mytable.id

Answer 2

Well you could manage it using the MySQL-specific regex operator:

WHERE category RLIKE '(\\||^)'+cat_id+'(\\||$)'

(Assuming MySQL non-standard backslash escapes are enabled, which they are by default.)

However, this kind of query is not indexable and it's generally considered extremely poor schema design to fudge multiple datapoints into one column like that. The usual solution is a join table of myTable to category.