Hi David,
Just wondering if there's any way around the following.
I am using a mixture of Boolean and standard text searching but am having problems with using MySQL's LIKE. For example, if I want to only display 'raincoats' and am searching on the keyword 'rain', I would use the expression "WHERE name LIKE '%rain%'". This would then return correct results like:
Blue Rain Coat
Red Raincoat
Raincoat
Fine. However, the following result would also be returned:
Hotel in Bahrain
Funny example I know, but you get the idea. Is there any way to use the LIKE comparison to return, in this instance, words with spaces before the keyword and not words that contain the keyword in the middle or at the end of them? I have tried "WHERE name LIKE '% rain%'" which I hoped would then only include words starting with 'rain' but this does not work at all. Any ideas please?
Cheers.
Keeop
Hi David,
Duh, good point! I should have remembered that but I think I have changed that in a couple of my sites to use 'name' instead of 'search_name' so that's my excuse for being thick and not checking! Cool, now the space does work with the wildcards, so '% rain%' will only grab what I'm after!
Thanks.
Keeop
Hi again,
Actually, this doesn't quite work as any names beginning with the word get missed out as there's no space before them! Any ideas of a way to get around this?
i.e. 'blue raincoat', 'red raincoat', 'the raincoat' all found but 'rain coat' is not as 'rain' has no preceding space.
Otherwise, this method seems good as irrelevant words such as 'drain', 'refrain' etc. are all omitted.
Cheers.
Keeop
Hi Keeop,
You'd need an OR case to handle that situation; with an option without a preceding % (wildcard), for example:
WHERE name LIKE '% rain%' OR name LIKE 'rain%'Cheers,
David.
Hi Keeop,
I'm not sure if you've spotted, but in the non-Full Text search section of search.php, the LIKE operator is used against the search_name field, which is the name field but with space characters removed.
If that's the case; changing your SQL to actually use name instead of search_name should do the trick - I'm not aware that LIKE treats space any differently to any other character but i'll check that out...
Cheers,
David.