I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure.
Table(ingredient) - Applied fulltext index on ingredient column.
------------------------------------------------------
ingredientID rcteID ingredient
310 1 Mint Leaves
311 1 Corriender Leaves
312 1 GreenChili
I am trying to fetch above record below fulltext search query but not getting that record.
SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
AGAINST ('+Mint Leaves +Corriender Leaves +Greenchili' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID`
IN ( 1 )
GROUP BY `Ingredient`.`rcteID`
Why above query is not working for above record?
When I tried below query it worked. just changed searching text.
SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
AGAINST ('+Greenchili +Mint Leaves +Corriender Leaves' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`
OUTPUT
--------------------
ingredientID rcteID
311 1
Don't understand what's going on. Why first query not returning any result and below query returning result?
Best Answer
According to the MySQL Documentation on FULLTEXT Boolean Searching
MAIN PROBLEM
Mint, Leaves, Corriender, Greenchili
)COURSE OF ACTION
You need to adjust the MATCH clause to query three string tokens. You do that by putting double quotes around
"Mint Leaves"
and"Corriender Leaves"
:GIVE IT A TRY !!!
NOTE: Corriender should be spelled Coriander