CODEWITHSUNDEEP
phpmysqlsearchmysqlifull-text-search
RoiEX
RoiEX

Reputation: 1235

Implement (almost) fully featured Fulltext search using PHP + mysql

I'm working on a project which requires a search engine.
I'm aware of MySQL's FULLTEXT IN BOOLEAN MODE feature. But my current implementation can be simply broken by adding quotes in the text field...
What I need:
A Search engine which allows for features like +obligatory, optional, "complete phrase" and -mustNotContain and sorts the results accordingly (most matches on top etc.)

Possible solutions I think of:
a function which escapes the search string in a way, the mentioned features persist, but the MySQL won't get mad if the syntax is completely wrong.

A complete mysql-independent search algorithm written in PHP (Please tell me, if there are any libraries you would suggest) which does the same thing (and maybe allows for auto complete - completely optional)

If you were asking yourself what my current implementation is, it's currently something basic and unfinished like

$result = $mysqli -> query("SELECT * FROM table WHERE MATCH(TITLE) AGAINST('$q' IN BOOLEAN MODE) ORDER BY MATCH(TITLE) AGAINST('$q' IN BOOLEAN MODE)")

Any help is appreciated!

Upvotes: 0

Views: 451

Answers (1)

Martin
Martin

Reputation: 446

You really want to use mysqli::prepare instead of interpolating the search string into the query (see http://php.net/manual/en/mysqli.prepare.php). The result should look something like this.

$statement = $mysqli->prepare("SELECT * FROM table WHERE MATCH(TITLE) AGAINST(? IN BOOLEAN MODE) ORDER BY MATCH(TITLE) AGAINST(? IN BOOLEAN MODE)");
$statement->bind_param("ss", $q, $q);
$statement->execute();
$result = $statement->get_result();

The statement is first prepared and then parameters are bound to the question mark placeholders. No manual escaping is required.

Upvotes: 3

Related Questions