Search for a string using MySQL and PHP

Question

I have a table say 3 fields in MySQL. I need to search for a string using PHP My code is like

<?PHP

    //SET THE SEARCH TERM
    $term = "Search Term";

    //QUERY THE TITLE AND CONTENT COLUMN OF THE PAGES TABLE RANK IT BY THE SCORE
    $sql = "SELECT *, MATCH(title, content) AGAINST('". $term ."') as score FROM pages WHERE MATCH (title, content) AGAINST('". $term ."') ORDER BY score DESC";
    $query = mysql_query($sql);

    //BUILD A LIST OF THE RESULTS
    while($result = mysql_fetch_assoc($query)) {
        echo("{$result['title']} - {$result['score']}");
    }

?>

Here it searched for the single word which is exactly in the database. I need to search for multiple words..How can I change the above code. Can someone suggest some idea.

Put a space between the words or look into boolean search.

Treffynnon
– Treffynnon

2011-03-30 12:50:45 +00:00
Commented Mar 30, 2011 at 12:50 — Treffynnon
– Treffynnon, Commented Mar 30, 2011 at 12:50

frail · Accepted Answer · 2011-03-30 12:51:12Z

1

MATCH (title,content) AGAINST ('Search Term' IN BOOLEAN MODE)

would search for one of the words

MATCH (title,content) AGAINST ('+Search +Term' IN BOOLEAN MODE)

would search both words

MATCH (title,content) AGAINST ('-Search +Term' IN BOOLEAN MODE)

would search Term without Search

just split the words and build it with using + - | , whatever needed.

check documentation on : http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

answered Mar 30, 2011 at 12:51

frail

4,1282 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

symcbean · Accepted Answer · 2011-03-30 12:55:44Z

You could just add the boolean mode switch to the match - but this does not sort the results by relevance.

You could try something like.....

$words=explode(' ', $term);
$score='CASE ';
$filter=array();
foreach ($words as $try_word) {
   $score.="WHEN MATCH(title, content) AGAINST('$try_word') THEN 1 ";
   $filter[]="MATCH(title, content) AGAINST('$try_word')";
}
$score.="ELSE 0 END CASE";
$qry="SELECT SUM(SCORE), " . ** add explicit list of fields here **
     . " FROM pages WHERE (" . implode(' OR ', $filter) . ")"
     . " GROUP BY " .  ** add explicit list of fields here **
     . " ORDER BY 1 DESC";

Note that this gets highlights the problem that full text searching in mysql is not the ideal solution to the problem - a better approach is to implement a separate table of extracted keywords and a temporary set of search words and perform a query against all three tables:

SELECT COUNT(*) as matched_fields, " . ** add explicit list of fields from page table here ** . "
FROM pages p, keywords k, search_terms s
WHERE k.word=s.word
AND s.session_id='" . some_session_or_request_identifier() . "'
AND k.page_id=p.id
GROUP BY " . ** add explicit list of fields from page table here ** . "
ORDER BY 1 DESC

In addition to simplifying the code somewhat and allowing very large sets of search terms but efficient searching, you can backpopulate relevance scores into the keywords table and use those to generate the score.

Collectives™ on Stack Overflow

Search for a string using MySQL and PHP

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related