Page 1 of 1

Finding & Replacing Multiple Strings

Posted: Wed Jun 25, 2008 5:28 am
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.

Re: Finding & Replacing Multiple Strings

Posted: Wed Jun 25, 2008 6:16 am
by jayshields
Missed an apostrophe after $new on line 17?

Re: Finding & Replacing Multiple Strings

Posted: Wed Jun 25, 2008 6:42 am
by kdidymus
One word....

DOH!

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

KD.

Re: Finding & Replacing Multiple Strings

Posted: Wed Jun 25, 2008 6:56 am
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.

Re: Finding & Replacing Multiple Strings

Posted: Wed Jun 25, 2008 7:53 am
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