Solved

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

Posted on 2008-09-30
4
1,209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

751 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