How to search for a specific word in a row in MYSQL?

Question

I have a row that has keywords in this way (keyword1, keyword2, keyword3) separated by commas as shown. When a user signs in, you know that he wants information about (keyword1, keyword3). Now, I have another table that has bunch of information related to different keywords, this table has a row called (keywords) which indicates if this information is suitable for which keyword. How do I render for the user the information he needs depending on the keywords.

In other words, if the user wants information about (keyword3, keyword1) how do I go to the (information) table and find all the information that has the word (keyword1) or the word (keyword3) in the row (keyword)?

Sorry if this is complicated (my explanation) but I tried my best to explain it.

Thank you in advance :)

Answer 1

Use the MySQL FIND_IN_SET function in your SQL query, like this:

 FROM TABLE t
WHERE FIND_IN_SET(t.keywords, 'keyword1, keyword3') > 0

Answer 2

Did you mean LIKE query ?? Check Out http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html

Answer 3

First rule of database design: store a single value per row.

In other words, you should modify your database to store each keyword in a separate row.

Answer 4

Have you considered changing the data model?

Currently you have a mini-table inside one column. That is, the keyword column contains multiple values, separated by comma's. You are going to have a hard time matching those keywords.

A better solution would be to make a table UserKeywords:

User    Keyword
Paul    snacks
Paul    food
Paul    beer
Kelly   snacks
Kelly   wine

This way, you can join this table against the information and user tables.

Answer 5

Please normalize your table, and be very suspicious about comma separated values in a single field. The more flexible setup would be:

USERS
id
name

KEYWORDS
id (or just use name as primary key)
name
...

USER_KEYWORDS
user_id
keyword_id

INFORMATION
id
data

INFORMATION_KEYWORDS
information_id
keyword_id

Your resulting query would be something like:

SELECT information.data
FROM users
JOIN user_keywords ON user_keywords.user_id = users.id
JOIN information_keywords ON information_keywords.keyword_id = user_keywords.keyword_id
JOIN information ON information_keywords.information_id = information.id
WHERE users.id = <id>

Answer 6

Sjoerd is right. A relational database setup is definitely the best solution. I'm not sure about MySQL FIND_IN_SET() but you can parse out your user keywords and compose a string so your search query would end up as something like this:

SELECT * FROM `information` WHERE MATCH(`keyword`) AGAINST('keyword1 keyword3' IN BOOLEAN MODE)

In this case your match against should return if it matches either one.

You should keep in mind that the column "keyword" has to be full text indexed for match against to work AND that match against only works for the minimum number of characters defined in the php.ini config on your server. In most cases the default is 4 characters. In other words, your keywords have to be 4 characters or more to use match against. That being said, you can set up your query string to do something like this:

$user_keywords = array('keyword1', 'keyword3');

if(count($user_keywords) == 1)
{
    $word = $user_keywords[0];

    if(strlen($word) >= 4)
    {
        $query_str = "MATCH(`keyword`) AGAINST ('".$word."' IN BOOLEAN MODE)";
    }
    else
    {
        $query_str = "`keyword` LIKE '%".$word."%'";
    }

}
else
{
    $query_str = "";
    foreach($user_keywords AS $key)
    {
        $query_str .= "`keyword` = '".$key."' OR ";
    }
    $query_str = substr($query_str, 0, -3);
}

$sql = "SELECT * FROM `information` WHERE ".$query_str;

Either way you do it, you really should begin with converting your database to a relational setup unless you have no choice - like you're using third party packages that you did not code and it would take forever to convert.

Also, the % are in there as an example of partial match. If you need more explanation, let us know.

Answer 7

When doing keyword type queries (hopefully doing the one-per-line method explained by sjoerd, I'm a big fan of the infrequently used REGEXP feature of MySql. Similar to like, it can do partial searches of all characters in a cell for similar data without all the special characters required to search various parts of the query. Think of it as Google for MySql

an example:

SELECT * from TABLE WHERE value REGEXP 'foo'