Using PHP to copy tables from one Mysql DB to another

Hey Sitepoint!

I’m trying to write a script that will copy the contents of my development domain over to the live site, or vice versa. Part of this script will also need to copy Mysql tables from one database to another. I have tried doing this manually with PHP (by copying all of the contents of the tables into a PHP array but it runs out of memory very quickly), so I was trying to find a better way of doing this and came across this: http://www.edmondscommerce.co.uk/mysql/mysql-copy-table-from-one-database-to-another/

DROP TABLE IF EXISTS `backup_db.backup_table`;
CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`

This seems really easy, however I don’t quite understand how this command can run as PHP will only be logged into one database at a time and the dev database, and the live database have different user/passwords.

Should I just run the SQL command using the system() function?

All you need to do is use the standard MySQL functions that PHP has built in, see the below examples:

MySQL

// Create a new MySQL database connection
if (!$con = mysql_connect('localhost', $username, $password)) {
    die('An error occurred while connecting to the MySQL server!<br><br>' . mysql_error());
}

if (!mysql_select_db($database)) {
    die('An error occurred while connecting to the database!<br><br>' . mysql_error());
}

// Create an array of MySQL queries to run
$sql = array(
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
);

// Run the MySQL queries
if (sizeof($sql) > 0) {
    foreach ($sql as $query) {
        if (!mysql_query($query)) {
            die('A MySQL error has occurred!<br><br>' . mysql_error());
        }
    }
}

mysql_close($con);

MySQLi

// Create a new MySQL database connection
if (!$con = new mysqli('localhost', $username, $password, $database)) {
    die('An error occurred while connecting to the MySQL server!<br><br>' . $con->connect_error);
}

// Create an array of MySQL queries to run
$sql = array(
    'DROP TABLE IF EXISTS `backup_db.backup_table`;',
    'CREATE TABLE `backup_db.backup_table` SELECT * FROM `live_db.live_table`'
);

// Run the MySQL queries
if (sizeof($sql) > 0) {
    foreach ($sql as $query) {
        if (!$con->query($query)) {
            die('A MySQL error has occurred!<br><br>' . $con->error);
        }
    }
}

$con->close();

I only see you logging into one Database… How will this connect to two different databases?

The article you linked to assumes that both databases have access via the same username and password, because of this the above example assumes that as well. The only other way to transfer the database values from one db to another would be to manually pull down all the data and store it in a buffer until your ready with the second database, of course this will cause a huge problem with the memory limit but it would be the only other way i can think of to transfer the data.

Yes, that is what I figured from the beginning, but I thought I’d ask and see if there was another way. Thank you for clarifying.

Are both the source and destination database on the same server? Assuming that the structure of the tables is the same in both databases you could after having got a list of the tables, loop through the tables, fdor each table using a “select insert” query to copy the data across

Yes, both source and destination databases are on the same Mysql Server, but they required different usernames and logins. Could you give an example of how I might pull off what you suggested? I’d really appreciate that.

Does one of the to users have rights to do SELECT queries on the tables in the source database and INSERT queries in the destination database? If so have PHP use that user to copy the data across, but running the query against the destination database.

Thread moved to the Databases forum

Hi wh335,

One way is to create two different database instances; one using database A’s credentials and one using database B’s Credentials.

So here is how to get both connections using PDO:


<?php
class DBOne {
   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'testdb';
     $user = 'db_user1' ;    $password = '' ;
     $host = 'localhost' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}
class DBTWO {
   static $db ;
   private $dbh ;
   private function PDO_DBConnect(){
     $db_type = 'mysql'; 
     $db_name = 'testdb2';
     $user = 'db_user2' ;    $password = '' ;
     $host = 'localhost' ; 
    try {
        $dsn = "$db_type:host=$host;dbname=$db_name"; 
        $this->dbh = new PDO ( $dsn, $user, $password); 
        $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage () . "\
" ;      die () ; 
     }  
   }
   public static function getInstance ( ) {
     if (! isset ( PDO_DBConnect::$db )) {
        PDO_DBConnect::$db = new PDO_DBConnect ( ) ;
     }
     return PDO_DBConnect::$db->dbh;
  }
}

 

You would set each connection by doing this:


$o_DbA = DBOne::getInstance(); //Get Database A
$o_DbB = DBTwo::getInstance(); // Get Database B
$sql = 'SELECT...';
$stmt = $o_DBA->prepare($sql);
$stmt->execute();
$db_A_results = $stmt->fetch(PDO::FETCH_ASSOC);
foreach($db_A_results as $results){
  foreach($result as $key => $value){
    $stmt = $o_DbB ->prepare("INSERT INTO sometable (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name', $key);
    $stmt->bindParam(':value', $value);
    $stmt->execute();
  }
}

Now you can use both connections to SELECT, UPDATE, INSERT AND DELETE data from the same application.

A different idea, hope this helps;

Steve

That’s an interesting way of looking at the problem. Unfortunately that code is a bit beyond my understanding.

I’ve managed to up the memory limit and time limit for the script to execute and I think I can do it by switching connections (kind of like your set up) between the databases. I’m just curious if there is an easy PHP Sql function to do this.

Normally I would do something like “select * from some table” and I would use while loop like

$query = 'select * from some_table";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
  $sql_table_data[] = $row;
}

Then, once I have that array with the table data I would do something like


connect_to_other_database();

foreach ($sql_table_data as $row)
{
  $query = 'insert into some_table set SOME_COLUMN_NAME = "' . $row['SOME_COLUMN_NAME'] . ', SOME_COLUMN_NAME2 = "' . $row['SOME_COLUMN_NAME2'] . '";

mysql_query($query);
}

But I’m thinking there is probably a way I can select a row of data and insert it with out having to specify each column name on the insert. Would you happen to know if this is possible?

yes, it is

provide a VALUES value for every column, and abandon the SET version of the INSERT statement

this has the added advantage that you can also supply values for multiple rows at a time, say in batches of 50 or 100 rows, to improve efficiency over calling the target database one row at a time

which raises the question… have you thought of dumping the table from the source database, and executing the load into the target as a command?

Would you mind writing me a small piece of sample code to demonstrate that? That really sounds like something I ought to know by now lol.

I tried to do “select * INTO OUTFILE” and it tells me I don’t have permission. That is probably because I only have access to PHPMYADMIN and I’m not sure how to give a database user login account access to the physical disk to write files. I would normally just do a full dump of the database and pave over the development database but I don’t want to wipe the whole thing out, I just want a select 10/30~ tables. So… here I am.

INSERT
  INTO targettable
VALUES
 (  9, 'asdf', 'this is a row' )
,( 37, 'qwer', 'this is another' )
,( 42, 'zxcv', 'this also' )
, ...

as for dumping, i didn’t mean INTO OUTFILE, i meant mysqldump command

Thank you very much for the example code. I think I get it now. How many rows can I insert into one SQL insert like that? You mentioned 50-100 but is there an actual limit I should respect?

As for mysqldump, I presumed it wasn’t possible to just dump just the tables I wanted. I also didn’t think it was possible to dump the tables into an array that I would be able to iterate through.

i was thinking that you would dump to a text file, then just load the text file into the target database

I could do that, but I was trying to make it so I could perform this database update simply by clicking a button on a password protected web page.

what is the difference between clicking a button to execute a php routine that copies one table to the other a few rows at a time, versus clicking a button to execute a php routine that runs a dump and upload?

Mostly my limited knowledge of Linux. When I try to do things from the command line (which I presume is what your talking about) quite often I run into permission errors because the user account that runs PHP on the VPS I use doesn’t have access to various directories and such. But if it’s possible to do all of this just through PHP then I’m game! At any rate, I think I got it figured out now with your examples from above, so thank you :smiley: