Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

query with replace

Recommended Posts

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

[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 advance

Dave

Share this post


Link to post
Share on other sites
try this way round

not 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]

Share this post


Link to post
Share on other sites
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

[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 again

Dave

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
You can also try the following.

[code]
SELECT
*
FROM
peoplesnames
WHERE
REPLACE(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].

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Hi

I'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 advance

Dave

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.