CODEWITHSUNDEEP
mysqlsearchfull-text-search
Whip
Whip

Reputation: 2244

Mysql - Search for a multi-word query

I'm trying to return results from a table of products when a user searches it by name. Currently I have this query (simplified): 

SELECT id, name FROM products WHERE status > 0 AND MATCH(name) AGAINST('test druzy%' IN BOOLEAN MODE) ORDER BY id desc LIMIT 0, 50

Where my search query was test druzy. These are the results I get.

results of first query

The problem is that I don't want results 'Test Tie Necklace' for instance. Mysql is searching for test and druzy seperately. How do I search for both words combined. Performance is also a concern since the table is expected to be very large.

I tried using LIKE as well but that doesn't return any results at all.

Any ideas?

Upvotes: 1

Views: 227

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

To search for both words you could use + operator like 

MATCH(name) AGAINST('+test +druzy' IN BOOLEAN MODE)

Demo

Boolean Full-Text Searches

Upvotes: 1

A. Colonna
A. Colonna

Reputation: 872

Have you tried lower the field name and apply LIKE like this :

SELECT id, name
FROM products
WHERE status > 0
AND LOWER(name) LIKE LOWER('Test Druzy%')
ORDER BY id desc LIMIT 0, 50

EDIT : You can put LOWER at the other side Or use a charset format case-insensitive

Upvotes: 0

Rajat Jain
Rajat Jain

Reputation: 1415

Instead of using MATCH query you should use LIKE query.

Upvotes: 0

Related Questions