Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1385
  • Last Modified:

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

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
UncleBubba
Asked:
UncleBubba
  • 2
  • 2
1 Solution
 
richdiesalCommented:
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
 
UncleBubbaAuthor Commented:
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
 
richdiesalCommented:
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
 
UncleBubbaAuthor Commented:
You are the proverbial diamond, thanks for your excellent help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now