tags:

views:

47

answers:

7

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 :)

+2  A: 

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
OMG Ponies
would that find all the rows that has keyword1 OR keyword3 (I mean they do not have to have both keywords to be address, as long as they have one at least, it is fine) Does that work with this condition? and what would (t.keywords) refer to?
@user220755: Yes, as long as one of the keywords exist in your table - the record will be returned.
OMG Ponies
and what does t.keywords stand for (just so i know what to replace it with :) ) THANK YOU!
@user220755: `t.` is a table alias reference - I included it to demonstrate where the column is referenced for your situation.
OMG Ponies
One last question: is there a way to know which one did it found? For example is there a way to know that the returned information is related to keyword3 only? or keyword1 or both? ((I really appreciate the help))
@user220755: FIND_IN_SET will return the position of the first match - so you wouldn't know about multiple matches.
OMG Ponies
So, if there are 20 pieces of information that match this keyword, I will only get one? If yes, how do I get all of them back?
@user220755: The WHERE clause I posted will be satisfied - unless you're wildcarding, I don't see a situation where what you describe will happen.
OMG Ponies
No what i meant is that if there are 3 pieces of information that has the keyword (keyword3), I want to display them all to the user.
OMG Ponies
A: 

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

LIKE is too simple for this operation
OMG Ponies
+1  A: 

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.

scribu
Thank you for the suggestions, I will try my best to change the database structure soon when I can
+1  A: 

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.

Sjoerd
Thank you for the suggestions, I will try my best to change the database structure soon when I can
+2  A: 

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>
Wrikken
Thank you for the suggestions, I will try my best to change the database structure soon when I can
I got Not unique table/alias: information
Nevermind, got it :)
A: 

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.

Kai
A: 

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'

bpeterson76