Mysql database compare

Posted: December 20, 2011 in MySql, PHP
<? // 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 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 getTableCount($table)
	{
		$sql = "select count(*) from $table";
		$result = mysql_query($sql);		
		if (!$result) 
		{
			echo '<strong>MySQL Error: </strong>'.$sql.'<br />'.mysql_error();
			exit;
		}		
		$row = mysql_fetch_row($result);
		return $row[0];
	}
	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;
			}				
		}
		return $tableCol;
	}
	
	function getTableColumn($dbOne)
	{
		$tableColumn = array();
		$table = getTable($dbOne);
		//=== get column ===
		$cnt = 0;
		for($i=0;$i<count($table);$i++)
		{
			$tableColumn[$cnt]['table'] = $table[$i];
			$tableColumn[$cnt]['count'] = getTableCount($table[$i]);
			$tableColumn[$cnt]['column'] = getColumn($table[$i]);
			$cnt++;
		}
		return $tableColumn;
	}
	function getTableString($arr)
	{
		$str = '';
		$str.= "<h2>".$arr['table']."</h2>";
		$str.= "<table width='600' border='1' cellspacing='0' cellpadding='0'>
				  <tr>
					<td><strong>Field</strong></td>
					<td><strong>Type</strong></td>
					<td><strong>Null</strong></td>
					<td><strong>Key</strong></td>
					<td><strong>Default</strong></td>
					<td><strong>Extra</strong></td>
				  </tr>";
		$column = $arr['column'];
		for($i=0;$i<count($column);$i++)
		{
			$str.= "<tr>";
			$str.= "<td>".$column[$i]['Field']."&nbsp;</td>";
			$str.= "<td>".$column[$i]['Type']."&nbsp;</td>";
			$str.= "<td>".$column[$i]['Null']."&nbsp;</td>";
			$str.= "<td>".$column[$i]['Key']."&nbsp;</td>";
			$str.= "<td>".$column[$i]['Default']."&nbsp;</td>";
			$str.= "<td>".$column[$i]['Extra']."&nbsp;</td>";
			$str.= "</tr>";
		}
		$str.= "</table>";
		return $str;
	}
	function checkTableExist($arr,$table)
	{
		for($i=0;$i<count($arr);$i++)
		{
			if($arr[$i]['table']==$table)
			{
				return 'yes';
			}
		}
		return 'no';
	}	
	function checkColumnExist($arr,$column)
	{
		for($i=0;$i<count($arr);$i++)
		{
			if($arr[$i]['Field']==$column)
			{
				return 'yes';
			}
		}
		return 'no';
	}	
	
	if(isset($_POST['act']) && $_POST['act']=='compare')
	{
		$hostOne = $_POST['hostOne'];
		$usernameOne = $_POST['usernameOne'];
		$passwordOne = $_POST['passwordOne'];
		$dbOne = $_POST['dbOne'];
		$hostTwo = $_POST['hostTwo'];
		$usernameTwo = $_POST['usernameTwo'];
		$passwordTwo = $_POST['passwordTwo'];
		$dbTwo = $_POST['dbTwo'];
		
		//=== db connect ===
		connect($hostOne, $usernameOne, $passwordOne, $dbOne);
		
		//==== get table ====
		$masterOne = getTableColumn($dbOne);			
		
		//=== db connect ===
		connect($hostTwo, $usernameTwo, $passwordTwo, $dbTwo);
		
		//==== get table ====
		$masterTwo = getTableColumn($dbTwo);	
		
		
		$str =  '<table class="result" width="1200" border="0" cellspacing="1" cellpadding="4">';
		$str.='<tr>
				<td colspan="6"><h2>'.$hostOne.' :: '.$usernameOne.' :: '.$passwordOne.' :: '.$dbOne.'</h2></td>
				<td>&nbsp;</td>
				<td colspan="6"><h2>'.$hostTwo.' :: '.$usernameTwo.' :: '.$passwordTwo.' :: '.$dbTwo.'</h2></td>
			  </tr>
			   <tr>
					<th><strong>Field</strong></th>
					<th><strong>Type</strong></th>
					<th><strong>Null</strong></th>
					<th><strong>Key</strong></th>
					<th><strong>Default</strong></th>
					<th><strong>Extra</strong></th>
					<th>&nbsp;</th>
					<th><strong>Field</strong></th>
					<th><strong>Type</strong></th>
					<th><strong>Null</strong></th>
					<th><strong>Key</strong></th>
					<th><strong>Default</strong></th>
					<th><strong>Extra</strong></th>
				  </tr>
			 ';
		$p1 = 0;
		$p2 = 0;		
		for($i=0;$i<count($masterOne)|| $i< count($masterTwo);$i++)
		{
			$str.='<tr><td class="blank" colspan="13"></td></tr>';
			$tableOne = $masterOne[$p1]['table'];
			$tableTwo = $masterTwo[$p2]['table'];
			$countOne = $masterOne[$p1]['count'];
			$countTwo = $masterTwo[$p2]['count'];
			$columnOne = $masterOne[$p1]['column'];
			$columnTwo = $masterTwo[$p2]['column'];
			//==== get table name ======
			if($tableOne == $tableTwo)
			{
				$str.='<tr>
					<td colspan="6"><h3> '.$tableOne.'<span '.(($countOne!=$countTwo)?("class='differ'"):('')).'> ( Rows: '.$countOne.')</span></h3></td>
					<td>&nbsp;</td>
					<td colspan="6"><h3> '.$tableTwo.'<span '.(($countOne!=$countTwo)?("class='differ'"):('')).'> ( Rows: '.$countTwo.')</span></h3></td>
					</tr>';
			    $p1++;
			    $p2++;
				//==== get columns ======
				$cp1 = 0;
				$cp2 = 0;	
				for($j=0;$j<count($columnOne)|| $j<count($columnTwo);$j++)
				{
					$field1 = $columnOne[$cp1]['Field'];
					$field2 = $columnTwo[$cp2]['Field'];
					if($columnOne[$cp1]['Field']==$columnTwo[$cp2]['Field'])
					{
						$str.= "<tr>";
						$str.= "<td class='tdFirst1'>".$columnOne[$cp1]['Field']."&nbsp;</td>";
						$str.= "<td class='tdFirst2'>".$columnOne[$cp1]['Type']."&nbsp;</td>";
						$str.= "<td class='tdFirst3'>".$columnOne[$cp1]['Null']."&nbsp;</td>";
						$str.= "<td class='tdFirst4'>".$columnOne[$cp1]['Key']."&nbsp;</td>";
						$str.= "<td class='tdFirst5'>".$columnOne[$cp1]['Default']."&nbsp;</td>";
						$str.= "<td class='tdFirst6'>".$columnOne[$cp1]['Extra']."&nbsp;</td>";
						$str.= "<td>&nbsp;</td>";
						$str.= "<td class='tdSecond1'>".$columnTwo[$cp2]['Field']."&nbsp;</td>";
						$str.= "<td class='tdSecond2'>".$columnTwo[$cp2]['Type']."&nbsp;</td>";
						$str.= "<td class='tdSecond3'>".$columnTwo[$cp2]['Null']."&nbsp;</td>";
						$str.= "<td class='tdSecond4'>".$columnTwo[$cp2]['Key']."&nbsp;</td>";
						$str.= "<td class='tdSecond5'>".$columnTwo[$cp2]['Default']."&nbsp;</td>";
						$str.= "<td class='tdSecond6'>".$columnTwo[$cp2]['Extra']."&nbsp;</td>";
						$str.= "</tr>";
						$cp1++;
			    		$cp2++;	
					}
					else
					{
						if(checkColumnExist($columnTwo, $field1)=='yes' || ($cp1==count($columnOne)))
						{
							$str.= "<tr class='differField'>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Field']."&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Type']."&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Null']."&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Key']."&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Default']."&nbsp;</td>";
							$str.= "<td>".$columnTwo[$cp2]['Extra']."&nbsp;</td>";
							$str.= "</tr>";
							$cp2++;	
						}
						else
						{
							$str.= "<tr class='differField'>";
							$str.= "<td>".$columnOne[$cp1]['Field']."&nbsp;</td>";
							$str.= "<td>".$columnOne[$cp1]['Type']."&nbsp;</td>";
							$str.= "<td>".$columnOne[$cp1]['Null']."&nbsp;</td>";
							$str.= "<td>".$columnOne[$cp1]['Key']."&nbsp;</td>";
							$str.= "<td>".$columnOne[$cp1]['Default']."&nbsp;</td>";
							$str.= "<td>".$columnOne[$cp1]['Extra']."&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "<td>&nbsp;</td>";
							$str.= "</tr>";
							$cp1++;
						}
					}
						/*$str.= "<tr>";
						$str.= "<td>".$columnOne[$cp1]['Field']."&nbsp;</td>";
						$str.= "<td>".$columnOne[$cp1]['Type']."&nbsp;</td>";
						$str.= "<td>".$columnOne[$cp1]['Null']."&nbsp;</td>";
						$str.= "<td>".$columnOne[$cp1]['Key']."&nbsp;</td>";
						$str.= "<td>".$columnOne[$cp1]['Default']."&nbsp;</td>";
						$str.= "<td>".$columnOne[$cp1]['Extra']."&nbsp;</td>";
						$str.= "<td>&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Field']."&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Type']."&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Null']."&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Key']."&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Default']."&nbsp;</td>";
						$str.= "<td>".$columnTwo[$cp2]['Extra']."&nbsp;</td>";
						$str.= "</tr>";
						$cp1++;
			    		$cp2++;	
					}*/
				}
			}
			else // table mis match
			{
				if(checkTableExist($masterOne,$tableOne)=='yes' && checkTableExist($masterTwo,$tableOne)=='no') // left yes = right no
				{
					$str.='<tr>
					<td colspan="6" class="differ"> <h3>'.$masterOne[$i]['table'].' ( Rows: '.$countOne.')</h3></td>
					<td>&nbsp;</td>
					<td colspan="6"> - </td>
					</tr>';
					$p1++;
				}
				if(checkTableExist($masterOne,$tableTwo)=='no' && checkTableExist($masterTwo,$tableTwo)=='yes') // left no = right yes
				{
					$str.='<tr>
					<td colspan="6"> - </td>
					<td>&nbsp;</td>
					<td colspan="6" class="differ"> <h3>'.$masterTwo[$i]['table'].' ( Rows: '.$countTwo.')</h3></td>
					</tr>';
					$p2++;
				}				
			}
		}		
		echo $str.='</table>';
	}
