/etc OSBN/Ubuntuusers Planet XING / LinkedIn / Amazon

Apply particular WordPress post meta settings database-wide

Doing a recent WordPress theme installation I stumbled upon a problem which could have taken much time to fix it.

The theme I installed offers multiple sidebar configurations (left, right, none). There is no default settings which means that it is necessary to set the sidebar configuration for every particular blog post – otherwise the design is erroneous. This means that it would have been necessary to do the following steps for every blog post:

  1. Edit post
  2. Select sidebar configuration
  3. Save post

Doing this for nearly 600 posts would have been a very time-consuming tasks – so I was looking for a way to automate this.

After having a deeper look at the MySQL datebase I found out that updating the blog post creates a new entry in the wp_postmeta table:

post_id meta_key meta_value

This means that blog posts that are not updated manually don’t have this setting which forces the crashed design.

So it is necessary to insert entries for all created blog posts in this table. The IDs of all created blog posts (published and unpublished) can be gathered by accessing the wp_posts table:

SELECT DISTINCT `post_parent`
FROM `wp_posts`
WHERE `post_type` = 'revision'
ORDER BY `post_parent` DESC;

The following nested query retrieves the appropriate meta setting for all blog posts:

SELECT  `post_id`, `meta_key`, `meta_value`
FROM `wp_postmeta`
WHERE post_id IN (SELECT DISTINCT `post_parent`
FROM `wp_posts`
WHERE `post_type` = 'revision'
ORDER BY `post_parent` DESC) AND `meta_key` = 'bluth_post_layout'
ORDER BY `wp_postmeta`.`post_id` DESC;

As I only edited one blog post manually the query only printed one record.

If you want to save the setting for all blog posts you need to run the following SQL command:

INSERT INTO `wp_postmeta` (post_id, meta_key, meta_value)
SELECT DISTINCT `post_parent`, 'bluth_post_layout', 'single'
FROM `wp_posts`
WHERE `post_type` = 'revision'
ORDER BY `post_parent` DESC;

If you execute the previous query once again you will see much more results – giving you time for more important things! 🙂

Sharing is caring