Finding & Replacing Multiple Strings

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Finding & Replacing Multiple Strings

Post by kdidymus »

Folks.

To continue from the last topic, I've been browsing the web for a solution to my particular problem.

Namely, I want to design a PHP page which "gets" two strings from a form. The first is the string I want replaced ($old) and the second is the string I want to replace it with ($new).

What this code is designed to do is act on multiple, SPECIFIED columns to change the strings in ALL rows from $old to $new.

This is the code I have so far which SHOULD change strings in the columns urn, motherurn and fatherurn ONLY:

Code: Select all

<?php
/*  Program name: replace.php
 *  Description:  Replaces old URN with new URN.
 */
 
    // CONNECT TO MYSQL DATABASE
    include_once("../*******.inc.php");
    $cxn = mysql_connect($host,$user,$password)
           or die ("couldn't connect to server");
    mysql_select_db($database);
 
    // GET STRINGS TO REPLACE
    $old=$_GET['old'];
    $new=$_GET['new'];
 
    // REPLACE STRINGS IN DATABASE
    $query = "UPDATE tree SET urn=replace(urn, '$old','$new'),motherurn=replace(motherurn,'$old','$new),fatherurn=replace(fatherurn,'$old','$new')";
    $result = mysql_query($query)
    or die (mysql_error());
 
    // DISPLAY RESULT OF MYSQL ENQUIRY USING HTML
echo "<html>
<head>
<title>REPLACE URN | Status Report</title>
</head>
<body>
<p align='center'><font face='Arial' color='#C0C0C0' size='7'>SUCCESS!</font><font face='Arial' size='5' color='#C0C0C0'><br>
</font><font face='Arial' size='4'>All applicable instances of URN $old have been replaced with URN $new.</font></p>
<p align='center'><font face='Arial' color='#FF0000'>Click
<a target='_top' style='color: #FF0000; font-weight: bold' href='index.htm'>HERE</a> 
to return to the Site Administration Gateway.</font></p>
</body>
</html>";
?>
 
And this is the error I'm getting:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '9999','9998')' at line 1
What's happening here? What have I done wrong?!

KD.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Finding & Replacing Multiple Strings

Post by jayshields »

Missed an apostrophe after $new on line 17?
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Finding & Replacing Multiple Strings

Post by kdidymus »

One word....

DOH!

You're a star. Just goes to prove one of Califdon's mottos. Check, try and check again!

KD.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Finding & Replacing Multiple Strings

Post by kdidymus »

Slight problem with this. Now that it works I can see that it acts as a "wildcard", changing any columns where $old appears WITHIN a string. i.e. It will change 9999 to 8888 and also 999999 to 888899.

How do I prevent this and make the code work on an EXACT instance of $old?

KD.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Finding & Replacing Multiple Strings

Post by kdidymus »

I found this code on the web:

Code: Select all

UPDATE table SET column = REPLACE(column, "from string", "to string") WHERE column LIKE "%from string%";
 
But it returns an error.

Anybody help with the correct syntax? What I WANT to do is something like this:

$query = "UPDATE tree SET urn=replace(urn,'$old','$new') WHERE urn LIKE '$old', motherurn=replace(motherurn,'$old','$new') WHERE motherurn LIKE '$old' etc.

But this is throwing up syntax errors.

KD
Post Reply