?>
<!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>Untitled Document</title>
<style>
h2{ background-color:#CCCCCC; padding:15px;}
.result{ background:#69C;}
.result td{ background:#FFF;}
.result td.blank{ background:#69C; height:3px;}
.result th{ background:#CCC;}
.differ{ background-color:#F79496;}
td.differ{ background-color:#F79496;}
.differField td{ background-color:#FFCECF;}
</style>
<script language="javascript" src="http://code.jquery.com/jquery-1.7.js"></script>
<script type="text/javascript">
$('document').ready(function() {
	$('.result tr').each(function(index) 
	{
		if($(this).find('.tdFirst1').text()!=$(this).find('.tdSecond1').text())	
		{			
			$(this).find('.tdFirst1').attr("class","differ");	
			$(this).find('.tdSecond1').attr("class","differ");	
		}
		if($(this).find('.tdFirst2').text()!=$(this).find('.tdSecond2').text())	
		{			
			$(this).find('.tdFirst2').attr("class","differ");	
			$(this).find('.tdSecond2').attr("class","differ");	
		}
		if($(this).find('.tdFirst3').text()!=$(this).find('.tdSecond3').text())	
		{			
			$(this).find('.tdFirst3').attr("class","differ");	
			$(this).find('.tdSecond3').attr("class","differ");	
		}
		if($(this).find('.tdFirst4').text()!=$(this).find('.tdSecond4').text())	
		{			
			$(this).find('.tdFirst4').attr("class","differ");	
			$(this).find('.tdSecond4').attr("class","differ");	
		}
		if($(this).find('.tdFirst5').text()!=$(this).find('.tdSecond5').text())	
		{			
			$(this).find('.tdFirst5').attr("class","differ");	
			$(this).find('.tdSecond5').attr("class","differ");	
		}
		if($(this).find('.tdFirst6').text()!=$(this).find('.tdSecond6').text())	
		{			
			$(this).find('.tdFirst6').attr("class","differ");	
			$(this).find('.tdSecond6').attr("class","differ");	
		}
	});
});
</script>
</head>

<body><br /><br /><br /><h2>DB Compare</h2>
<form name="frm" method="post">
<input type="hidden" name="act" value="compare" />
<table width="600" border="0" cellspacing="1" cellpadding="10">
  <tr>
    <td><strong>Host :</strong></td>
    <td><input type="text" name="hostOne" id="textfield" value="localhost" /></td>
    <td><input type="text" name="hostTwo" id="textfield" value="localhost" /></td>
    </tr>
  <tr>
    <td><strong>User Name:</strong></td>
    <td><input type="text" name="usernameOne" id="textfield" value="root" /></td>
    <td><input type="text" name="usernameTwo" id="textfield" value="root" /></td>
    </tr>
  <tr>
    <td><strong>Password :</strong></td>
    <td><input type="text" name="passwordOne" id="textfield" value="" /></td>
    <td><input type="text" name="passwordTwo" id="textfield" value="" /></td>
    </tr>
  <tr>
    <td><strong>Database :</strong></td>
    <td><input type="text" name="dbOne" id="textfield" value="hf_dump" /></td>
    <td><input type="text" name="dbTwo" id="textfield" value="hf_dump_15dec" /></td>
    </tr>
  <tr>
    <td>&nbsp;</td>
    <td><label>
      <input type="submit" name="button" id="button" value="Submit" />
    </label></td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s