Search Replace text in whole mysql database, is it possible ?

[ukscotth]

Hi,

 

I run a social networking site and I want to be able to change my members usernames when neccessary.  The problem is there usernames are stored in various different tables under various different field names.  Is there a way to run a query that searches and replaces text in the whole database ?

 

I know you can do it by backing up the database and the using search replace in notepad but I wanted to add the option to my admin panel.

 

Any ideas ?

 

Thanks in advance.

 

Scott.

 

 

 

[RestlessThoughts]

I don't know if there is a specific way to do that (would be useful if there was) but for your problem have you considered using ID numbers, and having a table with usernames to ID numbers, that way they keep the ID number for life and only one table needs updated everytime they change their username? It would mean selecting the table with names to ID everytime you wanted to print their name but it'd be easier to change it then.

[ukscotth]

Hi, yes i see what you mean but that would involve alot of file editing. But thanks its a shame the people that wrote the script didnt do it that way to begin with

[uniflare]

u could do it, it would just mean using as many queries as there are tables.

 

if its a once in a while thing it wouldnt be offensive to efficiency.

 

you would need to know the table names that hold usernames but thats a given.

[gevans]

Hey Scott,

 

Unfortunately I don't think you're going to like my reply (reply and not an answer). Basically you need to consider a relational database structure. If you have a primary key in your main user table, you should be using it to reference your user in other tables rather than their usernames.

 

Your primary key will (should) never change, so you can easily update a users details. once it's updated it's referenced by an id so is easy to change at anytime.

 

Hope that was at least helpful if not the answer.

 

As far as your question goes run a query for each table

 

UPDATE `the_table` SET `the_field`='new_username' WHERE `the_field`='old_username'

[ukscotth]

I understand and thanks alot for your help. Looks like Il have to do it one table at a time, I may suggest to the original script writers that a relational database structure would be an idea for the next version of the script. Thanks again

[gevans]

As far as your question goes run a query for each table

 

UPDATE `the_table` SET `the_field`='new_username' WHERE `the_field`='old_username'

UPDATE `the_table` SET `the_field`='new_username' WHERE `the_field`='old_username'

UPDATE `the_table` SET `the_field`='new_username' WHERE `the_field`='old_username'

 

Just run one script that updates every table

[ukscotth]

yep thats what il do, thanks gevans

[uniflare]

Loop function to do what you want:

<?php

// Put the names of all the tables and their corrosponding username fields in this array.
$table = Array(
     "table_name" => "field_name",
     "table_name2" => "field_name2"
);

Foreach($table As $Tablename=>$Fieldname){
     $Query = "UPDATE `".$Tablename."` SET `".$Fieldname."`='".$new_username."' WHERE `".$Fieldname."`='".$old_username."'";
     
     $result = mysql_query($Query) or die("Could not update username in table: ".$Tablename."<br />".mysql_error();
}

?>

 

If you want to change usernames that are embedded inside other text, you will need to get that data first with a select query, replace the text, THEN update.

 

something like:

 

<?php

// Put the names of all the tables and their corrosponding username fields in this array.
$table = Array(
     "table_name" => "field_name",
     "table_name2" => "field_name2"
);

Foreach($table As $Tablename=>$Fieldname){
     
$Query = "SELECT `".$Fieldname."` FROM `".$Tablename."`  WHERE `".$Fieldname."` LIKE '%".$old_username."%'";

$result = mysql_query($Query) or die("Could not update username in table: ".$Tablename."<br />".mysql_error();
$array = mysql_fetch_array($result,MYSQL_ASSOC);

// If its not a multi-dimensional array, make it one.
if(!isset($array[0]) || $array[0] == null){
	$array[0] = $array;
}


Foreach($array As $row){
	$oldData = $row[$Fieldname];
	$newData = str_replace($old_username,$new_username,$oldData);

	$Query = "UPDATE `".$Tablename."` SET `".$Fieldname."`='".$new_username."' WHERE `".$Fieldname."`='".$oldData."'";
	$result = mysql_query($Query) or die("Could not update username in table: ".$Tablename."<br />".mysql_error();
}
}

?>