How to copy data from one table to another new table in MySQL using PHP

In this article, we will discuss how to copy data from one table to another in the MYSQL database using PHP.

For reference, we are using the database(Codespeedy) which contains 3 tables from which we want to copy the table basetable_codespeedy,

PHP copy table in MYSQL

How to copy table using MySQL queries in PHP?

There are many numbers of query available in MySQL which can copy a table using one or more than one query. I like to cover two simple queries to copy the table.

The first method involves creating a blank table with the table structure from the old table i.e. basetable_codespeedy and then copy the data using the select method from the old table (basetable_codespeedy) to the new table.  The script is written as follows,




<?php 
  
//creating connection to database
$con=mysqli_connect("localhost","root","","codespeedy") or die(mysqli_error());

//create a empty table with the column structure of basetable_codespeedy	
$create_qry="CREATE TABLE derivedtable_codespeedy LIKE basetable_codespeedy;";
mysqli_query($con,$create_qry);

//query to copy table with all its  data
$copy_qry="INSERT derivedtable_codespeedy SELECT * FROM basetable_codespeedy;";
mysqli_query($con,$copy_qry);

?>

Output:-

PHP copy table from one table to another in MYSQL

Lets us simplify the query with creating and copying the data to a new table at once as follows,

<?php 

//creating connection to database
$con=mysqli_connect("localhost","root","","codespeedy") or die(mysqli_error());

//query to copy table with all its index and data
$qry="CREATE TABLE derivedtable_codespeedy SELECT * FROM basetable_codespeedy;";
mysqli_query($con,$qry);

?>

Output:-

final output PHP copy table in MYSQL

In this way, we can able to copy the data of one table to another using PHP. If you have any doubts, put a comment below.

See also,


Leave a Reply

Your email address will not be published. Required fields are marked *