Jump to content


Photo

query with replace


  • Please log in to reply
7 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 17 September 2006 - 01:26 PM

Hi all

I 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

$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$_GET['personsname']."%'";

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

$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".str_replace("a", "k", $_GET['personsname'])."%'";

but that didnt seem to work.

Can anyone help?

Thanks a million in advance

Dave

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 17 September 2006 - 01:33 PM

try this way round

not tested ok.
<?php
$names=$_POST['names'];
$names=$_GET['personsname'];
$names=str_replace("a","k",$names);
$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%" $names "%' ";
?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 17 September 2006 - 01:36 PM

Hi

thanks for that, what I wanted to do though, was to replace the letter in the database field when I do the query

In the following query I currently use

$sql = "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$_GET['personsname']."%'";

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 again

Dave

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 17 September 2006 - 01:47 PM

what about somethink like this then.

not tested at all a guess

<?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);
}
?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 01:53 PM

You can also try the following.

SELECT
*
FROM
peoplesnames
WHERE
REPLACE(LOWER(fullname), 'a', 'k')
LIKE
%$_GET['personsname']%

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.
<?php
$tr =  array('a'=>'k', 'k'=>'a');
$newname =  strtr(strtolower($_GET['personsname']), $tr);
$sql =  "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$newname."%'";
?>

If you're not already doing so, escape the submitted string with mysql_real_escape_string.

#6 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 17 September 2006 - 02:10 PM

Great, the 2nd one

<?php
$tr =  array('a'=>'k', 'k'=>'a');
$newname =  strtr(strtolower($_GET['personsname']), $tr);
$sql =  "SELECT * FROM peoplesnames WHERE fullname LIKE '%".$newname."%'";
?>

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.

#7 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 17 September 2006 - 02:37 PM

Hi

I've tried adjusting

$tr =  array('a'=>'k', 'k'=>'a');

with

$tr =  array(' '=>'', ''=>' ');

to try and remove spaces but it doesnt seem to work, can anyone give me anymore help?

Thanks in advance

Dave

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 17 September 2006 - 04:28 PM

You can use str_replace 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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users