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.
As a summary, I present a working script that contains the functionality discussed above, paired with a form interface to collect the data needed for the script to work, like server information, table names, and option settings. The form and processing scripts, which could be separated into two separate files, are actually contained in a single file and controlled by a ‘phase’ variable. While a lengthy discussion of the merits and drawbacks of ‘phased’ scripting could be subject of another article, I mention it here only to clear up any confusion about the structure of the finalized script.
I refrain from calling this a finished product, because there are a number of areas for improvement, and it is my hope that this will serve as a starting point, or at least open up some possibilities that you may not have conceived. With this tool, and the foregoing discussion, I have tried to present some advanced features of PHP and its MySQL functionality such as multiple simultaneous database connections, MySQL database permissions for remote access, building "create table" and insert statements on the fly from existing data, and obtaining and using information about tables and fields.