Posts Tagged ‘mysql’

MySql find and replace

Posted: October 10, 2012 in MySql
Tags: ,

<? // error_reporting(E_ALL);

function connect($host, $username, $password, $db)
{
$link = mysql_connect($host, $username, $password);
if(!$link)
{
die('<strong>Mysql host '.$host.' not connected: </strong>' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if(!$db_selected)
{
die ('<strong>Database '.$db.' not selected: </strong>' . mysql_error());
}
}
function filterIn($str)
{
if(get_magic_quotes_gpc())
{
$str = stripslashes($str);
}
return mysql_escape_string(trim($str));
}
function getTable($db)
{
$table = array();
$sql = "SHOW TABLES FROM $db";
$result = mysql_query($sql);
if (!$result)
{
echo '<strong>MySQL Error: </strong>'.$sql.'<br />'.mysql_error();
exit;
}
while ($row = mysql_fetch_row($result))
{
$table[] = $row[0];
}
return $table;
}
function getColumn($table)
{
$tableCol = array();
$resultCol = mysql_query("SHOW COLUMNS FROM ".$table);
if (!$resultCol)
{
echo '<strong>MySQL Error: </strong>'.$sql.'<br />'.mysql_error();
exit;
}
if(mysql_num_rows($resultCol) > 0)
{
while($rowCol = mysql_fetch_assoc($resultCol))
{
$tableCol[] = $rowCol['Field'];
}
}
return $tableCol;
}

if(isset($_POST['act']) && $_POST['act']=='compare')
{
$host = filterIn($_POST['host']);
$username = filterIn($_POST['username']);
$password = filterIn($_POST['password']);
$db = filterIn($_POST['db']);
$find = filterIn($_POST['find']);
$replace = filterIn($_POST['replace']);

$affected = 0;
$error = "";
if($host=="") $error.="Host can not be blank.<br />";
if($username=="") $error.="Username can not be blank.<br />";
if($db=="") $error.="Db can not be blank.<br />";
if($find=="") $error.="Find can not be blank.<br />";
if($replace=="") $error.="Replace can not be blank.<br />";

if($error=="")
{
connect($host, $username, $password, $db);
$tableAll = getTable($db);
if(count($tableAll)>0)
{
foreach($tableAll as $table)
{
$columnAll = getColumn($table);
if(count($columnAll)>0)
{
foreach($columnAll as $column)
{
$sql = "update $table SET $column= replace($column,'$find','$replace')";
mysql_query($sql);
$affected+=mysql_affected_rows();
}
}
}
}
echo $affected.' rows affected.';
exit;

}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>DB Find & Replace</title>
<style>
h2{ background-color:#CCCCCC; padding:15px;}

</style>
</head>

<body><br /><br /><br /><h2>DB Find & Replace</h2>
<form name="frm" method="post">
<input type="hidden" name="act" value="compare" />
<table width="600" border="0" cellspacing="1" cellpadding="8">
<tr>
<td colspan="2">
<?=$error;?>
</td>
</tr>
<tr>
<td><strong>Host :</strong></td>
<td><input type="text" name="host" id="textfield"  /></td>
</tr>
<tr>
<td><strong>User Name:</strong></td>
<td><input type="text" name="username" id="textfield" /></td>
</tr>
<tr>
<td><strong>Password :</strong></td>
<td><input type="text" name="password" id="textfield" /></td>
</tr>
<tr>
<td><strong>Database :</strong></td>
<td><input type="text" name="db" id="textfield" /></td>
</tr>
<tr>
<td><strong>Find :</strong></td>
<td><input type="text" name="find" id="textfield" value="" /></td>
</tr>
<tr>
<td><strong>Replace :</strong></td>
<td><input type="text" name="replace" id="textfield" value="" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><label>
<input type="submit" name="button" id="button" value="Submit" />
</label></td>
</tr>
</table>
</form>
</body>
</html>