I Need to ignore non-alpha characters in PHP / mySQL search...

Hello All,

I have a list of names, etc in a mySQL database where some contain periods as well as commas, /, &, etc.

How can I include those names in the results if a user does not enter those extra characters in the search form ?
Basically I want those characters completely ignored.

Examples:
A record might be for Mr Black and I want the results to display that record if a user searched for Mr Black or Mr. Black
Alternatively, The record could include the period (as in Mr.) so it should work inversely too.

I tried using LIKE in my sql query but this didn't provide the results I need.

To make this even more interesting, there are also situations where & and AND need to be interchanged. So a search for 'Black and Son' should result in records that are actually 'Black & Son', and inversely.
What would be the best approach for this?
I am not able to modify the way names are formatted within the records, but I COULD add an extra field where alternate name formats are stored. I would really like to avoid this option as it would mean manual modification to such records.

Thanks in advance for your help!
Cheers

Oliver
shmanazleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkna_gunnCommented:
i would use str_replace function to replace any characters you dont want. only prob is that you have to pull back the records first and then remove all the unwanted chars then compare with the search query entered.

e.g.

$name = str_replace('&','and',$db_name); // where db_name is field from row of db record

you can do the same if you want to remove , / . etc just replace them with either a space or nothing

e.g. $field = str_repalce('.','',$db_field);

mysql does have a replace function that is similar to php one so try it out
e.g. "select replace({your_field_name},'&','and') where....."


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_GeG_Commented:
I have only a real ugly and cpu wasting solution, so just use it if it is not you server :>

<?
$search="max";
$s='';
for ($i=0; $i<strlen($search); $i++){
    $s.=($i?'[^[alpha]]':'').$search{$i};
}
$query="SELECT * FROM table WHERE field REGEXP '$s'";
//..
?>
0
frugleCommented:
Fulltext searching does this automatically.

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.