mySQL search

[npsari]

Hello. I have a table which is called [info], I store my members information there. Inside this table, there is a row called [interests] which contains all the members’ interests.

 

I am creating a search tool & I wish MySQL to match as many keywords as possible.

 

So if the user searches for: LOVE TO GO OUT AND HAVE COFFEE mySQL will display the member who has as many keywords of this search term as possible

 

These quires search for exact terms only…

 

$q = "SELECT * FROM info WHERE  interests LIKE '%{$search_term}%' ORDER BY RAND() ";

$q = "SELECT * FROM info WHERE  interests  =  ‘$search_term' ORDER BY RAND() ";

 

 

I need a MySQL query which will match as many keywords as possible and order the results according to most matching. Is this possible? Or is it a complicated story? Help please.

 

[QuickOldCar]

That could get pretty complicated.

 

I suppose if you exploded the search terms into each keyword, and also query each user, then have them all in an array and display the highest number of results first......it's possible.

But that would be pretty intensive usage to do that for all your users.

 

I would suggest looking into mysql fulltext search in boolean mode, and read about the minimum word length, creating indexes

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

 

The following query will display a result if it contains at least one or more of the keywords in the search term.

 

$q = "SELECT * FROM info WHERE MATCH (interests) AGAINST ('$search_term' IN BOOLEAN MODE) ORDER BY RAND() ";

[The Little Guy]

Full text has stop words (words that are ignored in a fulltext search), and if you are using shared hosting, you probably cannot change them.

 

in your example the only words that would have been matched are "Love" and "Coffee"

 

http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

[npsari]

Thank you so much.

Yesssss, this is so simple, I knew it exists somewhere

 

$q = "SELECT * FROM info WHERE
MATCH ( interests ) AGAINST ('$search_term')";
$res = @mysql_query($q);
while($r = @mysql_fetch_array($res)) {
}