<?php
// ======================================================================================================================================================
	//  Author:     				Gustavo Arcila (tavoarcila at gmail dot com)
	//	Web: 	    				http://www.gurusistemas.com
	//	Name: 	    				phpMyDBCompare.inc.php
	// 	Description:   				Class
	//	License:    	  			GNU General Public License (GPL)
	//  Release Date:               May 29th / 2006
	//  Last Update date: 			May 29th / 2006
	// 
	//  Comments: A very simple tool which will help php developers to compare if their Remote databases are the same as their local databases
	//
	//            Sometimes you made changes in your local database but forget to do in your Remote, even more if you use several databases
	//
	//	Features: 
	//					* Easy to use
	//					* Local and remote databases may have different names.
	//					* Local connection to database is made thru php MySQL native functions and Remote connection is made thru ODBC Drivers
	//					* If the class found differences between databases, the row containing the error is painted red
	//
	//  Requirements:
	//					* MySQL ODBC 3.51 Drivers For the remote database. (can be found at www.mysql.com)
	//
	//  Tested on:
	//					* php 4.4.2
	//
	//				
	//  If you make any modifications making it better, please let me know to tavoarcila at gmail dot com
	//
// =====================================================================================================================================================

class DBCompare{
	var $linkMySQL = "";
	var $linkODBC  = "";
	var $LocalDatabase  = "";
	var $RemoteDatabase = "";
	
	function SelectLocalDatabase($db){
		$this->LocalDatabase=$db;
		$this->RemoteDatabase=$db;
	}
	
	function SelectRemoteDatabase($db){
		$this->RemoteDatabase=$db;
	}
	
	function ConectaLocal($serverdb="localhost",$userdb="root",$passworddb=""){
		$this->linkMySQL = mysql_connect($serverdb, $userdb, $passworddb) or die(mysql_error());
		mysql_select_db($this->LocalDatabase, $this->linkMySQL) or die(mysql_error());
	}

	function ConectaRemote($serverdb="localhost",$userdb="root",$passworddb=""){
		$dsn = "DRIVER={MySQL ODBC 3.51 Driver}; server=".$this->RemoteServer."; CommLinks=tcpip(Host=".$this->RemoteServer."); DATABASE=".$this->RemoteDatabase."; uid=".$this->RemoteUser."; pwd=".$this->RemotePassword;
		$this->linkODBC = odbc_connect($dsn, $db_user, $db_pass);
	}
	
	function Compare(){
		$tableList = array();
		$resultTablesMySQL = mysql_list_tables($this->LocalDatabase, $this->linkMySQL);
		if (!$resultTablesMySQL) {
		   echo mysql_error($this->linkMySQL);
		   exit;
		}
		$nMySql=0;
		while ($rowTablesMySQL = mysql_fetch_row($resultTablesMySQL)) {
		   $tableList[$nMySql][0]=$rowTablesMySQL[0]; $nMySql++;
		}
		mysql_free_result($resultTablesMySQL);$nODBC=0;
		$resultTablesODBC = odbc_tables($this->linkODBC);
		while (odbc_fetch_row($resultTablesODBC)){
			if(odbc_result($resultTablesODBC,"TABLE_TYPE")=="TABLE"){
				$tableList[$nODBC][1]=odbc_result($resultTablesODBC,"TABLE_NAME"); $nODBC++;
			}
		}
		echo "<table border='1' align='center' width='100%'>";
		echo "<tr><td colspan='2' bgcolor='#000000' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>List of Tables</b></font></td></tr>";
		echo "<tr bgcolor='#000000'><td align='center' width='50%'><font face='Verdana,Arial' color='#FFFFFF'><b>Local [".$this->LocalDatabase."]</b></font></td>";
		echo "<td align='center' width='50%'><font face='Verdana,Arial' color='#FFFFFF'><b>Remote [".$this->RemoteDatabase."]</b></font></td></tr>";
		foreach( $tableList as $tablename ){
			echo "<tr align='center'><td>".$tablename[0]."&nbsp;</td><td>".$tablename[1]."&nbsp;</td></tr>";
		}
		if ($nMySql!=$nODBC){
			echo "<tr><td colspan='2' bgcolor='#BE6C6C' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>Table number mismatch (Local: $nMySql <=> Remote: $nODBC)</b></font></td></tr>";
			exit;
		}
		echo "</table><br>&nbsp;";
		echo "<table border='1' align='center' width='100%'>";

		foreach( $tableList as $tablename ){
			$fieldList = array();
			echo "<tr><td colspan='4' bgcolor='#000000'><font face='Verdana,Arial' color='#FFFFFF'><b>Checking table [".$tablename[0]."]</b></font></td></tr>";
			echo "<tr><td bgcolor='#000000' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>Status</b></font></td>";
			echo "<td bgcolor='#000000' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>FIELD DATA</b></font></td>";
			echo "<td bgcolor='#000000' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>LOCAL</b></font></td>";
			echo "<td bgcolor='#000000' align='center'><font face='Verdana,Arial' color='#FFFFFF'><b>REMOTE</b></font></td></tr>";
			$resultMySQL = mysql_query("SHOW COLUMNS FROM ".$tablename[0], $this->linkMySQL);
			if (!$resultMySQL) {
				echo mysql_error($this->linkMySQL);
				exit;
			}
			if (mysql_num_rows($resultMySQL) > 0) {
				$i=0;
				while ($rowTablesMySQL = mysql_fetch_assoc($resultMySQL)) {
					foreach ($rowTablesMySQL as $key => $value){
						$fieldList[$i][0] = $key; 
						$fieldList[$i][1] = $value; $i++;
					}
				}
			}
			$resultODBC=odbc_exec($this->linkODBC,"SHOW COLUMNS FROM ".$tablename[0]);
			$o=0;
			while(odbc_fetch_row($resultODBC)){
				for($i=1;$i<=odbc_num_fields($resultODBC);$i++){
					$fieldList[$o][2] = odbc_result($resultODBC,$i);$o++;
				}
			} 
			$n=1;
			foreach ($fieldList as $value){
				if ($value[1]!=$value[2]){
					$bkg="#BE6C6C";
					$st = "<b>ERR</b>";
				}else{
					$bkg="#FFFFFF";
					$st = "OK";
				}
				echo "<tr bgcolor='$bkg'><td align='center' width='5%'>$st&nbsp;</td>";
				echo "<td align='center' width='33%'>".$value[0]."&nbsp;</td>";
				echo "<td align='center' width='31%'>".$value[1]."&nbsp;</td>";
				echo "<td align='center' width='31%'>".$value[2]."&nbsp;</td></tr>";
				if ($n % 6 == 0){
					echo "<tr><td colspan='4' style='height:5' bgcolor='#c0c0c0'></td></tr>";
				}
				$n++;
			}
		}
		echo "</table><br>&nbsp;";
	}
}
?>