Member Avatar for braveheart_sb

Hello,

I really would like to know how to compare two different databases hosted on the same server, compare each tableA rows with the tableB rows and find out the match and non Match ones using a php Script.
And display the match and non Match ones

Thank you

  • 2 Contributors
  • 8 Replies
  • 3K Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by DanielTulp

Dani AI

Generated

you can do this with a single MySQL connection when both schemas live on the same MySQL server. Reference each table with db.table in your SQL. is right that JOINs are the tool you want, but note that MySQL does not support FULL OUTER JOIN. Use INNER JOIN for matches, and LEFT/RIGHT JOIN (or a UNION of two LEFT JOINs) to get non-matches. See the MySQL manual for JOIN syntax and notes on full outer joins. MySQL manual: JOIN syntax

Matches and non-matches by userID:

-- rows that match in both tables
SELECT a.*, b.*
FROM db1.TableA AS a
JOIN db2.TableA AS b
  ON b.userID = a.userID;

-- rows in db1 with no match in db2
SELECT a.*
FROM db1.TableA AS a
LEFT JOIN db2.TableA AS b
  ON b.userID = a.userID
WHERE b.userID IS NULL;

-- rows in db2 with no match in db1
SELECT b.*
FROM db2.TableA AS b
LEFT JOIN db1.TableA AS a
  ON a.userID = b.userID
WHERE a.userID IS NULL;

-- emulate full outer non-matches
SELECT a.* FROM db1.TableA a
LEFT JOIN db2.TableA b ON b.userID = a.userID
WHERE b.userID IS NULL
UNION ALL
SELECT b.* FROM db2.TableA b
LEFT JOIN db1.TableA a ON a.userID = b.userID
WHERE a.userID IS NULL;

Insert the rows that are missing from db1:

INSERT INTO db1.TableA (userID, col1, col2)
SELECT b.userID, b.col1, b.col2
FROM db2.TableA AS b
LEFT JOIN db1.TableA AS a
  ON a.userID = b.userID
WHERE a.userID IS NULL;

If you need to detect same userID but differing column values, join on the key and compare with NULL-safe equality:

SELECT a.userID
FROM db1.TableA a
JOIN db2.TableA b USING (userID)
WHERE NOT (a.col1 <=> b.col1 AND a.col2 <=> b.col2 /* ... */);

Practical tips:

  • Use one connection (PDO/mysqli) and proper indexes on userID in both tables.
  • Watch collations and data types across schemas; mismatches can cause false non-matches.
Member Avatar for DanielTulp

search for the sql join command and you'll probably find your answer

Member Avatar for braveheart_sb

search for the sql join command and you'll probably find your answer

<?php
$handle_db1 = mysql_connect("localhost","myuser","apasswd");
$handle_db2 = mysql_connect("127.0.0.1","myuser","apasswd");
mysql_select_db("db1",$firstDB);
mysql_select_db("db2",$SecondDB);
//do a query from db1:
$query = "select * from test"; $which = $handle_db1;
mysql_query($query,$which);
//do a query from db2 :
$query = "select * from test"; $which = $handle_db2;
mysql_query($query,$which);
?>

Until there i got The display of both tables from different sql databases now i need to compare those results and display the non match ones

Edited by braveheart_sb because: n/a
Member Avatar for braveheart_sb

This is the position where i'm now!

<?php
$handle_db1 = mysql_connect("localhost","myuser","apasswd");
$handle_db2 = mysql_connect("127.0.0.1","myuser","apasswd");
mysql_select_db("db1",$firstDB);
mysql_select_db("db2",$SecondDB);
//do a query from db1:
$query = "select * from test"; $which = $handle_db1;
mysql_query($query,$which);
//display result on a table
//do a query from db2 :
$query = "select * from test"; $which = $handle_db2;
//display result on a table
mysql_query($query,$which);
//Merge Both results
$query_join="SELECT FROM SecondDB.TableA a inner join firstDB.TableA  ON TableA.userID=TableB.userID";

?>

Until there i got The display of both tables from different sql databases now i need to compare those results and display the non match ones and match ones after that insert the ones that match on the first database

Member Avatar for DanielTulp

first, you don't need the first two sql commands anymore
second, I think you should specify what you want to select (in this case *)
third, use the outer join command to get non matches

<?php
$handle_db1 = mysql_connect("localhost","myuser","apasswd");
$handle_db2 = mysql_connect("127.0.0.1","myuser","apasswd");
mysql_select_db("db1",$firstDB);
mysql_select_db("db2",$SecondDB);

//get the results when there is a match
$query_match="SELECT * FROM SecondDB.TableA inner join firstDB.TableA  ON TableA.userID=TableB.userID";

//get the results when there is no match
$query_nomatch="SELECT * FROM SecondDB.TableA outer join firstDB.TableA  ON TableA.userID=TableB.userID";
?>

this should give you the results that do ant don't have a match

Edited by DanielTulp because: n/a
Member Avatar for braveheart_sb

first, you don't need the first two sql commands anymore
second, I think you should specify what you want to select (in this case *)
third, use the outer join command to get non matches

<?php
$handle_db1 = mysql_connect("localhost","myuser","apasswd");
$handle_db2 = mysql_connect("127.0.0.1","myuser","apasswd");
mysql_select_db("db1",$firstDB);
mysql_select_db("db2",$SecondDB);

//get the results when there is a match
$query_match="SELECT * FROM SecondDB.TableA inner join firstDB.TableA  ON TableA.userID=TableB.userID";

//get the results when there is no match
$query_nomatch="SELECT * FROM SecondDB.TableA outer join firstDB.TableA  ON TableA.userID=TableB.userID";
?>

this should give you the results that do ant don't have a match

And i can get the result of the queries and insert them on different tables?
Based on your explanation you gave im sure that i can compare all the filds of each table of the database and i also saw it on joint query explanation.
Thank you

Member Avatar for DanielTulp

And i can get the result of the queries and insert them on different tables?

yes, html tables, sql tables, whatever you want

Based on your explanation you gave im sure that i can compare all the filds of each table of the database and i also saw it on joint query explanation.
Thank you

you're welcome

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.