CODEWITHSUNDEEP
phpmysql
Jason
Jason

Reputation: 15335

Copying a database into a new database including structure and data

In phpMyAdmin under operations I can "Copy database to:" and select

I need to be able to do that without using phpMyAdmin.

I know how to create the database and user. I have a source database that's a shell that I can work from so all I really need is the how to copy all the table structure and data part. (I know, the harder part)

system() & exec() are not options for me which rules out mysqldump. (I think)

How can I loop through each table and recreate it's structure and data?

Is it just looping through the results of

SHOW TABLES

then for each table looping through

DESCRIBE tablename

Then, is there an easy way for getting the data copied?

UPDATE:

So, I ended up going with:

SHOW TABLES;

for each table then I did

SHOW CREATE TABLE `tablename`;

and then

INSERT INTO `newBDname`.`tablename` SELECT * FROM `existingDBname`.`tablename`;

Upvotes: 1

Views: 543

Answers (2)

Spechal
Spechal

Reputation: 2716

You can use mysql_fetch_assoc on SHOW TABLES and then for each result, store the output of mysql_fetch_result SHOW CREATE TABLE $table_name and then issue mysql_query on $show_create_table

Hope that helps ... more into helping than doing it for you. :)

Upvotes: 4

Michael Irigoyen
Michael Irigoyen

Reputation: 22947

Use SELECT INTO to accomplish this.

SELECT * 
INTO destinationDB..newTable 
FROM sourceDB..existingTable

Upvotes: 3

Related Questions