Skip to content

Instantly share code, notes, and snippets.

@kharysharpe
Last active March 20, 2023 08:48
Show Gist options
  • Save kharysharpe/11275211 to your computer and use it in GitHub Desktop.
Save kharysharpe/11275211 to your computer and use it in GitHub Desktop.
Copy a table from one MYSQL database server to another database another
<?php
/**
*
* Code was adapted from SitePoint
* http://www.sitepoint.com/forums/showthread.php?697857-Copy-mysql-table-from-one-server-to-another-through-php&s=b5b25e09ff44749d2e49e0d7c1640fd8&p=4680578&viewfull=1#post4680578
*
*/
// Prevent script from timing out
set_time_limit(0);
// Table to be duplicated
$table = 'table_name';
// Source server of the table to be duplicated
$sourceHost = '127.0.0.1';
$sourceUser = 'your-username';
$sourcePassword = 'your-password';
$sourceDatabase = 'your-db';
// Destination server to duplicate the table
$destinationHost = '127.0.0.2';
$destinationUser = 'your-username';
$destinationPassword = 'your-password';
$destinationDatabase = 'your-db';
// Connect to source server
$source = mysql_connect($sourceHost, $sourceUser, $sourcePassword);
mysql_select_db($sourceDatabase, $source);
// Connect to destination server
$destination = mysql_connect($destinationHost, $destinationUser, $destinationPassword); // connect server 2
mysql_select_db($destinationDatabase, $destination); // select database 2
// Get the table structure from the source and create it on destination server
$tableInfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ", $source)); // get structure from table on server 1
mysql_query(" $tableInfo[1] ", $destination); // use found structure to make table on server 2
// Copy data from source to destination
$result = mysql_query("SELECT * FROM $table ", $source); // select all content
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
mysql_query("INSERT INTO $table (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')", $destination);
}
// Close connections
mysql_close($source);
mysql_close($destination);
@UNES97
Copy link

UNES97 commented Mar 24, 2021

Arigato

@Mario-H
Copy link

Mario-H commented Dec 22, 2021

Hi,

Ran into a problem while copying tables containing reserved names as field names.
Fixed it by adding four back ticks to de destination query:

Changed:

mysql_query("INSERT INTO $table (" . implode(", ", array_keys($row)) . ") VALUES ('" . implode("', '", array_values($row)) . "')", $destination);

To:
mysql_query("INSERT INTO $table (`" . implode("`, `", array_keys($row)) . "`) VALUES ('" . implode("', '", array_values($row)) . "')", $destination);

@avi-think-beyond
Copy link

above program will worked only older version of PHP <5.4

use "mysqli" for latest PHP versions.

@miracuves
Copy link

can this be updated for new php 7.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment