CODEWITHSUNDEEP
phpmysqldatabase
synergy989
synergy989

Reputation: 119

Simple Find and Replace Script PHP/MySQL

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

Upvotes: 2

Views: 1440

Answers (2)

Ddorda
Ddorda

Reputation: 399

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...

Upvotes: 1

alanmanderson
alanmanderson

Reputation: 8200

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.

Upvotes: 0

Related Questions