Jump to content

query with replace


CanMan2004

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.