Apostrophe Search

Dillenger

I'm wondering if anyone can help me... I have a database entry that is rosies's

I would like a user if they search it to be able to get results if they put in: rosies

Is this possible with Fulltext search? I cannot find any solution to this problem.

Thanks.

bpat1434

SELECT * FROM `table` WHERE `column` LIKE '{search_term}%'

If the column data has "rosies's" in it, the following is a list of items that would and wouldn't match:

rosie
rosies
ros
r

rose
ross
rosiess
rosey's

The "%" is a special character meaning "anything". So if you wanted to match "the_rosies's" from a search term of "rosies" then you would use:

%{search_term}%

bradgrafelman

You could also try:

SELECT col1, col2
FROM foo
WHERE col1 REGEXP '[[:<:]]$search(\'s)?[[:>:]]'

where $search would be the given search term (e.g. "rosies").

Dillenger

Hmm, those are good solutions, unfortunately, they are both not using full text searches...

I need one that will work with MATCH AGAINST... not LIKE

bpat1434

Okay... not much changes:

SELECT * 
FROM `table`
WHERE MATCH(`column`) AGAINST ('{search_term}')

And don't forget to use [man]mysql_real_escape_string/man to escape user input so if they use an apostrophe, it will be searched as an apostrophe, and not looked at as a string delimiter.

Dillenger

That still didn't quite work... if I search Jack's it works, but Jacks does not... here is my query, maybe I'm doing something wrong.

SELECT listings.*, menuitems.menuName, menuitems.menuDescription,((1.8*(MATCH (listingCat1,listingCat2,listingCat3,listingCat4,listingCat5) AGAINST ('(jack\'s) ("jack\'s") (+jack\'s)' IN BOOLEAN MODE))) + (1.3*(MATCH (listingName) AGAINST ('\{jack\'s\} (jack\'s) ("jack\'s") (+jack\'s)' IN BOOLEAN MODE))) + (0.6*(MATCH (listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription,menuPriceTag1,menuPriceTag2,menuPriceTag3,menuPriceTag4,menuPriceTag5,menuPriceTag6,menuPriceTag7,menuPriceTag8,menuPriceTag9,menuPriceTag10) AGAINST ('(jack\'s) ("jack\'s") (+jack\'s)' IN BOOLEAN MODE)))) AS relevance 
FROM listings inner join menuitems on (menuitems.listingID = listings.listingID OR menuitems.listingID = listings.masterID) 
WHERE MATCH (listingName,listingCat1,listingCat2,listingCat3,listingCat4,listingCat5,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation) AGAINST ('\{jack\'s\} (jack\'s) ("jack\'s") (+jack\'s)' IN BOOLEAN MODE) 
   OR MATCH (menuName,menuDescription,menuPriceTag1,menuPriceTag2,menuPriceTag3,menuPriceTag4,menuPriceTag5,menuPriceTag6,menuPriceTag7,menuPriceTag8,menuPriceTag9,menuPriceTag10) AGAINST ('("jack\'s") (+jack\'s)' IN BOOLEAN MODE) 
  AND listings.listingActive = 0 
GROUP BY listingID 
ORDER BY relevance DESC

leatherback

You are effectively looking for patters within patterns. You are not going to solve it using normal pattern comparisons. I know there are some phonetic search options, which is what you are after. Here is an earlier thread with suggestions