How to change database prefix for some tables?

Need help with the Administration of your Joomla! 1.5 site? This is the spot for you.

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.
Locked
banchow
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Wed Dec 03, 2008 10:43 pm

How to change database prefix for some tables?

Post by banchow » Mon Dec 08, 2008 5:33 pm

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?

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How to change database prefix for some tables?

Post by Per Yngve Berg » Mon Dec 08, 2008 9:18 pm

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.

banchow
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Wed Dec 03, 2008 10:43 pm

Re: How to change database prefix for some tables?

Post by banchow » Mon Dec 08, 2008 10:43 pm

Thank you, but can I rename (and how) table prefixes for jos_ tables with PhPmyadmin?
Thanx advanced.

User avatar
PtrNrs
Joomla! Explorer
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?

Post by PtrNrs » Mon Dec 08, 2008 11:18 pm

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 . . .

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);
Now, assuming you're changing from "jos_" to josNew_", edit the code to read . . .

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);
Paste this code in the SQL tab and press GO.
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How to change database prefix for some tables?

Post by Per Yngve Berg » Tue Dec 09, 2008 1:56 pm

No need to import/export. Just change the name with this sql:

Code: Select all

ALTER TABLE tbl_name RENAME TO new_tbl_name

banchow
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Wed Dec 03, 2008 10:43 pm

Re: How to change database prefix for some tables?

Post by banchow » Tue Dec 09, 2008 2:58 pm

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...

User avatar
PtrNrs
Joomla! Explorer
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?

Post by PtrNrs » Wed Dec 10, 2008 7:29 pm

Use phpMyAdmin's Export function to script out the whole database to a text file. Then you can search for the text.
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

banchow
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Wed Dec 03, 2008 10:43 pm

Re: How to change database prefix for some tables?

Post by banchow » Wed Dec 10, 2008 10:57 pm

Noup, just some text for flipingbook extension...

User avatar
PtrNrs
Joomla! Explorer
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?

Post by PtrNrs » Thu Dec 11, 2008 8:28 pm

Are you sure you've exported the whole database? Vanilla Joomla alone would have some 20 tables.
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

banchow
Joomla! Apprentice
Joomla! Apprentice
Posts: 27
Joined: Wed Dec 03, 2008 10:43 pm

Re: How to change database prefix for some tables?

Post by banchow » Thu Dec 11, 2008 9:45 pm

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

ringmasta6
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Jan 13, 2009 11:16 am

Re: How to change database prefix for some tables?

Post by ringmasta6 » Tue Jan 20, 2009 4:35 am

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!

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How to change database prefix for some tables?

Post by Per Yngve Berg » Tue Jan 20, 2009 10:16 am

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.

Code: Select all

ALTER TABLE bak_users RENAME TO js1_users;
ALTER TABLE bak_content RENAME TO js1_content;
Copy the finsihed list of alter commands to the clipboard.

In PhPMyadmin click on the SQL tab.
Paste in the list and click run.

User avatar
PtrNrs
Joomla! Explorer
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?

Post by PtrNrs » Tue Jan 20, 2009 11:38 am

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.

Code: Select all

DROP TABLE jos_users;
DROP TABLE jos_content;
etc.
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

sleiting
Joomla! Apprentice
Joomla! Apprentice
Posts: 48
Joined: Fri May 02, 2008 3:42 am

Re: How to change database prefix for some tables?

Post by sleiting » Fri Jan 30, 2009 1:12 am

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
You do not have the required permissions to view the files attached to this post.

User avatar
fionaby
Joomla! Enthusiast
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?

Post by fionaby » Mon Apr 27, 2009 5:41 pm

Silly question how do you copy just the table names in phpadmin

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How to change database prefix for some tables?

Post by Per Yngve Berg » Mon Apr 27, 2009 6:32 pm

Under Operations. Copy the table and select structure only.


Locked

Return to “Administration 1.5”