PHP/MySQL: Copy Table and Data from one Database to another

Question

Anoter question: What is the best way to copy a whole table from one Database without using something like this:

CREATE TABLE DB2.USER SELECT * FROM DB1.USER;

This does not work because I can't use data from two Databases at the same time. So I decided to make this with php. There I had to cache all Data and then I'd create a table in the other DB.

But now - what would be the fastest way to cache the data? I guess there are closely everytime less than 1000 records per table.

Thanks for your input

Answer 1

Export it to a .sql file and import to the new database.

In phpMyAdmin click the database you want to export, click export, check save as file, then go.

Then upload it to the new database that you want to duplicate the data in.

---

In a strict PHP sense, the only way I could think to do it would to be use of SHOW TABLES and describe {$table} to return the all the field names and structures of the records, parse that out, create your create table statements, then loop through each table and create insert statements.

I'm afraid the best I can do for you is a sort of prototype code that I would imagine would be incredibly server intensive, which is why I recommend you go an alternate route.

Something like:

<?php

    // connect to first DB

    $tables = mysql_query("SHOW TABLES") or die(mysql_error());
    while ($row = mysql_fetch_assoc($tables)) {
        foreach($row as $value) {
            $aTables[] = $value;
        }
    }
    $i = 0;
    foreach ($aTables as $table) {
        $desc = mysql_query("describe " . $table);
        while ($row = mysql_fetch_assoc($desc)) {
            $aFields[$i][] = array($row["Field"],$row["Type"],$row["Null"],$row["Key"],$row["Default"],$row["Extra"]);
        }
        $i++;
    }

    // connect to second DB

    for ($i = 0; $i < count($aTables); $i++) {

        // Loop through tables, fields, and rows for create table/insert statements

        $query = 'CREATE TABLE IF NOT EXISTS {$aTables[$i]} (
                //loop through fields {

                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    etc...
                }
            )';

            //loop through data

            $query .= 'INSERT INTO {$aTables[$i]} VALUES';
            $result = mysql_query("SELECT * FROM " . $aTables[$i]);
            while ($row = mysql_fetch_assoc($result)) {
                    $query .= '(';
                foreach ($aFields[$i][0] as $field) {

                    $query .= '"{$row[$field]}",';
                }
                $query .= '),';
            }
        mysql_query($query);
    }

?> 

This is based off of this script which may come in handy for reference.

Hopefully that's something to get you started, but I would suggest you look for a non PHP alternative.

Answer 2

This is the way I do it, not original with me:

http://homepage.mac.com/kelleherk/iblog/C711669388/E2080464668/index.html

Answer 3

$servername = "localhost";
$username = "root";
$password = "*******";
$dbname = "dbname";
$sqlfile = "/path/backupsqlfile.sql";

$command='mysql -h' .$servername .' -u' .$username .' -p' .$password .' ' .$dbname .' < ' .$sqlfile;
exec($command);