TechnoPHP

Copy one mysql database table to another using PHP

You can copy one table to another using MYSQL query and PHP script. if you are working on PHP project and you want to copy a table data to another using MYSQL and PHP then you have done it by myself insert and select query. Think about the update operation in php. How we can get the data from the database using id. If we click id 1 that shown us id 1 data and the same for all id values like 1,2,3....etc. Now we will get the data using id and insert (copy ) into another table. You can get data by id wise. This will help you select an option in your web application. To copy one MySQL table to another table you have to go through the MYSQL syntax.


MYSQL copy one table to another SYNTAX



INSERT INTO table 2 
SELECT * FROM  table 1  WHERE ...

Using the above syntax we will copy one table data to another table. First of all, we will select the data from table 1 and insert into table 2. It will be done by the PHP script.

Now we will create a PHP script for copy one table data to another using id. As you know that how to delete data from a table using id. We use the id and delete one by one row. If you want to copy and id to another table. You have to follow delete or update operation. In the delete or update operation, we get the data by id and perform delete and update operation. We will do the same thing here. We will get the data by id and insert into another table. That is a simple process of copy and get table data by id .

Why need copy data by id?
If you are working on a PHP project like contest, winner and same like these projects and you want to select one winner. This is done by the id. We create a database table name "winner and we will get the old table data by id and insert (copy) to MySQL winner table. This will help you to select the winner from the registered competitors (or according to your project etc ).

Copy one mysql table data to another mysql table using id

1. First of all create a table table 1 . (like competitors )

2. Now create an another table with same table field (same as competitors table fields )

3. Create a connection .

4. Select data by id . like -->



<a href=\"winner.php?id=$row[id]\" <button type='submit' >Select Winner </button > </a >"; //First review the update or delele operation by id


Update operation

Delete operation

5. We got the data by id and insert (copy) into another mysql table winner .

Copy one table to another usind id



<?php


$databaseHost = 'localhost';   //your db host 
$databaseName = 'contest';  //your db name 
$databaseUsername = 'root';    //your db username 
$databasePassword = '';//   db password 

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 
 
$id = $_GET['id'];



$sql="select * from competitors where (id='$id');";//  check id is already copied 

      $res=mysqli_query($mysqli,$sql);

      if (mysqli_num_rows($res) > 0) {
        // output data of each row
        $row = mysqli_fetch_assoc($res);
        if($id==$row['id'])
        {
echo "Already copied"; //error message if already copied 
            	
        }

       } else{

 
   
$query=mysqli_query($mysqli,"INSERT INTO winner
SELECT * FROM  competitors WHERE id =$id");// copy one table to another 

echo "Sucessfully copied"; 


	   }
	   
?>

In above example, we can copy the data to another table in on click. You can click one button and you will see the id data copied to another MySQL database.


$query=mysqli_query($mysqli,"INSERT INTO winner
SELECT * FROM  competitors WHERE id =$id");// copy one table to another 
In the part of PHP code, we have used MySQL query. We selected the table(competitors) data by id and copy into another table(winner).

You can modify this code according to your need like copy one MySQL table to another MySQL table. Just go through this code...