Recovering MySQL Table Data with PHP

PHP Best Practices

How to do a mass update of particular columns in a MySQL table using PHP

Date : 2006-04-26
Recently I made a really big 'oops' when working with a MySQL table in a user authentication database and I thought I'd share how it is that I managed to rectify the problem.

Everyone makes mistakes now and then and thats why its obviously good to have backups of your data. In my case the backup itself wasnt viable but thankfully I was able to track down similar data in another table that I could use to repopulate the table that I made the mistake on.

First of all the mistake was simply that I forgot to put a 'where' clause when trying to update a single record and instead of updating just the one record I updated the whole table. As you may know, MySQL does not have any 'rollback' kind of functionality so when I saw what I had done you might imagine the amount of sweat that broke on my brow and the curses that poured from my mouth.

After I got ahold of myself and did some poking around I found that there was another table that had the same data that I had just overwritten in it but with a completely different row structure. Obviously I couldn't do a simple join to make import the good data into the broken table so I switched gears and started thinking of a script that might do the trick for me.

After a bit of hit and miss and a conversation with a fellow php guru I came up with this fix:

  // Vars for connection to DB

  $username = "username";
  $password = "password";
  $hostname = "localhost";
  $dbname   = "GoodDB";

  // Connect to DB server

  $dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
  //Connect to clients database.

  $selected = mysql_select_db($dbname,$dbh) or die("Could not select clients: $selected");

  // Get All Records
  $sql = "select * from GoodDB";
  $gooddat = mysql_query($sql);

So at this point I've loaded the entire data set of the good table into the $gooddat variable. Next comes the simple but fun part!

while ($row = mysql_fetch_assoc($gooddat)) {

$username = $row['username'];
$pt       = $row['prop_type'];
$pass     = $row['password'];

$sql = "update brokendb.brokentable set password = '$pass' where username='$username' and prop_type = '$pt';";

$doUpd = mysql_query($sql);

And thats it!

Basically all it does is traverse the length of the good table and fill the values of the bad table with its more useful data. Yes, there is probably a better way to do this and a backup would definately been the way to go had there been one but it was a lesson learned for me and I'm wiser because of it.

I hope no one else has to do something odd like this but if ever you do I hope this little bit of code is helpful.

Comments :

No comments yet
  • Search For Articles