Simple Find and Replace Script PHP/MySQL

Question

I've been plugging away at this simple script and think it's time I try and get some help. I am trying to make a PHP script to communicate with my database. I want to be able to write an array with all the words to find and all the words to replace them with. Example:

array (find_word1,replace_word1,find_word2,replace_word2....etc)

I also want to be able to specify the table to look in, I will change this manually as well.

I will manually fill out all the words but I want to make it dynamic so it doesn't break if my array length changes.

I have tried many things and here is what I have so far:

    <?php

//set up variables and enter your credentials here
$dbname = "name"; 
$dbhost = "localhost";
$dbpass = "password";
$dbuser = "user";
$tbl_name = "Chairs";
//set up your master array! Array goes in this or
$mstr_array = array(
                "find1", "replace1",
                "find2", "replace2");

//connect to database
$con = mysql_connect($dbhost, $dbuser, $dbpass) or die('no connection:'  . mysql_error());
$db = mysql_select_db($dbname) or die ('cant select db: ' . mysql_error());

// reteive each column
$sql = "SHOW COLUMNS FROM `{$tbl_name}`";
$res = mysql_query($sql) or  die ('could not get columns: ' . mysql_error());
$find = 0;
$replace = 1;

while ($col = mysql_fetch_array($res)) {

        $sql = "UPDATE `{$tbl_name}` SET `{$col[0]}` = REPLACE(`{$col[0]}`, '{$mstr_array[$find]}' , '{$mstr_array[$replace]}')";
        $find = $find + 2;
        $replace = $replace + 2;
}

?>

Any help would be greatly appreciated! Thanks

You're probably going to hear this a lot, but you should use mysqli_* or PDO as opposed to mysql_* for security reasons — Chris Forrence
– Chris Forrence, Commented Sep 7, 2012 at 22:40

Ddorda · Accepted Answer · 2012-09-07 23:08:12Z

1

The first thing that ups to my mind is that instead of using:

$mstr_array = array(
                "find1", "replace1",
                "find2", "replace2");

use:

$mstr_keys = array('find1', 'find2', ....);
$mstr_values = array('replace1', 'replace2', ....);

and then on the query you can simply use:

$count = 0;
$sql = "UPDATE `{$tbl_name}` SET `{$col[0]}` = REPLACE(`{$col[0]}`, '{$mstr_keys[$count]}' , '{$mstr_values[$count]}')";
$count++;

I hope it's what you were looking for...

edited Sep 7, 2012 at 23:08

answered Sep 7, 2012 at 22:57

Ddorda

3991 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alanmanderson Over a year ago

mstr_values should have the replace words.

synergy989 Over a year ago

Thanks Ddorda, it wasn't exactly what I was looking for but it gave me a great idea with using your solution you provided. Thanks.

alanmanderson · Accepted Answer · 2012-09-07 23:00:50Z

0

There are a few errors. $col[0] is going to return the value in the column, not the column name. If you are using the update query like this you need to define the column name, not the value as the set value such as :

SET column_name = REPLACE(column_name, ...

However if you know the column name or can set it as a variable the following is all you need:

$column = "chairType";
for($i=0;$i<count($mstr_array);$i++){
    $find=$mstr_array($i++);
    $replace = $mstr_array($i);

    $sql = "UPDATE '{$tbl_name}' SET '{$column}' = REPLACE('{$column}',$find,$replace)
    $mysql_query($sql) or die(mysql_error());
}

However beware that this is going to replace the all rows where the column matches $find.

answered Sep 7, 2012 at 23:00

alanmanderson

8,31012 gold badges55 silver badges89 bronze badges

Collectives™ on Stack Overflow

Simple Find and Replace Script PHP/MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related