Solved

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

Posted on 2008-09-30
4
1,155 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
The viewer will learn how to dynamically set the form action using jQuery.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

861 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