How to Create a MySQL Table Structure in a different database using PHP

Question

I have 2 separate and different databases:

• SOURCE_DATABASE
• TARGET_DATABASE

I am trying to copy a table from SOURCE_DATABASE to TARGET_DATABASE using PHP and not phpMyAdmin (as it works fine in phpMyAdmin).

I have the following php:

$linkSource = mysql_connect( SERVER, SOURCE_USERNAME, SOURCE_PASSWORD );
mysql_select_db( SOURCE_DATABASE, $linkSource );
$linkTarget = mysql_connect( SERVER, TARGET_USERNAME, TARGET_PASSWORD );
mysql_select_db( TARGET_DATABASE, $linkTarget );

mysql_query( 'CREATE TABLE `targetDB.targetTable` LIKE `sourceDB.sourceTable` ) or die( mysql_error() );

Is it possible to create a table in a 2nd database (target) using the structure of a table in a 1st database (source)?

Answer 1

You could use MySQL's "SHOW CREATE TABLE" function to get the structure of the source table, and then execute the CREATE TABLE statement it gives you in the target connection.

$result = mysql_query("SHOW CREATE TABLE sourceTable", $linkSource);
$create_table = mysql_result($result, 0, 1);
mysql_query($create_table, $linkTarget);

Answer 2

PhpMyAdmin and PHP is wrong tools for this purposes. Use command line shell. Something like this:

mysqldump -u root -p database1 > database1.sql

mysql -u root -p database < database1.sql

It will work in 10 times more faster, I'm guarantee it. Also, database will take care about data consistency instead of you.

If you are realy want to do it in PHP, use php command line shell.

If you are still want to do it in PHP WITHOUT command line shell, I can suggest to do this kind of trick:

$query = "show tables from source_database";
$tables = $dbSource->getColumn($query)//get one column from database
foreach($tables as $tableName) {
  $query = "show create table ".$tableName;//← it will return query to clone table structure
  $createTableSchema = $dbSource->getRow($query);//← get assoc array of one row
  $dbTarget->query($createTableSchema['Create Table']);//← here is your silver bullet
}

PS and also, when (if) you will copy data from one table to another table, you should know that statement

insert into table () values (values1), (values2), (values3);

much more faster than

insert into table () values (values1);
insert into table () values (values2);
insert into table () values (values3);

But insert multy rows is related to max_allowed_packet propery field. In case, when your query will more large than this field (yes, query string can allocate 1 gb of memory), MySQL will throw exception. So, to build Insert multy query you should get max_allowed_packet and generate this query according to this size. Also to speed up performance you can disable keys from target table. (do not forgot to enable keys, lol)