Replace

[alirezaok]

hello

i have a table with this fields:

1- number (type interger autonumber)
2- title (type text)
3- body (type text)

i have many records.

i want replace all "city" word with "town" word in anywhwre in all of my recoreds

"city" words are in anywhere of my data of my table in title filed and body filed.

for example first record in my table is:

1- number = 1
2 - title = the city and my book at city time
3- body = city and many people in city are near river

ok
i need update or replace query or script for do it

[sidney]

the below should do the job you will need to add table name in querys and create a connect.php to connect to your data base

 <?php 
$from="city";
$to="town";
require("../temp/connect.php");
$query1="SELECT number FROM tablename ORDER BY number";
$sql1=mysql_query($query1);
while($row1=mysql_fetch_row($sql1))
	{
	mysql_query("UPDATE tablename SET title=REPLACE(title,'".$from."','".$to."'), body=REPLACE(body,'".$from."','".$to."') WHERE number ='".$row1[0]."'");
	}

?>  

[alirezaok]

thanks
excusme if i have not number field (type interger autonumber)

and my fields are :

1- title (type text)
2- body (type text)

i can not do it. anyone can help me when i have 2 field and i have not number field (type interger autonumber) in my table

[sidney]

  $from="city"; 
$to="town"; 
require("../temp/connect.php"); 
$query1="SELECT title,body FROM tablename ORDER BY number"; 
$sql1=mysql_query($query1); 
while($row1=mysql_fetch_row($sql1)) 
    { 
    mysql_query("UPDATE tablename SET title=REPLACE(title,'".$from."','".$to."'), body=REPLACE(body,'".$from."','".$to."') WHERE title ='".$row1[0]."' and body ='".$row1[1]."'"); 
    } 

[alirezaok]

i'm very sorry. i changed your script for me. but it is not work and dose not show any error. why? 🙁

$Host = "localhost";
$User = "root";
$Password = "";
$DBName = "118";
$TableName = "test";

$from="ab"; 
$to="cd"; 
$Link = mysql_connect ($Host, $User, $Password);
$query1="SELECT telno,fname,lname,address FROM $TableName ORDER BY telno";
$sql1=mysql_db_query ($DBName, $query1, $Link); 

while($row1=mysql_fetch_row($sql1))  

    {  

    mysql_query("UPDATE $TableName SET
	telno=REPLACE(telno,'".$from."','".$to."'),
	fname=REPLACE(fname,'".$from."','".$to."'),
	lname=REPLACE(lname,'".$from."','".$to."'),
	address=REPLACE(address,'".$from."','".$to."')
	WHERE telno ='".$row1[0]."' and fname ='".$row1[1]."' and lname ='".$row1[2]."' and address ='".$row1[3]."'");  

    }

is any mistak in this script?

[sidney]

nothing obvious but try connecting a different way and some error reporting

also the where statment doesnt need to be all fields just enough to make it unique

 $from="ab";  

$to="cd";  

$Link = mysql_connect ($Host, $User, $Password) or die(mysql_error()); 
mysql_select_db($DBName) or die(mysql_error());
$query1="SELECT telno,fname,lname,address FROM $TableName ORDER BY telno"; 
$sql1=mysql_query ($query1, $Link);  

while($row1=mysql_fetch_row($sql1))   

    {   

    mysql_query("UPDATE $TableName SET 
    telno=REPLACE(telno,'".$from."','".$to."'), 
    fname=REPLACE(fname,'".$from."','".$to."'), 
    lname=REPLACE(lname,'".$from."','".$to."'), 
    address=REPLACE(address,'".$from."','".$to."') 
    WHERE telno ='".$row1[0]."' and fname ='".$row1[1]."'");   

    } 

but i just tested and you can do an update on whole table with just the update query so no need to even do a loop or the initail query to the database

 $from="ab";   

$to="cd";   

$Link = mysql_connect ($Host, $User, $Password) or die(mysql_error());  

mysql_select_db($DBName) or die(mysql_error()); 

mysql_query("UPDATE $TableName SET 
state=REPLACE(state,'".$from."','".$to."'), 
city=REPLACE(city,'".$from."','".$to."') 
");    

[alirezaok]

Thank You very much. my problems resolved.
i wish nice days for you
Alireza - Iran - Qazvin City