SQL Select columns with comparison

Question

I have a SQL table which have a column named keywords.

Table:

| id | keywords |

| 0 | music programming guitar |

Now i have an array like $array = array("boat", "water", "music");.

My target is to select all columns which have at least one of the things from the array in the keywords.

Sql-Query:

"SELECT * FROM table WHERE keywords IN ('".$array."')";

It returns nothing.

You should definitely normalize your database first, get rid of the whitespace separated values in your column keywords. Once that's done, your query will also work. — TimoStaudinger, May 17 '15 at 19:52
No. Have a look at this: http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization Basically, you don't store multiple distinct values like different keywords in one column at all, you use multiple tables. — TimoStaudinger, May 17 '15 at 19:58

score 1 · Accepted Answer · answered May 17 '15 at 20:52

The approach I am going to describe is highly not recommended. The correct approach is to have a separate table with one row per id and keyword. This is a junction table, and it would have rows like:

id       keyword
1        music
1        programming
1        guitar

This is the right way to store lists in a relational database. Databases have this great data structure for lists. It is called a table, not a string.

But, if you are really stuck with this situation, then you can hack your way to a solution. Presumably, performance is not an issue.

The hack is to format your list as a regular expression, and create the where clause as:

where keywords regexp 'boat|water|music'

SQL Select columns with comparison

1 Answers1