Copy a MySQL table using PHP.

This is a short guide on how to copy a MySQL table using PHP. In this post, we will be using the PDO object.

Take a look at the following code sample:

//Connect to MySQL using PDO.
$pdo = new PDO($dsn, $user, $password);

//The name of the table that you want to copy.
$currentTable = 'members';

//The name of the new table.
$newTableName = 'members_copy';

//Run the CREATE TABLE new_table LIKE current_table
$pdo->query("CREATE TABLE $newTableName LIKE $currentTable");

//Import the data from the old table into the new table.
$pdo->query("INSERT $newTableName SELECT * FROM $currentTable");

A drill-down of the PHP above:

  1. We connected to MySQL using the PDO object.
  2. The name of the table that we want to copy is called “members”.
  3. The name of the new table that we want to copy our structure and data over to is called “members_copy”.
  4. We copied the structure of our old table by using the SQL statement: CREATE TABLE new_table_name LIKE current_table_name. This creates a MySQL table that is an exact replica of the old table. In other words, any indexes or triggers will also be copied over to the new table structure.
  5. Finally, we imported the data from the old table into the new copied table.

If you do not want to copy the indexes and triggers, then you can use the following SQL statement:

CREATE TABLE new_table_name AS SELECT * FROM current_table_name;

If you want to verify that your new table has been copied over, you can check out my guide on how to list the tables in a MySQL database using PHP and the PDO object.

Hopefully, you found this guide useful.