Home arrow MySQL arrow Page 7 - Remote Database Table Copier

Sample Code - 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

<? // #!/usr/local/bin/php (put this line as first line for Interland) if (empty($phase)) { setcookie("srcHost", ""); setcookie("srcDB", ""); setcookie("srcUname", ""); setcookie("srcPass", ""); setcookie("tgtHost", ""); setcookie("tgtDB", ""); setcookie("tgtUname", ""); setcookie("tgtPass", ""); } if ($phase == "setHost") { setcookie("srcHost", $srcHost); setcookie("srcDB", $srcDB); setcookie("srcUname", $srcUname); setcookie("srcPass", $srcPass); setcookie("tgtHost", $tgtHost); setcookie("tgtDB", $tgtDB); setcookie("tgtUname", $tgtUname); setcookie("tgtPass", $tgtPass); } ?> <HTML> <HEAD> <TITLE>Remote Database Copier</TITLE> </HEAD> <BODY> <? if (empty($phase)) { ?> <FORM method="post" action="<? echo $SCRIPT_NAME; ?>"> <TABLE border=0 cellpadding=5 cellspacing=0> <TR><TD colspan=2><B>Source:</B></TD></TR> <TR><TD>Hostname:</TD><TD><INPUT NAME="srcHost" TYPE="text"></TD></TR> <TR><TD>Database:</TD><TD><INPUT NAME="srcDB" TYPE="text"></TD></TR> <TR><TD>Username:</TD><TD><INPUT NAME="srcUname" TYPE="text"></TD></TR> <TR><TD>Password:</TD><TD><INPUT NAME="srcPass" TYPE="password"></TD></TR> <TR><TD colspan=2><B>Target:</B></TD></TR> <TR><TD>Hostname:</TD><TD><INPUT NAME="tgtHost" TYPE="text"></TD></TR> <TR><TD>Database:</TD><TD><INPUT NAME="tgtDB" TYPE="text"></TD></TR> <TR><TD>Username:</TD><TD><INPUT NAME="tgtUname" TYPE="text"></TD></TR> <TR><TD>Password:</TD><TD><INPUT NAME="tgtPass" TYPE="password"></TD></TR> <TR><TD colspan=2 align=center><INPUT TYPE="submit"></TD></TR> </TABLE> <INPUT type="hidden" name="phase" value="setHost"> </FORM> <? } if ($phase == "setHost" || $phase == "copy") { 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>"; } if ($phase == "setHost") { if (!($srcRst = mysql_list_tables($srcDB, $srcCnx))) { echo "Unable to get table list for $srcHost.<BR>"; echo mysql_error($srcCnx); } if (!($tgtRst = mysql_list_tables($tgtDB, $tgtCnx))) { echo "Unable to get table list for $tgtHost.<BR>"; echo mysql_error($tgtCnx); } ?> <FORM method="post" action="<? echo $SCRIPT_NAME; ?>"> <TABLE border=0 cellpadding=5 cellspacing=0> <TR><TD colspan=2><B>Source: (<? echo "$srcHost : $srcDB"; ?>)</B></TD></TR> <TR><TD>Table:</TD> <TD><SELECT NAME="srcTable"> <? while ($srcRow = mysql_fetch_row($srcRst)) echo "<OPTION value=\"$srcRow[0]\">$srcRow[0]</OPTION>"; ?> </SELECT></TD></TR> <TR><TD colspan=2><B>Target: (<? echo "$tgtHost : $tgtDB"; ?>)</B></TD></TR> <TR><TD>Table:</TD> <TD><SELECT NAME="tgtTable"> <? while ($tgtRow = mysql_fetch_row($tgtRst)) echo "<OPTION value=\"$tgtRow[0]\">$tgtRow[0]</OPTION>"; ?> </SELECT></TD></TR> <TR><TD colspan=2><B>Options:</B></TD> <TR><TD>Create Table?</TD><TD><INPUT type="checkbox" name="tgtCreate"></TD></TR> <TR><TD>Clear Table?</TD><TD><INPUT type="checkbox" name="tgtClear"></TD></TR> <TR><TD>Copy Data?</TD><TD><INPUT type="checkbox" name="tgtCopy" CHECKED></TD></TR> <TR><TD>Verbose?</TD><TD><INPUT type="checkbox" name="verbose"></TD></TR> <TR><TD colspan=2 align=center><INPUT TYPE="submit"></TD></TR> </TABLE> <INPUT type="hidden" name="phase" value="copy"> </FORM> <? mysql_free_result($srcRst); mysql_free_result($tgtRst); } if ($phase == "copy") { $srcRst = mysql_query("select * from $srcTable;", $srcCnx); $fieldList = ""; $columns = mysql_num_fields($srcRst); for ($i = 0; $i < $columns; $i++) { $tempField = mysql_field_name($srcRst, $i); $tempType = mysql_field_type($srcRst, $i); $fieldList .= $tempField . ", "; if ($tempType == "string") $createSQL .= "$tempField VARCHAR (" . mysql_field_len($srcRst, $i) . "), "; else $createSQL .= "$tempField $tempType, "; } $fieldList = substr($fieldList, 0, strlen($fieldList) - 2); $createSQL = substr($createSQL, 0, strlen($createSQL) - 2); if ($tgtCreate) { $createSQL = "CREATE TABLE $srcTable ( $createSQL );"; if ($verbose) echo $createSQL . "<BR>"; mysql_query($createSQL); $tgtTable = $srcTable . ""; } if ($tgtClear) { mysql_query("delete from $tgtTable;", $tgtCnx); echo "Table $tgtTable cleared.<BR>"; } if ($tgtCopy) { while ($srcRow = mysql_fetch_row($srcRst)) { $insertSQL = "insert into $tgtTable ($fieldList) VALUES ("; for ($i = 0; $i < $columns; $i++) { if ($srcRow[$i] == null) $insertSQL .= "null, "; else $insertSQL .= "'" . $srcRow[$i] . "', "; } $insertSQL = substr($insertSQL, 0, strlen($insertSQL) - 2); $insertSQL .= ");"; if ($verbose) echo "$insertSQL<BR>"; mysql_query($insertSQL, $tgtCnx); } echo "Table copied.<BR>"; } echo "<A HREF=\"$SCRIPT_NAME?phase=setHost\">Another Table</A><BR>"; echo "<A HREF=\"$SCRIPT_NAME?phase=\">Another Database</A><BR>"; mysql_free_result($srcRst); } if ($phase == "setHost" || $phase == "copy") { mysql_close($srcCnx); mysql_close($tgtCnx); } ?> </BODY> </HTML>




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