Home arrow MySQL arrow Page 5 - Remote Database Table Copier

Adding Some Options - 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


Copying data from one place to another is great, but our script would be much more useful if it could not only copy the data for us, but build the tables that will store the data before copying. By adding that feature, as well as a few others, we have a tool that can be used in a number of different scenarios.

Building the table on the fly is similar to building a row of data, except that you’ve got to know the data type and length of each field that you’re going to build. PHP has some excellent functionality for obtaining this information not only from a table definition, but from a recordset.pointer as well. The functions are used to dynamically build an SQL create statement to build the table which is executed against the database, as shown below:

// Select a recordset with all rows from the source table $srcRst = mysql_query("select * from $srcTable;", $srcCnx); $columns = mysql_num_fields($srcRst); // Iterate through each column, getting the name and type of each for ($i = 0; $i < $columns; $i++) { $tempField = mysql_field_name($srcRst, $i); $tempType = mysql_field_type($srcRst, $i); if ($tempType == "string") { // If the type is string, get the size of the field to size correctly. $createSQL .= "$tempField VARCHAR (" . mysql_field_len($srcRst, $i) . "), "; } else $createSQL .= "$tempField $tempType, "; } // Chop the trailing comma and space $createSQL = substr($createSQL, 0, strlen($createSQL) - 2); // Attach the field list to the other parts of the create statement $createSQL = "CREATE TABLE $srcTable ( $createSQL );"; // Run the query, create the table mysql_query($createSQL);

Unfortunately, this script has limitations. PHP’s mysql_field_type() function does not return exact field type information about each field. Instead, it will return general type information like "string", "int", "real", and "blob". Thus, any varchar, char, or text fields are returned as the string type. While giving enough information to be functional, it’s not entirely accurate. In this script, all "string" fields are interpreted as VARCHAR type, allowing flexibility without taking up a tremendous amount of space.

Other fields are handled without respect to column size, which could cause a problem for date and blob types. Admittedly, this is one aspect of the project that could be improved, but this is a start, and was acceptable for my purpose at the time.

The finished script also contains some additional functionality, which I will not explain in great detail here. It will suffice to explain the purpose of each option, and let the sample code speak for itself.
- Empty a target table before copying data, which was very useful during script testing

- The option to not copy data, which would allows a user to just create tables based on existing structures or empty tables using the option above without copying any data

- ‘Verbose’ mode, which simply echoes each SQL statement to the browser before being executed. This should be used carefully on large tables, which would generate thousands or hundreds of thousands on insert statements.

All of these options can be seen in the completed script presentation at the end of the article. They are controlled by checkboxes on the control form, which turn into boolean switches in the part of the script that actually does the copying.

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