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.

First thing to recommend is to not use the `mysql_` api calls. — Nigel Ren, Jun 01 '18 at 18:48
Not something I've ever tried and I would recommend checking with an expert if it's advisable to use, but `CREATE TABLE ... SELECT` (https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html) — Nigel Ren, Jun 01 '18 at 18:50
I would not use PHP but mySQL directly, simply connect to the server and do the following: mysqldump -u dump.sql then to restore it: mysql -u -p second_db_name < dump.sql — Cedric Guindon, Jun 01 '18 at 18:54
I am giving this for a client.They dont know to code.So the process should take place when they click on a button — Nikeshh Vijayabaskaran, Jun 01 '18 at 18:55
@NigelRen there are issues with that. I use it frequently to make a quick backup of the data in a table just before I make any type of adhoc change to production data. It doesn't include keys or constraints or things that might be important. — gview, Jun 01 '18 at 18:56
Copying a DB on a button click might give you timeouts, make sure to also read on DB lock since you'll be doing a heavy DB query and being on the site at the same time. — Cedric Guindon, Jun 01 '18 at 18:58
How to truncate the whole database and create the necessary tables and copy the values?What will be the sql query for this? — Nikeshh Vijayabaskaran, Jun 01 '18 at 19:00

mdeora · Accepted Answer · 2018-06-01T20:05:35.837

4

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

edited Jun 01 '18 at 20:05

answered Jun 01 '18 at 19:11

mdeora

4,152
2
19
29

Does this code target only one table or entire table in the database? – Nikeshh Vijayabaskaran Jun 01 '18 at 19:18
I have given code for single table with a simple loop it can be done for all tables. – mdeora Jun 01 '18 at 19:19
Can you implement for the entire tables?$table='tabletest'; This field is given manually.How to assign it automatically – Nikeshh Vijayabaskaran Jun 01 '18 at 19:23
see updated code for copying all tables, off course you need to make changes per your use case. – mdeora Jun 01 '18 at 19:30
Will this create newer tables in the second database dynamically or Should i create the tables before itself? – Nikeshh Vijayabaskaran Jun 01 '18 at 19:34
You should create the tables prior to running the script. You must try the code locally first before using it in your environment. – mdeora Jun 01 '18 at 19:37
This code uses mysql extension.Can you rewrite it in mysqli extension because mysql extension is deprecated – Nikeshh Vijayabaskaran Jun 01 '18 at 19:41
I guess that much you can do on your own. – mdeora Jun 01 '18 at 19:44
added mysqli version too. – mdeora Jun 01 '18 at 19:53
I am trying to use the mysqli verision above. However, the table I am trying to copy contains apostrophes in some of the field values causing the script to halt and error: "right syntax to use near ' xxxxx ". The creators of the table did not escape the values when inserting I guess. Is there a way to correct this? – WGS Nov 07 '18 at 12:46

score 1 · Answer 2 · answered Jun 01 '18 at 19:16

In my opinion, this is not the way to accomplish this. The way to accomplish an entire database is to script it using the mysqldump command. To backup an entire database you would do something along the lines of:

mysqldump --databases yourdb --password=pw | mysql -u user --password=pw otherdb

There are various options and settings you might need or want, covered in some detail in this blog post. For example this works just as well copying databases between mysql servers if you add in the -h parameter and host details for the remote host to the command line mysql portion of the command.

Once scripted, it is simple enough to run this type of script from PHP using system or exec.

user1495495 · Answer 3 · 2020-04-06T20:27:33.623

Below is code to migrate data betwee two databases it checks if table or column exist othewise create it and insert or udpate data Note it is not suitable solution for large database and make sure you bakup for you target database.

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

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

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

