Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-09-30
4
Medium Priority
?
1,329 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 2000 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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

688 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