3

Hi I have write a code that can copy database table from one sever to another but the record of each table did not copy how to write a function that can copy tables and each record from one db server to another?

here's my sample code:

<?php
$dbNewDB = 'newdb';
$dbNewUser = 'newroot';
$dbNewUserPswd = 'newpass';

$dbConnect = mysql_connect('localhost', 'root', 'mypassword') or die('Couldn\'t connect to MySql:'.mysql_error());
$dbNewConnect = mysql_connect('localhost', $dbNewUser, $dbNewUserPswd) or die('Couldn\'t connect to MySql:'.mysql_error());

$sqlStatement = "SHOW TABLES FROM olddb";
$result = mysql_query($sqlStatement,$dbConnect) or die('Unable to get tables: '.mysql_error());
while($row = mysql_fetch_row($result)) 
    {
        //Drop table if exist
        $sqlStatement = "DROP TABLE IF EXISTS " . $dbNewDB . "." . $row[0];
        mysql_query($sqlStatement,$dbNewConnect) or die("Failed to delete: " . mysql_error());
        //Create new table
        $sqlStatement = "CREATE TABLE " . $dbNewDB . "." . $row[0] . " LIKE olddb." . $row[0];
        echo "$sqlStatement [" . __METHOD__ . "]"; 
        mysql_query($sqlStatement,$dbNewConnect)or die("Failed to create: ". mysql_error());
        //Insert data
        $sqlStatement = "INSERT INTO " . $dbNewDB . "." . $row[0] . " SELECT * FROM " . $dbNewDB . "." . $row[0];
        echo "$sqlStatement [" . __METHOD__ . "]"; 
        mysql_query($sqlStatement,$dbNewConnect)or die("Table copy failed: ".mysql_error());
        echo "$row[0] copy done. [" . __METHOD__ . "]"; 
    }

mysql_free_result($result);
mysql_close($dbConnect);
mysql_close($dbNewConnect);

?>

my code is already functional All i want to fixed to copy the records of each tables. Any idea?or help?

Thank you!

enobrev
  • 22,314
  • 7
  • 42
  • 53
devzone
  • 305
  • 1
  • 12
  • 25
  • 1
    Not enough description. What issues have you experienced? – zerkms Nov 24 '10 at 02:04
  • I cant read anything of your code, but you should use a real mysql (sql-)dump. Its more reliable. – KingCrunch Nov 24 '10 at 02:05
  • "my code is already functional All i want to fixed" -- if code needs to be fixed - then it is not functional. Working without errors means nothing since your code doesn't follow your expectations. – zerkms Nov 24 '10 at 02:05
  • whats with the use of magic constant __METHOD__? i dont think you are even in a method. also, is "$row[0]" doing what you hope? i know it doesn't with named indexes but maybe it works with numerical. if it doesnt work, use either curly bracket syntax or just put it outside the quotes and concatenate – jon_darkstar Nov 24 '10 at 05:00
  • that being said, i think mysqldump is the way to go, provided you have access to the DB thru terminal (i dont see why you wouldnt). if you insist on the PHP script this looks mostly correct, whats the problem? tables are being created but INSERTs arent working right? – jon_darkstar Nov 24 '10 at 05:02

4 Answers4

5

You can dump the whole database in SQL format like this:

mysqldump --user=root --password=whatever --databases dbtest --opt --quote-names --complete-insert > testbkup.sql

Then you can import it back like this:

mysql -u root -p whatever dbtest < testbkup.sql

(Note: user = root, password = whatever, dbtest is your database.)

Just saying.

Sujith Surendranathan
  • 2,569
  • 17
  • 21
  • +1 This is probably the best way to do it (but it doesn't use PHP). – Ben Nov 24 '10 at 02:45
  • @steve: Y use php? all he wants is replication... @devzone: try sujith's solution or go for db replication? Y getting into PHP coding if there may be a good alternative... Once you configure configuration, then it will be automatically done onwards... – Assad Nazar Nov 24 '10 at 04:55
4

I found these script working, you can try these:

<?php
$connect2 = mysql_connect("localhost", "root", "");
$database1 = "test1"; // destination database
mysql_select_db($database1, $connect2);
set_time_limit(0);

$database = 'dev_loribonn'; //original database
$connect = mysql_connect("localhost", "root", "");

mysql_select_db($database, $connect);

$tables = mysql_query("SHOW TABLES FROM $database");

while ($line = mysql_fetch_row($tables)) {
    $tab = $line[0];
    mysql_query("DROP TABLE IF EXISTS $database1.$tab");
    mysql_query("CREATE TABLE $database1.$tab LIKE $database.$tab") or die(mysql_error());
    mysql_query("INSERT INTO $database1.$tab SELECT * FROM $database.$tab");
    echo "Table: <b>" . $line[0] . " </b>Done<br>";
}
?>
Abu Zafar Md. Wahid
  • 41
  • 1
0

Your insert statement looks off. Trying to insert into $dbNewDB with your values coming from dbNewDB. You have to turn to the old database. Below I am building two stings for the insert. $string1 = '(col1name, col2name...,)' $string2 = '(val1-1, val1-2, ...), (val2-1, val2-2,...), ...' for "INSERT INTO table $string1 VALUES $string2"

    //Insert data 

    $sql2 = "SELECT * FROM " . $row[0];
    $r = mysql_query($sql, $bConnect);
    $string1 = '('; $arr = array();
    while ($irow = mysql_fetch_assoc($r)) {$arr[] = $irow;}
    foreach($irow as $k=>$v)
    {
         $string1 .= "$k,";
    }
    $string1 = substr($string1, 0, -1) //lose last comma
    $string1 .= ')';
    $string2 = array_reduce($f, $arr); 
    $string2 = substr($string2, 0, -1) //lose last comma


    $sqlStatement = "INSERT INTO " . $dbNewDB . "." . $row[0] . " $string1 VALUES $string2";
    echo "$sqlStatement [" . __METHOD__ . "]";  
    mysql_query($sqlStatement,$dbNewConnect)or die("Table copy failed: ".mysql_error()); 
    echo "$row[0] copy done. [" . __METHOD__ . "]";

declare $f elsewhere

$f = function($b, $x) {$a = ' ('; foreach($x as $v) {$a .= "'$v',";} $a = substr($a, 0, -1); $a .= ')'; return "$b $a,";}
jon_darkstar
  • 16,398
  • 7
  • 29
  • 37
  • with my code? theres probably a few, im sketching an idea and i didnt test. but do you see what im going for? if you insist on doing it in PHP, this is the only way i see to perform the inserts. still, theres a number of easier ways to copy this database as others have suggested – jon_darkstar Nov 24 '10 at 06:26
0

You can use sql yog to transfer the tables to different host without writing code in php .

XMen
  • 29,384
  • 41
  • 99
  • 151