Page 1 of 1

apostrophe query

Posted: Wed Jan 24, 2007 6:43 am
by gurjit
hi all,

I have a query as such

Code: Select all

$term = addslashes($term);
$full_search = $field . " LIKE '%" . $term . "%';

SELECT * ,
				if (ag_website_status IS NULL or ag_website_status!=1, 1, 0)
				AS isnull,
				if (ag_area_search IS NULL or ag_area_search = '', or_post_town, ag_area_search)
				AS sort_field
				FROM clean_table INNER JOIN agent_extensions ON
				orid = frn_orid
				WHERE " . $full_search . " AND or_yp_classification_code = '" . $_GET['type'] . "'
				ORDER BY isnull ASC, sort_field ASC, or_company_name ASC
Now if i do a search with the $term set to "Warren's" with a apostrophe I want the record "Warrens" without the apostrophe to come out too and vice versa.

How can I do this? What am I doing wrong?

At the moment if you serach for "Warren's", you get "Warren's" AND if you search for "Warrens" you get "Warrens"

Posted: Wed Jan 24, 2007 7:33 am
by dude81
:idea: Seriously I think you need to do something like google ,

when ever you come across apostrophe

Code: Select all

explode()
with apostrophe and search for the first word if it exists, concatenate the rest of the array words
ask the user did he mean warrens(on concatenation it results to warrens)?? if he clicks on that link search with %warren%

Posted: Wed Jan 24, 2007 7:35 am
by kaszu
Maybe i'm talking rubish, but i think regular expresions could be answer
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Re: apostrophe query

Posted: Wed Jan 24, 2007 9:38 am
by Mohamed
gurjit wrote: At the moment if you serach for "Warren's", you get "Warren's" AND if you search for "Warrens" you get "Warrens"
I think str_replace can help us in this matter.
replace all apostrophes with nothing, then search both cases(apostrophe and non apostrophe. I don't know how to do the other one.

Code: Select all

<?php

	$string = "warren's";
	$string2 = str_replace("'","",$string);
        echo $string."\n".$string2;
?>

Posted: Wed Jan 24, 2007 9:44 am
by Kieran Huggins
you could build a simple regexp to strip everything except word characters and spaces.

if you want to get even fuzzier, you could use the mysql SOUNDEX() function.

Posted: Wed Jan 24, 2007 9:58 am
by gurjit
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


guys - i've tried everything.

I just thought may be the only way to do this is too SUBSTRING in the sql and read the first 5 chars:

[syntax="sql"]

SELECT * , if (ag_website_status IS NULL or ag_website_status!=1, 1, 0) AS isnull, if (ag_area_search IS NULL or ag_area_search = '', or_post_town, ag_area_search) AS sort_field FROM clean_table INNER JOIN agent_extensions ON orid = frn_orid WHERE (SUBSTRING(or_company_name,0,5) = 'Warrens') OR(or_company_name LIKE '%Warrens%' OR ag_area_search LIKE '%Warrens%' OR or_post_town LIKE '%Warrens%' OR or_postcode LIKE '%Warrens%' OR ag_website LIKE '%Warrens%') AND or_yp_classification_code = '8630' ORDER BY isnull ASC, sort_field ASC, or_company_name ASC LIMIT 0,25 

however I can see problems here if the search string contains an apostrophy before 5 chars e.g. "D'lounge"

I can't do an explode as suggested because the search could have an apostrophy any where in the word as for example "D'lounge". Company names are a pain.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Jan 24, 2007 10:02 am
by gurjit
This is what I read on SOUNDEX

"The answer to whether soundex works except for the first letter in klancy vs clancy is to always prefix words with the same letter.

aklancy will match aclancy
bklancy will match bclancy

soundex seems to only check the 1st 2 syllables.??
ie: spectacular matches spectacle

just a thought if you rely on soundex.
"