CanMan2004 Posted September 17, 2006 Share Posted September 17, 2006 Hi allI have a sql database that contains 2,000 records and within it, I have a field called `fullname`I then have a php website form which has a field called personsname`, the form method is "GET"At the moment im performing a standard query which looks like[code]$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$_GET['personsname']."%'";[/code]So if there is a record in the field called "DavidLawson" then this is returned if the user searches for "David" or "Lawson" or "vidLaws" and so on.What I want to do is to keep the same query but change it so that it replaces the letter A with a K in the field data when it does the search.Keeping the "DavidLawson" example above, if I then did a search for "David", it would return nothing, but if I did a search for "Dkvid" it would return a result as it has replaced the "a" in "David" with a "k", again, if I did a search for "DkvidLawson" it would find nothing as "Lawson" contained a "a", but if I did a search for "DkvidLkwson" then it would return a result.Does that make sense?I have been battling with this for ages,I have tried to change it to[code]$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".str_replace("a", "k", $_GET['personsname'])."%'";[/code]but that didnt seem to work.Can anyone help?Thanks a million in advanceDave Quote Link to comment Share on other sites More sharing options...
redarrow Posted September 17, 2006 Share Posted September 17, 2006 try this way roundnot tested ok.[code]<?php$names=$_POST['names'];$names=$_GET['personsname'];$names=str_replace("a","k",$names);$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%" $names "%' ";?>[/code] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 17, 2006 Author Share Posted September 17, 2006 Hithanks for that, what I wanted to do though, was to replace the letter in the database field when I do the queryIn the following query I currently use[code]$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$_GET['personsname']."%'";[/code]it would replace the letter in `fullname` rather than `personsname`Does that make sense? Basically what you showed me, but swiched around so it performs the replace on the sql db field and not on the field on the form.Thanks againDave Quote Link to comment Share on other sites More sharing options...
redarrow Posted September 17, 2006 Share Posted September 17, 2006 what about somethink like this then.not tested at all a guess[code]<?php$sql = "SELECT fullname FROM peoplesnames ";$result=mysql_query($sql);while($record=mysql_fetch_assoc($result)){$fullname=$record['fullname'];$fullname=str_replace("a","k",$fullname);$sql = "SELECT * FROM peoplesnames WHERE $fullname LIKE '%".$_GET['personsname']."%'";$sql=mysql_query($sql);}?>[/code] Quote Link to comment Share on other sites More sharing options...
shoz Posted September 17, 2006 Share Posted September 17, 2006 You can also try the following.[code]SELECT*FROMpeoplesnamesWHEREREPLACE(LOWER(fullname), 'a', 'k')LIKE%$_GET['personsname']%[/code]A faster alternative should be the following. Use PHP to convert the submitted string. Note that k's are made into a's and a's are made into k's.[code]<?php$tr = array('a'=>'k', 'k'=>'a');$newname = strtr(strtolower($_GET['personsname']), $tr);$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$newname."%'";?>[/code]If you're not already doing so, escape the submitted string with [url=http://www.php.net/mysql_real_escape_string]mysql_real_escape_string[/url]. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 17, 2006 Author Share Posted September 17, 2006 Great, the 2nd one[code]<?php$tr = array('a'=>'k', 'k'=>'a');$newname = strtr(strtolower($_GET['personsname']), $tr);$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$newname."%'";?>[/code]worked great.Is there a method to do it with spaces, so it would remove spaces and replace them with nothing and then do a search, so David Lawson is seen as DavidLawson. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 17, 2006 Author Share Posted September 17, 2006 HiI've tried adjusting[code]$tr = array('a'=>'k', 'k'=>'a');[/code]with[code]$tr = array(' '=>'', ''=>' ');[/code]to try and remove spaces but it doesnt seem to work, can anyone give me anymore help?Thanks in advanceDave Quote Link to comment Share on other sites More sharing options...
shoz Posted September 17, 2006 Share Posted September 17, 2006 You can use [url=http://www.php.net/str_replace]str_replace[/url] to remove spaces.strtr was used previously because what you wanted then required that a search for dkrwk would be seen as a search for "darwa", but a literal search for "darwa" should be seen basically as a search for "dkrwk".If you only want to remove spaces now, use str_replace. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.