$result = (mysqli_query($dblink1,"SHOW TABLES  "));
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}
echo "Searching Table: ".$tables[0] ."<br>";
foreach ($tables as $i => $table){

    $val = mysqli_query($dblink2,"select 1 from $table  ");

    if($val !== FALSE) /***** table exists */
    {
        $structure1= get_colums($dblink1, $table);
        $structure2= get_colums($dblink2, $table);
        compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );


        $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
        echo $table." Updating table.... "."<br>";
        $rowcount=mysqli_num_rows($result); 
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
            $update_string = update_query_format($row );

           mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."') 
           ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
        }
        echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>"; 
    }
    else
    {
        /**** table not exists */
       echo $table." Table not found "."<br>";
       echo $table." Creating table... "."<br>";
       $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        
       echo $table." Copying table.... "."<br><br><br>"; 
       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);


 function compare_structure($table, $dblink1 , $dblink2){
            /***  Check if table exists */
            $val = mysqli_query($dblink2,"select 1 from $table  ");

            if($val !== FALSE) /***** table exists */
            {
                $structure1= get_colums($dblink1, $table);
                $structure2= get_colums($dblink2, $table);
                compare_colums($dblink1,$dblink2, $structure1, $structure2, $table );


                $result = mysqli_query($dblink1,"SELECT * FROM $table  "); // select all content        
                echo $table." Updating table.... "."<br>";
                $rowcount=mysqli_num_rows($result); 
                while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {     
                    $update_string = update_query_format($row );

                   mysqli_query($dblink2,"INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."') 
                   ON DUPLICATE KEY UPDATE $update_string "); // insert one row into new table
                }
                echo $table." Updating table Completed.... total updated: $rowcount"."<br><br><br>"; 
            }
            else
            {
                /**** table not exists */
               echo $table." Table not found "."<br>";
               echo $table." Creating table... "."<br>";
               $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        
               echo $table." Copying table.... "."<br><br><br>"; 
               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
               }
            }

}

function get_colums($dblink, $table){
    $result = mysqli_query($dblink,"SHOW COLUMNS FROM $table ");
    $columns = [];           
    if (!$result) {
        echo 'Could not run query: ' . mysql_error();
        return [];
    }
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $columns[$row['Field']] = $row['Type'];
        }
    }

    return $columns;

}

function compare_colums( $dblink1, $dblink2,  $structure1, $structure2, $table ){

    foreach ($structure2 as $field => $type){
        if (!array_key_exists($field,$structure2)){

            $result = mysqli_query($dblink2,"ALTER TABLE cus_tbl  ADD $field $type NOT NULL ");  
            if ($result){
                echo "Table: ". $table." New Field created ".$field. " ". $type. " .... "."<br>"; 
            }
        }

    }

}

function update_query_format($row){

    $query_format = "";
    foreach ($row as $k => $v){
        $query_format .= " ".$k. " = '". $v ."',"; 
    }
   // echo strlen($query_format);exit;
   // echo substr($query_format, 0,strlen($query_format)-1); exit;
    return substr($query_format, 0, strlen($query_format)-1);

}

walugembe jamal umal · Answer 4 · 2023-04-25T18:47:30.703

                 ///////////////////////////////////////////COPY DATABSE STRUCTURE TO NEW DATABASE/////////////////////////////////////////////////////////

                            /*After CREATING CONNECTIONS TO THE TWO DATABASES
                            AND THE TWO DATABASES MUST BE AT THE SAME SAVER*/

                            $old_database=$YOUR_FROM_DATABASE;
                            $new_dataBase=$DESTINATION_DATABASE;

                            $list = mysql_list_tables ("$old_database"); 
                            $i = 0;

                            while ($i < mysql_num_rows ($list)) {

                                $tb_names[$i] = mysql_tablename ($list, $i);  

                                $createTables="CREATE TABLE $new_dataBase.".$tb_names[$i]." LIKE $old_database.".$tb_names[$i];
                                $createTablesQry=mysql_query($createTables);

                                $i++;

                            }//clossing while ($i < mysql_num_rows ($list)) {

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Apr 26 '23 at 23:53

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

4 Answers4