Home arrow MySQL arrow Page 3 - Remote Database Table Copier

Connecting the World, Two Servers at a Time - MySQL

The purpose of this article is to describe the methods used to create the database copying tool, and outline features of the technologies used in its creation. Specifically, focusing on the following topics: MySQL permissions for remote connections; PHP support for simultaneous connections to multiple databases; building dynamic INSERT and CREATE TABLE statements with PHP.

  1. Remote Database Table Copier
  2. The Permissions Problem
  3. Connecting the World, Two Servers at a Time
  4. Copying the Data
  5. Adding Some Options
  6. Summary
  7. Sample Code
By: Stephen Junker
Rating: starstarstarstarstar / 14
May 07, 2001

print this article


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);

>>> More MySQL Articles          >>> More By Stephen Junker

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: