Solved

Duplicate and Re-prefix Tables in a MySQL Database with PHPMyAdmin

Posted on 2008-09-30
4
1,083 Views
Last Modified: 2013-12-12
Hello, I have a (hopefully) simple but very specific question regarding database management.
I have a single MySQL database containing all of the relevant tables for a live Joomla installation.
I want to make another joomla installation using the same table data but with a different prefix so, using PHPMyAdmin, I want to duplicate the existing tables but I want the newly created duplicates to have a new prefix.
For example:
Let's say that there are currently 50+ tables (rows?? tables??) called things like:
joom_users (xx;xx;xxxx;)
joom_headers(yy;xx;xxxx)
etc.

I want to make exact copies of all tables prefixed with joom_ but I want all of the duplicates to be prefixed joom2_ so that you end up with
 joom_users (xx;xx;xxxx)
 joom_headers (yy;xx;xxxx;)
 joom2_users (xx;xx;xxxx;)
 joom2_headers(yy;xx;xxxx)
etc.

Is this possible with PHPMyAdmin?
How (exactly please) would I go about this?
0
Comment
Question by:UncleBubba
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:richdiesal
ID: 22610426
A series of table creation statements in SQL would probably be easiest...

CREATE TABLE joom2_users AS SELECT * FROM joom_users;

Just create one line per table you want to duplicate...  you can enter this SQL directly by clicking on the "SQL" tab at the top of PHPMyAdmin.
0
 

Author Comment

by:UncleBubba
ID: 22612366
Thanks richdiesal.
I will try this later, then return to assign points
Being a lazy sod, is there a way to make it automatically do this for any table whose name begins with joom_  without having to type in each line?
0
 
LVL 9

Accepted Solution

by:
richdiesal earned 500 total points
ID: 22616179
Well, you could, but frankly it would be a lot more effort than just doing it by hand.

Personally, I would write them all in Excel or another spreadsheet program like this:
1) Put all table names in Column A
2) Put the code snippet below into Column B
3) Copy all lines from Column B into SQL

Although I would double check to make sure all of your table names look correct in the SQL before you run it...
="CREATE TABLE joom2_"&MID(A1,6,30)&" AS SELECT * FROM "&A1&";"

Open in new window

0
 

Author Closing Comment

by:UncleBubba
ID: 31501564
You are the proverbial diamond, thanks for your excellent help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now