如何使用php将所有表从一个数据库复制到另一个数据库？ [英] How to copy all the tables from one database to another database using php?

问题描述

我需要以编程方式将一个表的所有值从一个数据库复制到另一个数据库。我非常喜欢使用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);

这段代码可以正常工作，但是问题是我的数据库中有大约50个表。一种截断第二个数据库并创建表并从第一个数据库复制值的方法？

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?

我知道phpmyadmin中有一个选项。但是我想以编程方式执行此操作。
我想实现这一点，因为我正在创建一个销售点系统，该销售点系统位于本地主机中，并且可以在网站上在线查看分析我需要定期将表及其数据复制到在线数据库中。

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 -

 <?php
$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);

下面是mysqli版本-

BELOW is the mysqli version -

<?php
$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);

这篇关于如何使用php将所有表从一个数据库复制到另一个数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！