The X-Lab - Experimental Development Repository

MySQL Table Copier
Category: database
Language: PHP/MySQL
Description:
For MySQL versions that don't provide a quick/easy way to copy table(s) from one database to another
Source Code:
<!--### Written by Micah Atwell - www.micahatwell.com/xlab ###-->
<?php
   define("SQL_HOST","your_sqlhost");
   define("SQL_USER","your_username");
   define("SQL_PASS","your_pass");
   define("PAGE",basename(__FILE__));
   
   $skip_dbs=array("mysql");
   $sqlStat=false;
   
   function sqlAccess($db=null){
      global $dbcon,$sqlStat;
      if(!$sqlStat){
         $dbcon=@mysql_connect(SQL_HOST,SQL_USER,SQL_PASS)or die(mysql_error());
         $sqlStat=true;
      }
      if($db!=null){
         $sel_db=@mysql_select_db($db,$dbcon)or die("Error trying to select database <code>".
            $db."</code>:<br>".mysql_error());
         if(!$sel_db)die("Database not selected: <code>".$db."</code>");
      }
   }
   ### RETRIEVE LIST OF ALL DATABASES ###
   $DATABASES=array();
   sqlAccess();
   $qSelDBs=mysql_query("SHOW DATABASES")or die(mysql_error());
   while($res=mysql_fetch_assoc($qSelDBs))if(!in_array($res{'Database'},$skip_dbs))$DATABASES[$res{'Database'}]=array();
   foreach($DATABASES as $DB_NAME=>$TABLES){
      $qSelTbls=mysql_query("SHOW TABLES IN ".$DB_NAME)or die(mysql_error());
      if(mysql_num_rows($qSelTbls)==0)continue;
      while($res=mysql_fetch_assoc($qSelTbls))$DATABASES[$DB_NAME][]=$res{'Tables_in_'.$DB_NAME};
   }
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
   <title>MySQL Database Table Copier</title>
   <style type="text/css">
      body{font:75% Verdana,Arial,Helvetica,sans-serif;}
      td{font:75% Verdana,Arial,Helvetica,sans-serif;text-align:center;vertical-align:top;}
         td.divider{background-color:#aba;padding:0px;width:1px}
      table.bdr{border:solid #aba 1px;}
      th{font:75% Verdana,Arial,Helvetica,sans-serif;background-color:#ded;}
      code{font-size:12px;color:green;font-weight:bold;}
         code#srcPath{color:blue;font-weight:normal;}
         code#destPath{color:red;font-weight:normal;}
         code.qs{color:purple;font-weight:normal;}
      .red{color:red;}
      #recData{border:solid #bcb 1px;padding:10px;background-color:#efe;}
   </style>
   <script type="text/javascript">
      <?php
         foreach($DATABASES as $DB_NAME=>$TABLES)echo 'var db_'.$DB_NAME.'=new Array("'.implode('","',$TABLES).'");'."\n";
         echo 'var DATABASES=new Array(db_'.implode(",db_",array_keys($DATABASES)).');'."\n";
         echo 'var DB_NAMES=new Array("'.implode('","',array_keys($DATABASES)).'");'."\n";
      ?>
      function updPathPrvs(){
         var targEls=new Array(
            new Array(document.getElementById("srcPath"),"src"),
            new Array(document.getElementById("destPath"),"dest")
         ),F=document.forms;
         for(var i=0;i<targEls.length;i++)targEls[i][0].innerHTML="<b>"+
            F[0][targEls[i][1]+"DB"].options[F[0][targEls[i][1]+"DB"].selectedIndex].value+"</b>."+
            F[0][targEls[i][1]+"TBL"].options[F[0][targEls[i][1]+"TBL"].selectedIndex].value;
      }
      function disp(targEl){
         var targEl=document.getElementById(targEl);
         targEl.style.display=(targEl.style.display=="none")?"block":"none";
      }
   </script>
</head>
<body>
<div align="center">
   <div style="width:600px;">
      <div style="text-align:center;"><h3>MySQL Database Table Copier</h3></div>
<?php if(!@$_POST){ ?>
      <p style="width:400px;text-align:justify;">Use this utility to copy a table from one database to another. The destination database can be an existing one or new one (created at runtime). You can also mirror the table name or specify an alternate name (in case another table with that name exists in the destination database).</p>
      <form method="POST" action="<?php echo PAGE;?>" onsubmit="
         sdb=this.srcDB.options[this.srcDB.selectedIndex].value;
         ddb=this.destDB.options[this.destDB.selectedIndex].value;
         dtb=this.destTBL.options[this.destTBL.selectedIndex].value;
         if(sdb==ddb){alert('Cannot copy table to the same database!\nChoose a different destination database.');return false;}
         if(ddb==''||dtb==''){alert('Empty destination database or table name received.');return false;}
      ">
         <table width="400" cellpadding="5" cellspacing="0" border="0" align="center" class="bdr">
            <tr>
               <td><b>Source Database</b><br>
                  <select name="srcDB" onchange="
                     // repopulate srcTBL AND destTBL with selected srcDB's tables.
                     var tmp='',db=eval('db_'+this.options[this.selectedIndex].value);
                     for(i=0;i<db.length;i++)tmp+='<option>'+db[i];
                     this.form.srcTBL.innerHTML=tmp;
                     this.form.destTBL.innerHTML=tmp;
                     updPathPrvs();
                  ">
                     <script type="text/javascript">
                        for(i=0;i<DB_NAMES.length;i++)document.write("<option>"+DB_NAMES[i]);
                     </script>
                  </select><br>
               </td>
               <td class="divider"></td>
               <td><b>Destination Database</b><br>
                  <select name="destDB" onchange="updPathPrvs();">
                     <script type="text/javascript">
                        for(i=0;i<DB_NAMES.length;i++)document.write("<option>"+DB_NAMES[i]);
                     </script>
                  </select><input type="button" value="+" onclick="
                     var txt=null;
                     txt=prompt('Enter the name of the database you wish to create:','');
                     if(txt==null||txt=='')return false;
                     newOp=document.createElement('OPTION');
                     this.form.destDB.options.add(newOp,0);
                     this.form.destDB.options[0].text=txt;
                     this.form.destDB.selectedIndex=0;
                     updPathPrvs();
                  " title="create new database">
               </td>
            </tr>
            <tr>
               <td><b>Source Table</b><br>
                  <select name="srcTBL" onchange="
                     // force selected in destTBL to match this' selected table
                     var targ=this.form.destTBL;
                     for(i=0;i<targ.options.length;i++){
                        if(targ.options[i].value==this.options[this.selectedIndex].value){
                           targ.options[i].selected=true;
                           break;
                        }
                     }
                     updPathPrvs();
                  ">
                     <script type="text/javascript">
                        for(i=0;i<DATABASES[0].length;i++)document.write("<option>"+DATABASES[0][i]);
                     </script>
                  </select>
               </td>
               <td class="divider"></td>
               <td><b>Destination Table</b><br>
                  <select name="destTBL" onchange="updPathPrvs();">
                     <script type="text/javascript">
                        for(i=0;i<DATABASES[0].length;i++)document.write("<option>"+DATABASES[0][i]);
                     </script>
                  </select><input type="button" value="+" onclick="
                     var txt=null;
                     txt=prompt('Enter the name of the table you wish to create:','');
                     if(txt==null||txt=='')return false;
                     newOp=document.createElement('OPTION');
                     this.form.destTBL.options.add(newOp,0);
                     this.form.destTBL.options[0].text=txt;
                     this.form.destTBL.selectedIndex=0;
                     updPathPrvs();
                  " title="use alternate table name">
               </td>
            </tr>
            <tr>
               <td><b>Source:</b><br><code id="srcPath"></code></td>
               <td class="divider"></td>
               <td><b>Destination:</b><br><code id="destPath"></code></td>
            </tr>
            <tr>
               <td colspan="3" style="background-color:#ded;">
                  <input type="submit" value="Start">
                  <input type="reset" onclick="window.location='<?php echo PAGE;?>';">
               </td>
            </tr>
         </table>
         <script type="text/javascript">updPathPrvs();</script>
      </form>
<?php }else if(!isset($_POST['delSrcTbl'])){
      $srcDB=$_POST['srcDB'];
      $srcTBL=$_POST['srcTBL'];
      $destDB=$_POST['destDB'];
      $destTBL=$_POST['destTBL'];
      echo 'Checking for existence of destination database <code>'.$destDB.'</code>...<br><code class="qs">';
      echo $qs="SHOW DATABASES LIKE '".$destDB."'";
      $qDestDBexists=mysql_query($qs)or die(mysql_error());
      echo '</code><br>';
      if(mysql_num_rows($qDestDBexists)==0){
         echo 'Database does not exist.<br>';
         echo 'Creating database <code>'.$destDB.'</code>...<br><code class="qs">';
         echo $qs="CREATE DATABASE ".$destDB;
         $qCreateDestDB=mysql_query($qs)or die(mysql_error());
         echo '</code><br>------------ <b>OK</b> ------------<br>';
      }else echo 'Database exists.<br>';
      echo 'Copying table structure from <code>'.$srcDB.'.'.$srcTBL.'</code>...<br><code class="qs">';
      echo $qs="CREATE TABLE ".$destDB.".".$destTBL." LIKE ".$srcDB.".".$srcTBL;
      $qCopyTbl=mysql_query($qs)or die(mysql_error());
      echo '</code><br>------------ <b>OK</b> ------------<br>';
      echo 'Retrieving data from <code>'.$srcDB.'.'.$srcTBL.'</code>...<br><code class="qs">';
      echo $qs="SELECT * FROM ".$srcDB.".".$srcTBL;
      $qSrcData=mysql_query($qs)or die(mysql_error());
      echo '</code><br>------------ <b>OK</b> ------------<br>';
      $srcDataC=mysql_num_rows($qSrcData);
      if($srcDataC==0)echo 'No data in source table (nothing to copy).<br>';
      else{
         $SRC_DATA=array();
         while($row=mysql_fetch_assoc($qSrcData))
            foreach($row as $col=>$val)$SRC_DATA[$row{'id'}][$col]="'".mysql_real_escape_string($val)."'";
         echo 'Populating <code>'.$destDB.'.'.$destTBL.'</code> with source data...<br><code class="qs">';
         foreach($SRC_DATA as $rowID=>$rowData){
            echo $qs="INSERT IGNORE INTO ".$destDB.".".$destTBL." (".implode(",",array_keys($SRC_DATA[$rowID])).
               ") VALUES (".implode(",",$SRC_DATA[$rowID]).")";
            $qIns=mysql_query($qs)or die(mysql_error());
            echo '<br>';
         }
         echo '</code><br>------------ <b>OK</b> ------------<br>';
      }
      echo '<p>============ COPYING COMPLETE ============</p>';
      echo 'Verifying copied data...<br><code class="qs">';
      echo $qs="SELECT * FROM ".$destDB.".".$destTBL;
      $qCopiedData=mysql_query($qs)or die(mysql_error());
      echo '</code><br>';
      $destDataC=mysql_num_rows($qCopiedData);
      echo '   Records in source table: '.$srcDataC.'<br>';
      echo '   Records in destination table: '.$destDataC.'<br>';
      echo (($srcDataC==$destDataC)?'Records counts match.':'<b class="red">Record counts do not match!</b>').'<br>';
      if($destDataC>0){
         echo '<a href="" onclick="disp(\'recData\');return false;">View Record Data »</a>';
         $qs="SHOW COLUMNS FROM ".$destDB.".".$destTBL;
         $qCols=mysql_query($qs)or die(mysql_error());
         if(mysql_num_rows($qCols)==0)die("Unable to retrieve list of columns in <code>".$destDB.".".$destTBL."</code>");
         echo '<div id="recData" style="display:none;"><table cellpadding="3" cellspacing="0" border="1">';
         while($colData=mysql_fetch_assoc($qCols))echo '<th>'.$colData['Field'].'</th>';
         while($row=mysql_fetch_assoc($qCopiedData)){
            echo '<tr>';
            foreach($row as $col=>$val)echo '<td>'.$val.'</td>';
            echo '</tr>';
         }
         echo '</table></div><br><br>';
      }
      ?>
<form method="POST" action="<?php echo PAGE;?>">
   <input type="button" value="Back to Main" onclick="window.location='<?php echo PAGE;?>';">
   <input type="hidden" name="delSrcTbl" value="no">
   <input type="hidden" name="srcDB" value="<?php echo $srcDB;?>">
   <input type="hidden" name="srcTBL" value="<?php echo $srcTBL;?>">
   <input type="button" value="Delete Source Table" onclick="
      if(confirm('Are you sure you want to delete: '+this.form.srcDB.value+'.'+this.form.srcTBL.value+'?')){
         this.form.delSrcTbl.value='yes';
         this.form.submit();
      }
   ">
</form>
      <?php
   }else if(isset($_POST['delSrcTbl']) && $_POST['delSrcTbl']=='yes' && isset($_POST['srcDB']) && isset($_POST['srcTBL'])){
      echo 'Dropping <code>'.$_POST['srcDB'].'.'.$_POST['srcTBL'].'</code>...<br><code class="qs">';
      echo $qs="DROP TABLE ".$_POST['srcDB'].".".$_POST['srcTBL'];
      $qDropSrcTbl=mysql_query($qs)or die(mysql_error());
      echo '</code><br>------------ <b>OK</b> ------------<br>';
      echo '<p>============ ALL TASKS COMPLETE ============</p>';
      echo '<input type="button" value="Back to Main" onclick="window.location=\''.PAGE.'\';"><br><br>';
   }
?>
   </div>
</div>
</body>
</html>