Full Text Search Problem

Hi friends,

This is the query for test table

and it has follwing data

1. Pink crib sheet
2. red crib sheet
3. green crib sheet
4. crib bib
5. crib toddler
6. pinkish crib sheet
7. pink cribs sheet
8. crib sheet pink
9. white crib

When I search pink crib I get all rows which have 'pink crib' in it i.e 3 rows

But when I search 'crib' only than query result is zero.

Can you please explain me what wrong I am doing here.

 

Note you are getting records with "pink" or "crib" in them. I think this is what you intended - which would return one record, "Pink crib sheet":

SELECT * FROM search WHERE MATCH (word) AGAINST ('"pink crib"' IN BOOLEAN MODE);

Code (markup):

MySQL ignores terms that are too common in the data set.

You can force it to match them by using boolean mode as so:

SELECT * FROM search WHERE MATCH (word) AGAINST ('crib' IN BOOLEAN MODE);

Code (markup):

Also, why are you using trailing spaces? If it is, for example, to stop 'crib' matching 'cribs', there is no need as MySQL checks that.

 

Thanks krt. Green to you.

Now search is working fine. Now if I search 'Pink Crib'. I get all the rows as all rows have 'crib' in it. I want that if somebody search for 'Pink Crib' than Server should return only those rows which have 'pink crib' in it. Currently using 'IN BOOLEAN MODE' I am getting all rows in result including 'Green Crib' which is not required in result here. what should I do for this.