Archive for the ‘MySql’ Category

You can add this line in top of your script which will allow you to insert any foreign values.

SET FOREIGN_KEY_CHECKS = 0;

And then at end again add

SET FOREIGN_KEY_CHECKS = 1;

 

Remove duplicate entry from database

Posted: September 3, 2013 in MySql
Tags:

DELETE FROM tbl_user
WHERE user_id NOT
IN (
SELECT user_id
FROM (
SELECT MIN( a.user_id ) AS user_id, concat( first_name, last_name ) AS fullname
FROM tbl_user a
GROUP BY fullname
)x
)

If you want to update one column with another table’s column you can use below query

UPDATE `tb_student`
 INNER JOIN  tb_student_backup ON ( tb_student_backup.student_id = tb_student.student_id)
 SET tb_student.address_1 = tb_student_backup.street_address

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>

Mysql reverse like

Posted: July 11, 2012 in MySql
SELECT
*
FROM town_hint
WHERE
townName like '%M%' OR
townName like '%Mo%' OR
townName like '%Mor%' OR
townName like '%Morr%' OR
townName like '%Morri%' OR
townName like '%Morris%' OR
townName like '%Morrist%' OR
townName like '%Morristo%' OR
townName like '%Morristow%' OR
townName like '%Morristown%'
ORDER BY
CASE WHEN
townName like '%Morristown%' THEN 9
WHEN townName like '%Morristow%' THEN 8
WHEN townName like '%Morristo%' THEN 7
WHEN townName like '%Morrist%' THEN 6
WHEN townName like '%Morris%' THEN 5
WHEN townName like '%Morri%' THEN 4
WHEN townName like '%Morr%' THEN 3
WHEN townName like '%Mor%' THEN 2
WHEN townName like '%Mo%' THEN 1
WHEN townName like '%M%' THEN 0 ELSE 0 END desc

In windows-7 copy file from below location and paste it to any new system.

C:\Users\user.name\AppData\Roaming\MySQL\mysqlx_user_connections.xml

SELECT *
FROM `student`
WHERE FIND_IN_SET( '2', subject_id )