How to copy all the tables from one database to another database using php?

Question

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.

Answer 1

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);

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);