Sometimes you need to access your WordPress database in order to modify things quickly and “dirtily” you could use a plugin but when you enjoy dealing with source code without worrying about installing any third part applications, why not just doing it !
Where are our posts inside our WordPress Database ?
Get yourself onto your server bash console and log in on your database :
> use my_wordpress_base;
Let’s assume you have a post_type named : testimonial
and you wish to transfer all of these posts inside another database named other_wordpress_base.
When you list all of your tables you will see the obvious entry here :
You may then check the content by issuing the following command :
SELECT * FROM wp_posts WHERE post_type like ‘testimonial’;
For a more in depth description of the database schema within WordPress, take a loog at the diagram at the following page :
You need to have rights to at least read from the source database and write to the destination one.
Without using a plugin, you also need access to either your Mysql server console or PhpMyAdmin depending on your hosting provider.
Transfer the posts from one base to the other
When transferring the data from the wp_posts table to another base, you need to prepare the Mysql command so you end up with the following items :
- the matching user ID (post_author)
Also you may need to transfer the posts terms (wp_terms) and post comments but we won’t treat them here.
Let’s update our new base by filling up the wp_posts table with our old entries :
> INSERT INTO new_DB.wp_posts SELECT * FROM old_DB.wp_posts WHERE post_type LIKE ‘testimonial’;
After issuing the above command, you will likely retrieve the following list on your WordPress interface :
We are missing the author, terms and language for each entry, we can just correct it using the following command :
Author name and ID :
> SELECT * FROM new_DB.wp_users;
Note the ID of the author(s) and update your table entries as follow :
> UPDATE new_DB.wp_posts SET post_author = 1 WHERE post_type = ‘testimonials’;
Here our author id is 1.
Transferring posts of a specific category
Dealing with post types is straightforward but when dealing with categories of posts there are a few steps to follow.
First of, retrieve the ID of the category you need by using Mysql commands :
> SELECT * FROM wp_term_taxonomy WHERE taxonomy =’category’;
Now check the table wp_terms for the entries matching the term_id :
> SELECT * FROM wp_terms WHERE term_id = 258;
Replace 258 by your own term id. The above command will display the name and slug so you can determine the proper term to use within the transfer query.
Now it’s time to dig into terms relationships by using a query to extract the post id :
> SELECT * FROM wp_term_relationships WHERE term_taxonomy_id = 258;
The object id represents the post id. After the above query, you have the list of posts to transfer.
In order to update your new base, you can issue the following Mysql query :
> INSERT INTO new_DB.wp_posts (ID,post_author,post_date,post_date_GMT,post_content,post_title,post_excerpt,post_status,post_name,post_modified,post_modified_GMT,g
uid,post_type,to_ping,pinged) SELECT ID,post_author,post_date,NOW(),post_content,post_title,post_excerpt,post_status,post_name,post_modified,NOW(),guid,’profiles’
,to_ping,pinged FROM old_DB.wp_posts WHERE ID IN (SELECT object_id FROM old_DB.wp_term_relationships WHERE term_taxonomy_id = 258);
If you take a look carefully above you will notice that we’ve modified the type of post to match our new database schema (post type is not post but profiles – however you are free to use post instead or any type that matches your needs).
After issuing this query you will see the results within your new WordPress administrative interface and you may add a few modifications such as categories, author, status…
How about your posts media ?
When you transfer posts, you might need to transfer images and featured ones !
First off, you need to manually copy all physical images from your uploads folder within your wordpress installation.
Then you need to tell WordPress about the media by inserting data into the database.
Check out your old database table named wp_postmeta as follow :
SELECT * FROM wp_postmeta WHERE meta_key LIKE ‘%thumbnail%’;
Now issue the Mysql query :
INSERT INTO new_DB.wp_postmeta SELECT * FROM old_DB.wp_postmeta WHERE meta_key LIKE ‘%thumbnail%’;
And one more step and you are done :
INSERT IGNORE INTO new_DB.wp_posts SELECT * FROM old_DB.wp_posts WHERE post_type= ‘attachment’;
Beware about the url to change if needed (apply this query in case : UPDATE new_DB.wp_posts SET guid = REPLACE(guid,’http://temp.’,’http://’) WHERE post_type LIKE ‘attachment’;)
There a few other entries you need within the wp_postmeta table, this time under _wp_attached_file, _wp_attachment_backup_sizes and _wp_attachment_metadata :
INSERT IGNORE INTO new_DB.wp_postmeta SELECT * FROM old_DB.wp_postmeta WHERE meta_key LIKE ‘_wp_attached_file’;
INSERT IGNORE INTO new_DB.wp_postmeta SELECT * FROM old_DB.wp_postmeta WHERE meta_key LIKE ‘_wp_attachment_metadata’;
You should be all set for the transfer of your posts and media, if you have additional meta, you may use a query to insert them all at once instead of dividing queries.
Tranfering WordPress users to the other base
Besides the administrator (I assume yourself), in case you have subscribers who are used to access a private page or whatever functionality on your website, you will need to transfer them as well.
To achieve this, you need to use the wp_users table and transfer the entries as follow :
> INSERT INTO new_DB.wp_users SELECT * FROM old_DB.wp_users WHERE ID > 1;
The ID of 1 represents our administrator.
I hope you enjoyed these lines and if you have any comment, do not hesitate, I’ll be glad to answer.