[PHP] MySQL databases comparison new vs old

config.php

[CODE]<?php

$host = “host ip”;

$user = “user”;

$pass = “password”;

$database_1 = “database_1”; //usually the newer one let’s say trinity_world_new

$database_2 = “database_2”; // let’s say trinity_world_old

?>[/CODE]

functions.php

[CODE]<?php

function connection($host,$user,$pass){

$link = mysql_connect($host,$user,$pass);

if(!$link) die("Can't connect to the database");


return $link;

}

function table_exists($tablename, $database = false) {

if(!$database) {

    $res = mysql_query("SELECT DATABASE()");

    $database = mysql_result($res, 0);

}


$res = mysql_query("

    SELECT COUNT(*) AS count 

    FROM information_schema.tables 

    WHERE table_schema = '$database' 

    AND table_name = '$tablename'

");


return mysql_result($res, 0) == 1;

}

?>[/CODE]

index.php

[CODE]<?php

error_reporting(E_ALL);

ini_set(“display_errors”,“1”);

require_once(“config.php”);

require_once(“functions.php”);

$link = connection($host,$user,$pass);

echo “

$database_1 vs $database_2

”;

$tables_array_1 = array();

$tables_array_2 = array();

$tables_array_diff = array();

$array_1 = array();

$array_2 = array();

$array_diff = array();

$sql = “SHOW TABLES FROM $database_1”;

$result = mysql_query($sql);

if (!$result) die(mysql_error());

while ($row = mysql_fetch_row($result)) {

array_push($tables_array_1, $row[0]);

}

$sql = “SHOW TABLES FROM $database_2”;

$result = mysql_query($sql);

if (!$result) die(mysql_error());

while ($row = mysql_fetch_row($result)) {

array_push($tables_array_2, $row[0]);

}

echo “

Tables ± comparison


”;

$tables_array_diff = array_diff($tables_array_1, $tables_array_2);

echo "
$database_1 has this tables added: ";

if(count($tables_array_diff)==0) echo “-”;else {

echo “”;

foreach($tables_array_diff as $added_tables) echo $added_tables." | ";

echo “”;

}

$tables_array_diff = array_diff($tables_array_2, $tables_array_1);

echo "
$database_2 has this tables added: ";

if(count($tables_array_diff)==0) echo “-”;else{

echo “”;

foreach($tables_array_diff as $added_tables) echo $added_tables." | ";

echo “”;

}

echo “

Tables columns comparison

”;

foreach($tables_array_1 as $table){

echo "<br />";

$array_1 = array();

$array_2 = array();

$array_diff = array();

$skip = 0;

if(!table_exists($table,$database_1)) {

echo “

Table $table doesn’t exist in $database_1!

”;

$skip = 1;

}

if(!table_exists($table,$database_2)) {

echo “

Table $table doesn’t exist in $database_2!

”;

$skip = 1;

}

if($skip==0){

mysql_select_db($database_1) or die(“can’t select the database”);

$result1 = mysql_query(“SHOW FIELDS FROM $table”);

while($row = mysql_fetch_array($result1)){

array_push($array_1,$row[0]." <span style='color:blue'>".$row[1]."</span> ".$row[2]." ".$row[3]." ".$row[4]." ".$row[5]);

}

mysql_select_db($database_2) or die(“can’t select the database”);

$result2 = mysql_query(“SHOW FIELDS FROM $table”);

while($row = mysql_fetch_array($result2)){

array_push($array_2,$row[0]." <span style='color:blue'>".$row[1]."</span> ".$row[2]." ".$row[3]." ".$row[4]." ".$row[5]);

}

$match = 1;

$array_diff = array();

$array_diff = array_diff($array_1, $array_2);

if(count($array_diff)!=0) {

$match = 0;

echo "<span style='color:red;font-weight:bold'>Table $table are diffrent <br />";

foreach($array_diff as $added_col) echo $added_col." | ";

echo "</span>";

}

$array_diff = array();

$array_diff = array_diff($array_2, $array_1);

if(count($array_diff)!=0) {

$match = 0;

echo "<span style='color:red;font-weight:bold'>Table $table are diffrent <br />";

foreach($array_diff as $added_col) echo $added_col." | ";

echo "</span>";

}

if($match) echo “$table are matched
”;else

if(!$match)

{

$count_1 = count($array_1);

$count_2 = count($array_2);


if($count_1 > $count_2) $count=$count_1;else $count=$count_2;



echo "<table border='1' cellpadding='4'>";


echo "<tr>";

echo "<th>$database_1 $table</th>";

echo "<th>$database_2 $table</th>";

echo "<th>Difference</th>";

echo "</tr>";


for($i=0;$i<$count;$i++){

	echo "<tr>";

	if(isset($array_1[$i])) echo "<td>".$array_1[$i]."</td>";else echo "<td>-</td>";

	if(isset($array_2[$i])) echo "<td>".$array_2[$i]."</td>";else echo "<td>-</td>";

	if(isset($array_2[$i])&&isset($array_2[$i])){

	if($array_1[$i]==$array_2[$i]) echo "<td>-</td>";else echo "<td><span style='color:red;font-weight:bold;'>YES!</td>";

	}else echo "<td><span style='color:red;font-weight:bold;'>YES!</td>";

	echo "</tr>";


}


echo "</table>";

}

}

}

mysql_close($link);

?>[/CODE]

If there’s an error, please correct me /emoticons/default_smile.png.

i use a 3rd party program to compare db’s, but i guess this will work as well

Next Step: compare every table content :wink: