How to change database prefix for some tables?
Moderator: General Support Moderators
Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
-
- Joomla! Apprentice
- Posts: 27
- Joined: Wed Dec 03, 2008 10:43 pm
How to change database prefix for some tables?
I made some mess after installation of second site in xampp/htdocs folder.
My first site database was with prefix bak_, and second one with jos_
but now I lost all previous work (in first site) becouse in phpmyadmin
/databases/joomla there are some tables with prefix bak_ and some with prefix jos_
(like in picture http://www.arterego.co.cc/databases.jpg ).
Can I somehow rename all prefixes jos_ to bak_ with phpmyadmin,
and restore my first site?
My first site database was with prefix bak_, and second one with jos_
but now I lost all previous work (in first site) becouse in phpmyadmin
/databases/joomla there are some tables with prefix bak_ and some with prefix jos_
(like in picture http://www.arterego.co.cc/databases.jpg ).
Can I somehow rename all prefixes jos_ to bak_ with phpmyadmin,
and restore my first site?
- Per Yngve Berg
- Joomla! Master
- Posts: 30809
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: How to change database prefix for some tables?
The bak_ prefix is for backup and should not be used on a live site. The install program will rename any old tables to bak_ and overwrite your site.
Use Jos1_ and Jos2_ or anything different than bak_ or Jos_.
Rename the tables with PhPmyadmin and edit configuration.php to correspond to the same prefix.
Use Jos1_ and Jos2_ or anything different than bak_ or Jos_.
Rename the tables with PhPmyadmin and edit configuration.php to correspond to the same prefix.
-
- Joomla! Apprentice
- Posts: 27
- Joined: Wed Dec 03, 2008 10:43 pm
Re: How to change database prefix for some tables?
Thank you, but can I rename (and how) table prefixes for jos_ tables with PhPmyadmin?
Thanx advanced.
Thanx advanced.
- PtrNrs
- Joomla! Explorer
- Posts: 355
- Joined: Sat Jul 05, 2008 3:56 am
- Location: Melbourne, Australia
- Contact:
Re: How to change database prefix for some tables?
First, before you start backup, backup & backup (not literally three times, but you understand).
Now, phpMyAdmin's Export function (click on Add DROP TABLE / VIEW / PROCEDURE / FUNCTION) creates the following mySQL code like this . . .Now, assuming you're changing from "jos_" to josNew_", edit the code to read . . .Paste this code in the SQL tab and press GO.
Now, phpMyAdmin's Export function (click on Add DROP TABLE / VIEW / PROCEDURE / FUNCTION) creates the following mySQL code like this . . .
Code: Select all
DROP TABLE IF EXISTS `jos_templates_menu`;
CREATE TABLE IF NOT EXISTS `jos_templates_menu` (
`template` varchar(255) NOT NULL default '',
`menuid` int(11) NOT NULL default '0',
`client_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`menuid`,`client_id`,`template`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `jos_templates_menu` (`template`, `menuid`, `client_id`) VALUES
('rhuk_milkyway', 0, 0),
('khepri', 0, 1);
Code: Select all
DROP TABLE IF EXISTS `jos_templates_menu`;
CREATE TABLE IF NOT EXISTS `josNew_templates_menu` (
`template` varchar(255) NOT NULL default '',
`menuid` int(11) NOT NULL default '0',
`client_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`menuid`,`client_id`,`template`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `josNew_templates_menu` (`template`, `menuid`, `client_id`) VALUES
('rhuk_milkyway', 0, 0),
('khepri', 0, 1);
- Per Yngve Berg
- Joomla! Master
- Posts: 30809
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: How to change database prefix for some tables?
No need to import/export. Just change the name with this sql:
Code: Select all
ALTER TABLE tbl_name RENAME TO new_tbl_name
-
- Joomla! Apprentice
- Posts: 27
- Joined: Wed Dec 03, 2008 10:43 pm
Re: How to change database prefix for some tables?
Hmm...
In my Database: joomla structure, there are still tables with both prefixes.
I think that this problem is too tricky for me and my beginer joomla knowledge,
so, please, can someone tell me:
Is there posibility to find text from articles and modules of my first site?
I don't have any backup, and afraid that I can't write it again. It takes toooo long...
Please...
In my Database: joomla structure, there are still tables with both prefixes.
I think that this problem is too tricky for me and my beginer joomla knowledge,
so, please, can someone tell me:
Is there posibility to find text from articles and modules of my first site?
I don't have any backup, and afraid that I can't write it again. It takes toooo long...
Please...
- PtrNrs
- Joomla! Explorer
- Posts: 355
- Joined: Sat Jul 05, 2008 3:56 am
- Location: Melbourne, Australia
- Contact:
Re: How to change database prefix for some tables?
Use phpMyAdmin's Export function to script out the whole database to a text file. Then you can search for the text.
-
- Joomla! Apprentice
- Posts: 27
- Joined: Wed Dec 03, 2008 10:43 pm
Re: How to change database prefix for some tables?
Noup, just some text for flipingbook extension...
- PtrNrs
- Joomla! Explorer
- Posts: 355
- Joined: Sat Jul 05, 2008 3:56 am
- Location: Melbourne, Australia
- Contact:
Re: How to change database prefix for some tables?
Are you sure you've exported the whole database? Vanilla Joomla alone would have some 20 tables.
-
- Joomla! Apprentice
- Posts: 27
- Joined: Wed Dec 03, 2008 10:43 pm
Re: How to change database prefix for some tables?
Ouch!
Youre right my friend!
But anyway, I deleted it all! Site install folder, database... All.
Anyway...
Just want to thank to all of you for great help and support!
Hope one day I'll be helpfull too.
SeeYa
Youre right my friend!
But anyway, I deleted it all! Site install folder, database... All.
Anyway...
Just want to thank to all of you for great help and support!
Hope one day I'll be helpfull too.
SeeYa
-
- Joomla! Apprentice
- Posts: 21
- Joined: Tue Jan 13, 2009 11:16 am
Re: How to change database prefix for some tables?
im trying to change ALL prefixes from bak_ to Jos_
How do i MASS do this on phpmyadmin, If you can give me a solution, you will be saving me Hours and Hours of work.
I try "Now, phpMyAdmin's Export function (click on Add DROP TABLE / VIEW / PROCEDURE / FUNCTION)" And it Crates a HUGE SQL list. is there an easyer way that works?? Thank you!
How do i MASS do this on phpmyadmin, If you can give me a solution, you will be saving me Hours and Hours of work.
I try "Now, phpMyAdmin's Export function (click on Add DROP TABLE / VIEW / PROCEDURE / FUNCTION)" And it Crates a HUGE SQL list. is there an easyer way that works?? Thank you!
- Per Yngve Berg
- Joomla! Master
- Posts: 30809
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: How to change database prefix for some tables?
Unfortnatly there is no wildcard on the rename command.
You can make a batch file in a text editor a run it.
In PhPMyadmin select the list of tables from the left column and copy it to the clipboard.
Paste it into a text editor and edit the lines.
Copy the finsihed list of alter commands to the clipboard.
In PhPMyadmin click on the SQL tab.
Paste in the list and click run.
You can make a batch file in a text editor a run it.
In PhPMyadmin select the list of tables from the left column and copy it to the clipboard.
Paste it into a text editor and edit the lines.
Code: Select all
ALTER TABLE bak_users RENAME TO js1_users;
ALTER TABLE bak_content RENAME TO js1_content;
In PhPMyadmin click on the SQL tab.
Paste in the list and click run.
- PtrNrs
- Joomla! Explorer
- Posts: 355
- Joined: Sat Jul 05, 2008 3:56 am
- Location: Melbourne, Australia
- Contact:
Re: How to change database prefix for some tables?
Yep, that's pretty much it.
You may need to use the same strategy to delete the existing jos_ tables (do you have any?) before renaming the bak_ tables.
You may need to use the same strategy to delete the existing jos_ tables (do you have any?) before renaming the bak_ tables.
Code: Select all
DROP TABLE jos_users;
DROP TABLE jos_content;
etc.
-
- Joomla! Apprentice
- Posts: 48
- Joined: Fri May 02, 2008 3:42 am
Re: How to change database prefix for some tables?
If you have Excel, paste the list into the first cell in a new spreadsheet (cell A1)
Each db name should now be in it's own row
Use REPLACE to replace the text before the db name with "ALTER TABLE". (in mine the text before the db name was # BROWSE)
In the first cell of the next column (cell B1) type "RENAME TO" and copy it down the rows to the end of list
Copy column A to Column C
Select all of Col C and REPLACE "ALTER TABLE bak_" with what ever you want the prefix to be.
Now type the following formula into the top cell in Column D. (cell D1) NOTE: there is a space between the first two sets of quote marks and a semi-colon between the last set. =A1&" "&B1&" "&C1&";"
Hit Enter
Copy that cell to all the cells below it in column D
Column D should now contain the proper text for you to cut and paste into the SQL tab
Each db name should now be in it's own row
Use REPLACE to replace the text before the db name with "ALTER TABLE". (in mine the text before the db name was # BROWSE)
In the first cell of the next column (cell B1) type "RENAME TO" and copy it down the rows to the end of list
Copy column A to Column C
Select all of Col C and REPLACE "ALTER TABLE bak_" with what ever you want the prefix to be.
Now type the following formula into the top cell in Column D. (cell D1) NOTE: there is a space between the first two sets of quote marks and a semi-colon between the last set. =A1&" "&B1&" "&C1&";"
Hit Enter
Copy that cell to all the cells below it in column D
Column D should now contain the proper text for you to cut and paste into the SQL tab
You do not have the required permissions to view the files attached to this post.
- fionaby
- Joomla! Enthusiast
- Posts: 163
- Joined: Fri Jul 27, 2007 2:03 pm
- Location: South Africa - Cape town
Re: How to change database prefix for some tables?
Silly question how do you copy just the table names in phpadmin
- Per Yngve Berg
- Joomla! Master
- Posts: 30809
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: How to change database prefix for some tables?
Under Operations. Copy the table and select structure only.