Remote Database Table Copier - Connecting the World, Two Servers at a Time (
Page 3 of 7 )
Everyone who’s reading this
article has probably used PHP to connect to a single database, return data to a
pointer variable and display it in an HTML wrapper. You’ve probably also used
PHP to take data from an HTML form and insert that data into a table using SQL
insert statements. What you likely have not done is performed both of those
operations on separate databases nearly simultaneously in the same
script.
To perform this trick, we’re going to need to make use of the
database connection identifier, something which is likely ignored in simpler
pages. One very convenient feature of PHP is the assumption that if you’ve only
opened up one database connection, you’re going to use it to perform any further
database operations and you don’t need to specify it in database functions.
However, that connection identifier is available if you need perform operations
on two databases at once.
To use the connection identifier, simply set
the result of your mysql_connect() function to a variable:
$source_cnx = mysql_connect("source_db", "uname", "pass");
You can then use this connection identifier in any
subsequent database related calls such as mysql_select_db() and mysql_query().
It’s important to remember to call mysql_close() for every connection that’s
opened, as well. (This isn’t a complete list of functions that can take the
connection identifier argument, but they are the most commonly used. For a
complete list, please see the PHP manual at www.php.net.) Because the connection
identifier is an optional argument in these functions, it is typically the last
argument in the function call.
After setting up your first connection,
you would set up another in the same way, using a different connection
identifier variable:
$target_cnx = mysql_connect("target_db", "uname", "pass");
Using this information we can set up the shell of
connections for a multiple database connection script with some error checking
as follows:
if (!($srcCnx = mysql_connect($srcHost, $srcUname, $srcPass)))
echo "Unable to connect to $srcHost.<BR>";
if (!mysql_select_db($srcDB, $srcCnx))
echo "Unable to open database $srcDB on $srcHost.<BR>";
if (!($tgtCnx = mysql_connect($tgtHost, $tgtUname, $tgtPass)))
echo "Unable to connect to $tgtHost.<BR>";
if (!mysql_select_db($tgtDB, $tgtCnx))
echo "Unable to open database $tgtDB on $tgtHost.<BR>";
. . . (perform SQL transactions on databases) . . .
mysql_close($srcCnx);
mysql_close($tgtCnx);
/ 14 
