Home arrow MySQL arrow Page 4 - Remote Database Table Copier

Copying the Data - 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
After establishing the connections, the next logical step is to move the data from one place to another. This means "SELECT"-ing the data from the source table, and "INSERT"-ing it into the target table. (Assume for now that the target table already exists and has the correct structure to handle the data--weíll deal with the concept of dynamically building the correct table structures in the next section.)

This can be done with the code shown below (assuming that youíve created the connections with the code from the previous section). Rather than provide extensive commentary outside of the code, Iíve added commenting in the code where appropriate to show the function of each section.

// Return a recordset pointer to all rows in the source table $srcRst = mysql_query("select * from $srcTable;", $srcCnx); $fieldList = ""; $columns = mysql_num_fields($srcRst); // Build the field list for our INSERT statement by iterating through the column names for ($i = 0; $i < $columns; $i++) { $tempField = mysql_field_name($srcRst, $i); $fieldList .= $tempField . ", "; } // Chop off the trailing comma and space ( // Iím sure there are more elegant ways of doing this.) $fieldList = substr($fieldList, 0, strlen($fieldList) - 2); // Iterate through the rows, creating an insert statement for each row while ($srcRow = mysql_fetch_row($srcRst)) { $insertSQL = "insert into $tgtTable"; $insertSQL = " ($fieldList)"; $insertSQL = " VALUES "; // Build a comma-separated value list to match the field list generated above $insertSQL = "("; // Iterate through the columns again, this time grabbing // the value from each column // and building a value list. If values are null, // we want to explicitly insert the null // value, because dates will be set to all zeroes, // if theyíre set to an empty string for ($i = 0; $i < $columns; $i++) { if ($srcRow[$i] == null) $insertSQL .= "null, "; else $insertSQL .= "'" . $srcRow[$i] . "', "; } // Again, chop off the comma and space at the end $insertSQL = substr($insertSQL, 0, strlen($insertSQL) - 2); $insertSQL .= ");"; // Execute the finished SQL insert statement on the target table mysql_query($insertSQL, $tgtCnx); } // Free memory from the source recordset. mysql_free_result($srcRst);


Building the insert statement on the fly is made significantly easier by MySQLís flexibility with field types when entering data into tables. In my experience with other databases, specifically Access and Visual FoxPro, specific formatting and quoting syntax must be followed for the inserts to work correctly. An ODBC version of this database copying tool would be wonderful, but it would be difficult to deal with the quirks in SQL syntax for each database engine or ODBC driver.

One other thing I should mention is that copying data this way is probably not appropriate for large tables of more than, say, 10,000 rows. To copy larger sets of data, you would likely have to adjust the timeout settings on your web server, if you have access to it. The timeout for most servers is between 30 and 90 seconds before scripts fail due to timeout, and that may not be enough time to copy exceptionally large tables. Iíve successfully tested the script with tables of up to 5,000 rows without any problems.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- 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: