CODEWITHSUNDEEP
phpmysqlsearchpdo
user3392886
user3392886

Reputation: 13

Allow searching in multiple columns with multiple keywords

Here is my scenario: I have a search which searches multiple columns

$sql = "SELECT * FROM $this->_table WHERE r_title LIKE ?
   OR r_level LIKE ?
   OR r_section LIKE ?
   OR r_excerpt LIKE ?
   ORDER BY r_id DESC"

This works good, but for example the title could be "Squash" and the section could be "Soup" so if a user enters "squash soup" it doesn't return any results.

Does this make sense? How can I get it to deliver a more relevant, merged response?

The section would technically be called "Soups"not "Soup", I still want it to return "Squash Soup" if the section is really soups, just ignore the "s".

Upvotes: 0

Views: 101

Answers (1)

miken32
miken32

Reputation: 42695

You need to use MySQL's search functionality. Something like this:

SELECT * FROM table
WHERE MATCH(r_title, r_level, r_section, r_excerpt)
    AGAINST (? IN BOOLEAN MODE)
ORDER BY r_id DESC

Boolean search, by default, looks for any word in the search phrase, so no pre-processing is necessary there. You'll need to make sure your tables have appropriate indices on them however:

ALTER TABLE table
    ADD FULLTEXT INDEX (r_title),
    ADD FULLTEXT INDEX (r_level),
    ADD FULLTEXT INDEX (r_section),
    ADD FULLTEXT INDEX (r_excerpt);

Upvotes: 3

Related Questions