CODEWITHSUNDEEP
phpmysqlsearch
Max D
Max D

Reputation: 815

MYSQL - Search words in multiple coumns

I want to make a search tool for my website.

if i search for a phrase i want it to search multiple columns

for example if i search dewalt drill and the title has the works dewalt power drill i want it to come up.

also if i search dewalt drill and the tile has dewalt and the description has drill i want it to come up.

but all words of the search must be contained in any combination of fields.

can someone help me with the query?

Currently:

{Select * from products where sku like '%{$searchwords}%' or title like '%{$searchwords}%' or desc like '%{$searchwords}%}

Upvotes: 1

Views: 77

Answers (2)

ejrowley
ejrowley

Reputation: 503

If your table is myisam you can create a fulltext index then use in boolean mode

to add the key:

alter table products ADD FULLTEXT (sku, title, desc)

then your query would be:

 $searchwords = join(' +', explode(' ', $searchwords));

 $query = "SELECT * FROM products WHERE MATCH (sku, title, desc) AGAINST ('{$searchwords}' IN BOOLEAN MODE)";

Upvotes: 1

Wrikken
Wrikken

Reputation: 70460

Your probably want FULLTEXT searching (starting with MySQL 5.6, this is also available for InnoDB tables). You can require all words with BOOLEAN MODE.

Upvotes: 1

Related Questions