MySQL; Update all rows in a second

I’ve just converted an Excel sheet to MySQL to import numerous data for a new client project. Afterwards when I verified the data all the “Room” numbers was written with an extra .0 at the end besides a few other smaller issues.

So how do you clean up a few hundred rows quickly? You simply write a tiny PHP script to do the job for you:

$query = "SELECT * FROM Table";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
while($row = mysql_fetch_assoc($result))
{
    # Assuming you have an auto_increment Field named ID
    $ID = $row['ID'];
    # Replace the unwanted text with new text
    $updateRow = str_replace("before","after",$row['FieldName']);
    # Update your table with the new value
    $UpdateRecords = "UPDATE Table SET FieldName='$updateRow' WHERE ID='$ID'";
    $Update = mysql_query($UpdateRecords) or die("Query failed: " . mysql_error());
}

Could prove useful to you as well someday – it sure did for me 🙂