Remote Database Table Copier - Adding Some Options
( Page 5 of 7 )

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