mySQL query across 2 databases

I have a query to compare tables in two separate databases - they share the same connection details except the database names are different. But now the databases need to be on different servers so they CAN'T share the same connection. At the moment this is how it is:

$sql="SELECT tab1.* FROM db1.products as tab1, db2.products as tab2 where tab1.products_last_modified != tab2.products_last_modified and tab1.products_id = tab2.products_id";

$result = @mysql_query($sql, $connection);

How can I modify this to compare the databases now they are on different servers/connections? If more queries are needed now, can you suggest them please?
moflintAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
You might make two separate data base connections and selections, then use the resource ID in $connection to execute two separate queries.

You might consider creating a temporary table with the results of the two queries - then you could use your current queries with only minimum modification.
0
moflintAuthor Commented:
Thank you Ray for your comment.

My knowledge of sql is basic. Can you show me what the query would look like that creates a temporary table based on the criteria being given in the current query?

0
Ray PaseurCommented:
Not having your data bases, I can't really test this and I am the widely acknowledged king of typos, so please take this as conceptual code, rather than something you can drop into the scripts.  I'll post as much of an example as I can in a few minutes.

Best regards, ~Ray
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ray PaseurCommented:
See if this makes sense to you.  There may be more efficient ways to do it, but this would seem to work for a proof of concept.

HTH, ~Ray
<?php // RAY_mysql_example_3.php
// QUERY TWO SEPARATE DATA BASES ON TWO SEPARATE SERVERS
 
error_reporting(E_ALL);
 
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
 
 
 
// CONNECTION AND SELECTION VARIABLES FOR THE FIRST DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
 
// OPEN A CONNECTION TO THE FIRST DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$dbcx1 = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO FIRST DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}
 
// SELECT THE FIRST MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$dbsel1 = mysql_select_db($db_name, $dbcx1))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO FIRST DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO FIRST DATA BASE');
}
 
 
 
// CONNECTION AND SELECTION VARIABLES FOR THE SECOND DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
 
// OPEN A CONNECTION TO THE SECOND DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$dbcx2 = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO SECOND DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}
 
// SELECT THE SECOND MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$dbsel2 = mysql_select_db($db_name, $dbcx2))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO SECOND DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO SECOND DATA BASE');
}
 
 
 
// IF WE GOT THIS FAR WE CAN DO QUERIES
 
 
 
// CREATING TEMPORARY TABLE ONE
$sql = "CREATE TEMPORARY TABLE ttab1 (
        _key int NOT NULL AUTO_INCREMENT,
        products_id int NOT NULL,
        products_last_modified DATETIME,
        PRIMARY KEY(_key)
        ) ENGINE=MEMORY";
$res = mysql_query($sql, $dbcx1);
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
 
// CREATING A TEMPORARY TABLE TWO USING CONNECTION ONE
$sql = "CREATE TEMPORARY TABLE ttab2 (
        _key int NOT NULL AUTO_INCREMENT,
        products_id int NOT NULL,
        products_last_modified DATETIME,
        PRIMARY KEY(_key)
        ) ENGINE=MEMORY";
$res = mysql_query($sql, $dbcx1); // NOTE THIS IS RIGHT 
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
 
 
// LOADING THE FIRST TEMPORARY TABLE
$sql = "INSERT INTO ttab1 ( products_id, products_last_modified ) 
        SELECT products_id, products_last_modified FROM tab1"; 
$res = mysql_query($sql, $dbcx1); 
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
 
// LOADING THE SECOND TEMPORARY TABLE - GET DATA USING CONNECTION TWO
$sql = "SELECT products_id, products_last_modified FROM tab2"; 
$res = mysql_query($sql, $dbcx2); 
 
// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
 
// ITERATE OVER THE RESULTS SET - CAN BE MADE MORE EFFICIENT!
while ($row = mysql_fetch_assoc($res))
{
    extract($row);
    $xql = "INSERT INTO ttab2 ( products_id, products_last_modified ) VALUES ( $products_id, \"$products_last_modified\" )";
    $xes = mysql_query($xql, $dbcx1); // NOTE CONNECTION ONE
    if (!$xes)
    {
       $errmsg = mysql_errno() . ' ' . mysql_error();
       echo "<br/>QUERY FAIL: ";
       echo "<br/>$xql <br/>";
       die($errmsg);
    }
}
 
// QUERYING THE TWO TEMPORARY TABLES ON CONNECTION ONE
$tql = "SELECT ttab1.* FROM ttab1, ttab2.* FROM ttab2 WHERE ttab1.products_last_modified != ttab2.products_last_modified AND ttab1.products_id = ttab2.products_id";
$tes = mysql_query($tql, $dbcx1); // NOTE CONNECTION ONE
 
// ETC... 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moflintAuthor Commented:
My goodness Ray, I didn't expect you to do all that - that is superb. Thank you so much - you're a top guy.

0
moflintAuthor Commented:
Thanks again Ray.
0
Ray PaseurCommented:
Thanks for your kind words, and for the points.  Best of luck with it, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.