Copy of MySQL Tables in PHP

Introduction

If you are working with tables that are running on a server at your business or school, it is usually a good idea to create a copy of some or all of the table before you do any testing or for a backup. We often must copy the data of a table or copy the total table structure with data. We can selectively copy the data of a MySQL table to a new table or copy the total data to a new table. To do that, you can use the "CREATE TABLE AS" statement  with the select statement.

For example

CREATE TABLE table_name AS select_statement

When you use this pattern, the result set that is defined by the select_statement is copied into a new table. When you use this pattern to create tables, MySQL only copies the column definition and data, In other words, MySQL does not retain other parts of column definitions and data such as primary key, foreign keys etc. So if you are copying table data using this pattern, then only the column definition and data are copied. The definition of primary keys, foreign keys, indexes and so on are not included in the new table.

The following table shows the description of the employee table. You can see the "emp_id" is defined as a primary key.



and when you create a copy of the employee table as a "newemp" table name, you will see this table description, it does not include the primary key with the "emp_id" column.



Drop a table in MySQL

You can use the drop table statement to delete a table from the current database. The following query image shows how to delete a table from the database:



Example copy of table in PHP

The following example shows how to use the CREATE TABLE AS statement. The following example query "CREATE TABLE newemp as SELECT * FROM employee" is copies all the columns from all the rows in the employee table into a new table named newemp table.

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
 
print "<h2>MySQL: Data of employee table</h2>";
mysql_select_db("mysql", $con);
$result = mysql_query("SELECT * FROM employee");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>EmpName</th>
<th>EmpSalary</th>
</tr>";
 
while($rowval = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $rowval['emp_id'] . "</td>";
echo "<td>" . $rowval['emp_name'] . "</td>";
echo "<td>" . $rowval['emp_salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
print "<h2>MySQL: Query of copy of table</h2>";
echo "Query=CREATE TABLE newemp as SELECT * FROM employee;";
$result = mysql_query("CREATE TABLE newemp as SELECT * FROM employee");
mysql_query($result,$con);
print "<h2>newemp table has been created.</h2>";
print "<h2>MySQL:Data of newemp table</h2>";
$result = mysql_query("SELECT * FROM newemp");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>EmpName</th>
<th>EmpSalary</th>
</tr>";
while($rowval = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $rowval['emp_id'] . "</td>";
echo "<td>" . $rowval['emp_name'] . "</td>";
echo "<td>" . $rowval['emp_salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>

Output