php mysql 复制数据库表结构图,如何使用php将所有表从一个数据库复制到另一个数据库？...

I need to copy all the values of a table programatically from one database to another.I highly prefer using php. How do i achieve this?

I found a particular code:

$sql1 = "DELETE FROM Kunthanahali.justshawarma_aauth_groups;";

$result1 = $conn->query($sql1);

$sql2 = "INSERT INTO Kunthanahali.justshawarma_aauth_groups SELECT * FROM justshawarmapos.justshawarma_aauth_groups;";

$result2 = $conn->query($sql2);

This code is working fine.But the problem is I have around 50 tables in my database.Is there a way to truncate the second database and create tables and copy values from the first database?

I know there is a option inside phpmyadmin. But i want to do this programatically.

I want to achieve this because i am creating an point of sale system where the point of sale system is present in the localhost and the analytics is viewed online in a website.I need to copy the tables and its data periodically to the online database.

解决方案

You can achieve this using the below code -

$dblink1=mysql_connect('$ip1', '$user1', '$pass1'); // connect server 1

mysql_select_db('$database1',$dblink1); // select database 1

$dblink2=mysql_connect('$ip2', '$user2', '$pass2'); // connect server 2

mysql_select_db('$database2',$dblink2); // select database 2

$tables = mysql_fetch_array(mysql_query("SHOW TABLES ",$dblink1));

//$table='tabletest';

foreach($tables as $table){

$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ",$dblink1)); // get structure from table on server 1

mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2

$result = mysql_query("SELECT * FROM $table ",$dblink1); // 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))."')",$dblink2); // insert one row into new table

}

}

mysql_close($dblink1);

mysql_close($dblink2);

BELOW is the mysqli version -

$dblink1=mysqli_connect('127.0.0.1', 'root', ''); // connect server 1

mysqli_select_db($dblink1,'pdb1'); // select database 1

$dblink2=mysqli_connect('127.0.0.1', 'root', ''); // connect server 2

mysqli_select_db($dblink2,'pdb4'); // select database 2

$tables = mysqli_fetch_array(mysqli_query($dblink1,"SHOW TABLES "));

//$table='tabletest';

foreach($tables as $table){

$tableinfo = mysqli_fetch_array(mysqli_query($dblink1,"SHOW CREATE TABLE $table ")); // get structure from table on server 1

mysqli_query($dblink2," $tableinfo[1] "); // use found structure to make table on server 2

$result = mysqli_query($dblink1,"SELECT * FROM $table "); // select all content

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {

mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')"); // insert one row into new table

}

}

mysqli_close($dblink1);

mysqli_close($dblink2);