Moving Data from one Database to Another with PHPAdmin

Moving from one blog to another can be a real pain. Especially if you don’t want to write a conversion program, or are limited to using a tool such as phpMyAdmin.

So here’s what we’re going to do. We’re going to move data from table to another, via a third interim table. Why? The interim table gives us the opportunity to make changes both to the data and/or the table structure without fear of destroying production dasta. Of course it doesn’t hurt to have a real backup as well. To do this, selected the destination table within the destination database using phpAdmin. Once the table was selected, I choose the “Operations” tab/screen and copied the structure to the source database.

At this point, I had within my Nucleus database an interim table named item2mt, which was based upon the structure of the mt_entry table in my MovableType database. Now to move the data from the source table into the interim table. This mean using the following SQL construct:

INSERT into `item2mt` SELECT inumber, 1, 2, iauthor, 0, 0, ‘__default__’, icat, ititle, ”, ibody, imore, ”, null, null, null, itime, itime, 1, 1 from `nucleus_item`;

The trick here is to make sure that the datatypes and number of columns (fields) after the keyword SELECT match that of the iterim table.
Once this is done, you can apply any host of SQL queries to massage the data to taste. For example:

UPDATE `item2mt` SET icat = 7 WHERE icat = 9;

You can then use phpAdmin’s Export feature, or once again use the “Operations” screen, to get data over to the destination table. Of course, if the interim table is either in, or moved/copied to the destination database, then one only need to run the following SQL to get the job done:

INSERT into `mt_entry` SELECT * FROM `item2mt`;

Yes, I know this is a very geek article, but data conversion happens. Since some of you aren’t code monkies, I figured I’d give you some pointers on how to use a common (and free) tool such as phpMyAdmin to get the job done.

Now for those of you who feel like living dangerously. Here’s how you can do it from table-to-table from the confines of a single database (to another database). All you need is the structure of the source table via the phpAdmin Export features, then …

Of course, if you’re a SQL-stud, you can forego any saftey nets you might gain from an interim table and just table-to-table, database-to-database using the following syntax:

Don’t forget to rebuild. And it might not hurt to back things up first.

UPDATE – MovableType has a table called mt_placement which hooks-up entries with their categories. If you’ve populated an entry table with any of the above methods, than the following query will get you the rest of the way there:

INSERT INTO `mt_placement` SELECT entry_id, entry_id, entry_blog_id, entry_category_id, 1 FROM `mt_entry`