Words with apostrophes and added slashes

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

Post Reply
Reviresco
Forum Contributor
Posts: 172
Joined: Tue Feb 19, 2008 4:18 pm
Location: Milwaukee

Words with apostrophes and added slashes

Post 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 ";
 
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

Re: Words with apostrophes and added slashes

Post 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
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Words with apostrophes and added slashes

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