apostrophe query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

apostrophe query

Post 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"
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post 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%
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post 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
Mohamed
Forum Newbie
Posts: 21
Joined: Fri Jan 19, 2007 6:59 pm
Location: Seattle

Re: apostrophe query

Post 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;
?>
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post 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]
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post 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.
"
Post Reply