create a table with php and mysql using SHOW CREATE TABLE

Question

I'm trying to create an exact duplicate of another table via php by using SHOW CREATE TABLE.

I only want to change the table name, but I haven't figured out how to do that yet. If the old table's name is table_1, I want the new one to be table_2.

This didn't work. Didn't really expect it to, but that's how far I got:

 $t = $DB->fetch("SHOW CREATE TABLE table_1");
    $t[0] = "table_2";
    $DB->query($t[1]);

Zohaib · Accepted Answer · 2011-11-24 05:00:28Z

2

The following query will create new table, column attributes and indexes will also be copied.

CREATE TABLE new_table_name LIKE old_table_name;

And if you also want the rows copied, then execute following query after executing the above

INSERT INTO new_table_name SELECT * FROM `old_table_name`;

http://www.mysqlfaqs.net/mysql-faqs/Data-Back-Up/How-to-create-duplicate-table-in-MySQL

http://www.tech-recipes.com/rx/1487/copy-an-existing-mysql-table-to-a-new-table/

answered Nov 24, 2011 at 5:00

Zohaib

7,1263 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Hunt Over a year ago

Make sure you don't do this, you'll lose auto_increments, primary keys etc.. CREATE TABLE t2 SELECT * FROM t1;

domino Over a year ago

@John, I just need a table with the same structure. No data. But thanks for another method.

Akhil Thayyil · Accepted Answer · 2011-11-24 05:07:11Z

0

<?php
$result=mysql_query("SHOW TABLES");
$oldTableName="TABLE1";
$found=false;
while($row=mysql_fetch_array($result))
{
    if($row[0]==$oldTableName)
        {
            $found=true;
            break;
        }
}

if($found)
    {
        //TODO Code for table1
    }
else
{
        //TODO Code for table2
}



?>

answered Nov 24, 2011 at 5:07

Akhil Thayyil

9,4256 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

create a table with php and mysql using SHOW CREATE TABLE

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related