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!

Improve this question
5
  • 1
    Not enough description. What issues have you experienced? Commented Nov 24, 2010 at 2:04
  • I cant read anything of your code, but you should use a real mysql (sql-)dump. Its more reliable. Commented Nov 24, 2010 at 2: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. Commented Nov 24, 2010 at 2: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 Commented Nov 24, 2010 at 5: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? Commented Nov 24, 2010 at 5:02

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 This is probably the best way to do it (but it doesn't use PHP).
@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...
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>";
}
?>
Improve this answer

Comments

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,";}
Improve this answer

1 Comment

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
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.