Page 1 of 1

Words with apostrophes and added slashes

Posted: Wed Apr 29, 2009 3:56 pm
by Reviresco
Not sure if this would be better in the database forum...

Question matching text using mysql regexp when the word being searched for has slashes added.

Specific example: "Alzheimer's". In the db, any instance of this will be spelled "Alzheimer\'s" except for maybe a few that somehow snuck in without slashes.

When someone searches for "Alzheimer's", mysql_real_escape_string adds a slash to it before I query the db.

The search comes up empty. Any tips for how to do this?

Code: Select all

 
$this->sql_query = "SELECT id, segment_name, title, thumbnail, flv, summary, copy, show_number FROM hctv_pages WHERE (title REGEXP '$search_term' OR copy REGEXP '$search_term' OR topic REGEXP '$search_term' OR tags REGEXP '$search_term') AND page_type = 'sub' GROUP BY id ORDER BY show_number DESC ";
 

Re: Words with apostrophes and added slashes

Posted: Wed Apr 29, 2009 6:07 pm
by tech603
If you not looking for specific patterns i would suggest using LIKE instead of REGEXP.

Code: Select all

$this->sql_query = "SELECT id, segment_name, title, thumbnail, flv, summary, copy, show_number FROM hctv_pages WHERE (title LIKE '$search_term' OR copy LIKE '$search_term' OR topic LIKE '$search_term' OR tags LIKE '$search_term') AND page_type = 'sub' GROUP BY id ORDER BY show_number DESC ";

Hope that helps

Re: Words with apostrophes and added slashes

Posted: Sat May 09, 2009 8:33 am
by kaisellgren
LIKE accepts % and _ wildchar characters, but lacks of many features that REGEXP offers. Whether you want LIKE or REGEXP, I leave that to you, but the problem you described that mysql_real_escape_string() makes your ' into \' is not the problem here. The \ character will be stripped on the MySQL side later and it is needed to be there.