Compare data between databases

[ericsante]

I have 2 mySql databases where I have a UserID field in a table. I want to compare each row between the two tables in their respective database and display the differences (all the fields on each row) on a web page.

I have tried it a few different ways and have been unsuccessful. Any input would be greatly appreciated.

Thanks.

[bogu]

U need to select all the info from one db -> table and put it into an array, then select all the info from the other db -> table put it into another array and compare array's ...

[ericsante]

thanks, I kinda already knew that. I was looking more for code samples, since I stated I tried several things already but did not get my intended resumts.

[bogu]

A little example:

DB1 - contain 1 table

CREATE TABLE `table` (
  `UserID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`UserID`)
) TYPE=MyISAM;

DB2 - contain 1 table

CREATE TABLE `table` (
  `UserID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`UserID`)
) TYPE=MyISAM;

Extract all the UserID from DB2 that are not in DB1

<?php
	//define error's
	define('ERROR_NONE',0);
	define('ERROR_CONNECT',1);
	define('ERROR_OPEN',2);
	$error = ERROR_NONE;

//define db1 connect
define('_Db_User1', 'root');
define('_Db_Pass1', 'root');
define('_Db_Name1', 'db1');
define('_Db_Host1', 'localhost');

//define db2 connect
define('_Db_User2', 'root');
define('_Db_Pass2', 'root');
define('_Db_Name2', 'db2');
define('_Db_Host2', 'localhost');

$_Db_Connect1 = ConnectDB1 ();
//$rez_db1 will contain all the UserID from [db1-table] (ex. 1,2,3,4)
$rez_db1 = '';
$sql_txt = "SELECT UserID FROM `table`";
$sql_rez = mysql_query($sql_txt,$_Db_Connect1) or die(mysql_error($_Db_Connect1));
while ($sc = mysql_fetch_assoc($sql_rez)) $rez_db1.= "{$sc['UserID']},";
mysql_free_result($sql_rez);
if (!empty($rez_db1)) $rez_db1 = substr($rez_db1,0,-1); /* final result */
else $rez_db1 = 0;
mysql_close($_Db_Connect1);

$_Db_Connect2 = ConnectDB2 ();
//now we will get all the records from [db2 - table] where UserID not in [db1 - table]
$sql_txt = "SELECT * FROM `table` ";
$sql_txt.= "WHERE UserID NOT IN ({$rez_db1})";
$sql_rez = mysql_query($sql_txt,$_Db_Connect2) or die(mysql_error($_Db_Connect2));
while ($sc = mysql_fetch_assoc($sql_rez)) {
	echo "<pre>",print_r($sc,1),"</pre>";
}mysql_free_result($sql_rez);
mysql_close($_Db_Connect2);

function ConnectDB1 () {
	global $error;
	//connect to db1
	if(!$_Db_Connect1 = @mysql_connect( _Db_Host1, _Db_User1, _Db_Pass1 )) $error = ERROR_CONNECT;
	if(!@mysql_select_db( _Db_Name1, $_Db_Connect1 ))	$error = ERROR_OPEN;
	if ($error != ERROR_NONE) die('Database1 error : '.$error);
	return $_Db_Connect1;
}

function ConnectDB2 () {
	global $error;
	//connect to db2
	if(!$_Db_Connect2 = @mysql_connect( _Db_Host2, _Db_User2, _Db_Pass2 )) $error = ERROR_CONNECT;
	if(!@mysql_select_db( _Db_Name2, $_Db_Connect2 ))	$error = ERROR_OPEN;
	if ($error != ERROR_NONE) die('Database2 error : '.$error);
	return $_Db_Connect2;
}
?>

[ericsante]

So looking at your example I have come up with the following: However when I add the 2 "auction" numbers that are returned from my second query to the order_2 database I still get results returned, when I should not be getting anything.

So order_2 has 12345, 67890, 13579 in the auction field.
The results returned from $auction_query are 12345, 67890, 13579. However when I run the code below I get 67890 as a result.

If I remove one or more rows from order_2 then I get the proper responce.

Please advise if possiable.

Thanks in advance.

@mysql_select_db($ORDER_DATABASE) or die("Unable to connect to the database");

$order_query = "SELECT auction FROM order_2";
$order_res=mysql_query($order_query) or die(mysql_error());

//$rez_db1 will contain all the Auctions from [order_2] (ex. 1,2,3,4)

$rez_db1 = '';
while ($sc = mysql_fetch_assoc($order_res)) $rez_db1.= "{$sc['auction']},";
	mysql_free_result($order_res);
	if (!empty($rez_db1)) $rez_db1 = substr($rez_db1,0,-1); /* final result */
else $rez_db1 = 0;

mysql_close();

mysql_connect (localhost, $USERNAME, $PASSWORD);
@mysql_select_db($AUCTION_DATABASE) or die("Unable to connect to the database");

$auction_query = "SELECT a.auction, a.winner, b.id FROM ";
$auction_query.= "winners a, auctions b ";
$auction_query.= "WHERE a.auction=b.id AND (b.closed=1 or b.closed=-1) ";
$auction_query.= "AND b.suspended=0 AND a.winner= $ID AND a.auction NOT IN ('{$rez_db1}')";

$auction_res=mysql_query($auction_query) or die("Unable to connect to the database");

// This should return all items that are not in order_2, auction table.

while ($sc = mysql_fetch_assoc($auction_res)) {
echo "<pre>",print_r($sc,1),"</pre>";
}mysql_free_result($auction_res);

mysql_close();