3

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)?

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
H. Ferrence
  • 7,906
  • 31
  • 98
  • 161
  • 1
    first: use mysqli not mysql, the mysql have been removed in PHP 7. And do you mean with 2ed database, and are all you want to do is create a table in your database using PHP ? – TheCrazyProfessor Jan 03 '17 at 22:06
  • Thanks @JayBlanchard. I edited the OQ as it is not asked nor answered elsewhere -- I looked over and over for a similar Q... – H. Ferrence Jan 04 '17 at 00:22
  • Thanks for the deprecated info @TheCrazyProfessor. "Yes", all I want to do is copy a table in Database 1 (source) exactly as is -- structure, data and indexes -- into Database 2 (target) using PHP. – H. Ferrence Jan 04 '17 at 00:24
  • 1
    The duplicate answers the error you were getting. Without clearing that error you could not proceed. I'll reopen the question, but only after editing out the error. – Jay Blanchard Jan 04 '17 at 13:02
  • Ok, thank you @JayBanchard. For those reading through and contributing/answering...here is the error message I get: **SELECT command denied to user 'username'@'localhost' for table 'sourceTable'** – H. Ferrence Jan 04 '17 at 15:47
  • I am trying to use `mysqli_query()` to create to new table structure in database #2 using database #1 @TheCrazyProferssor and I cannot get it to work. Have you had any success doing it yourself? If so, could you share the query? Thank you. – H. Ferrence Jan 05 '17 at 00:57
  • It does not look like this is possible using `mysqli`. – H. Ferrence Jan 05 '17 at 15:59

2 Answers2

1

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)

degr
  • 1,559
  • 1
  • 19
  • 37
  • So are you actually stating that the answer to the OQ is that you cannot achieve this in PHP @degr? I am building an application in PHP to do this so the command line and phpMyAdmin are not desirable alternatives. – H. Ferrence Jan 04 '17 at 15:48
  • Thank you @degr. I'll look through your answer and try it. Please note, there is no reason for me to capture all tables in the databases. I intrinsically know the exact table I want to copy from db1 to db2. So my lines of code will be condensed. – H. Ferrence Jan 05 '17 at 12:50
  • Decided to give up and do it all manually in phpMyAdmin. This is way too complex to try to program, test and re-use. After spending 3 days trying, I could have manually copied to db tables over hundreds of times in phpMyAdmin. Oh well...live & learn :) – H. Ferrence Jan 05 '17 at 16:05
0

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);
AzMoo
  • 486
  • 3
  • 10