How to Copy a Table Definition In MySQL Using PHP

In this article, we show how to copy a table definition in MySQL using PHP.

By copying the defintion of a table, we are copying all the columns of a table and the definition and data types that these columns are made of. We are not copying the contents in the rows of the table. We are just copying the same structure that the table is made of.

So if a table named Users has an ID int(11) column, first name VARCHAR(50) column, last name VARCHAR(50) column, the table we create will be composed of the exact same columns with the exact same definitions. Again, none of the inserted rows of the table are copied over, just the column defintions. So even if the table you are copying has 12 rows of users, none of this data will be transferred to the new table we create. We are simply copying the definition or structure of the table.

This has good application if you simply want to repeat a table but maybe use this table for another purpose. For example, you have a table composed of toys for boys and now you want to create the same structure table but this as a table for toys for girls.

So the MySQL code to copy a table definition is shown below.

CREATE TABLE table2 AS SELECT * FROM table1 WHERE 1=0";

So the above is the MySQL code to create a table, table2, which copies the structure of table 1 (has all the same structured columns).

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to copy a table definition is shown below.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $copytabledefinition= "CREATE TABLE table2 AS SELECT * FROM table1 WHERE 1=0"; $create= mysql_query($copytabledefinition, $connection); if ($create) { echo "The table has been successfully copied."; } else { echo "The table has not been copied"; } ?>

So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $copytabledefinition that creates a table named table 2, which copies the structure of table 1. So now a new table, table2, is created that has identicial columns as table 1.

We make a variable called $create, which runs a $copytabledefinition query.

We then execute the query through the if statement.

If the $create variable returns true, the table has been successfully copied. If not, the table has not been.

This is all that is required to copy a table definition or copy the structure of MySQL table using PHP.

Related Resources

How to Create a MySQL Table Using PHP

How to Select a Table from a MySQL Database

How to Check if a MySQL Table Exists Using PHP

How to Show All Tables of a MySQL Database Using PHP

How to Delete a MySQL Table Using PHP

How to Modify the Defintion or Data Type of a Column of a MySQL Table Using PHP

How to Insert Data into a MySQL Table Using PHP

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP

How to Insert Data Into a Table of a MySQL Database From an HTML Form Using PHP

How to Update a Value in a MySQL Table Using PHP