My blog has been through the ringer.

First, it’s really the combination of 2 custom blogs that I had created and manually ported to WordPress.

Second, none of the posts in my old blogs were really “categorized”, so my posts came to WordPress meta-data-challenged.

Third, some of the ways that WordPress handles content is a bit weird.

Fourth, it takes FOREVER to go through and apply a featured image to EVERY SINGLE POST.

Let’s Back Up: The Story

Ever since I ported my old blogs into this single platform, I’ve wanted to do a better job of categorizing my posts.  Fortunately, WordPress makes this pretty easy with the “Quick Edit” options. Once I finally got all my posts properly categorized, I wanted to be able to associate an image with each category, to sort of spruce up the look of my posts and associate each category with a visual aspect.

While there are a bunch of plugins that allow for this sort of thing, I didn’t really want to mess with implementing some kind of code solution. After all, I don’t mind picking an image for each post…it was really just getting all the old posts associated with a particular “featured image” that was the issue.

The Solution

Sure, there are probably a billion ways to do this.  However, my solution was to bust into the database tables and wreak some havoc.  To me, this felt like the quickest solution and, well, it’s about time I familiarized myself with the WP architecture anyway…

So if this is something you’d like to do, the very first thing you want to do is to prepare all the images for your categories.  You can obviously do this as you go, but having them all finished up front makes things go smoother. 🙂

Get the Category Images

Now, get into your favorite mySQL IDE and run the following query:

select	*
from	wp_posts
where post_mime_type like '%image/%'

Media item references are, interestingly, stored in the wp_posts table.  You can differentiate them from other content by, among other things, keying in on the post_mime_type column.

In the results that are returned, you’re going to be most interested in the ID.

Get Posts Belonging to a Category

Now that we’ve got the images, either save the id’s in a format that you’ll remember, or open another query window.  In our next query, we’ll be getting the id’s of all the posts that belong to a particular category:

select	p.id,p.post_title
from wp_posts p
join wp_term_relationships r on p.id = r.object_id
join wp_term_taxonomy tx on r.term_taxonomy_id = tx.term_taxonomy_id
join wp_terms t on tx.term_id = t.term_id
where t.name in( 'your category here') and p.post_parent = 0 and tx.taxonomy = 'category'

Nothing terrifically spectacular happening here.  Basically, it’s just a number of joins from wp_posts table to get at the relationship created between wp_terms and the wp_term_relationships tables.  I’ll not walk through those relationships here, but using the query you should be able to work it out with a bit of snooping in your own database.

This query simply returns the ids (and titles…for readability) of all the posts that belong to the category specified in the WHERE clause.  Also, you’ll notice I used in() rather than =.  This leaves it open a bit in case you want to query on several categories.

With the results in hand, copy all the ids, and get ready for one last query.

Insert Meta Records for Category Posts

When a “featured image” is attached to a post, a record is created in the wp_postmeta table.  It records the id of the post, as well as the meta_key “_thumbnail_id” and the meta_value of the image (remember those image id’s we got earlier).  So, in order to add a featured image to all of the posts in our particular category, we simply need to do an insert/select statement, using the post id’s that we just got done retrieving.  The following should do the trick:

insert into wp_postmeta (post_id,meta_key,meta_value)
select id,'_thumbnail_id','the_id_of_your_image'
from wp_posts
where id in(1,2,3,4,....) <-- these are the ids that we just retrieved from the category lookup

This is pretty straightforward.  We start off with the regular insert syntax.  However, instead of directly specifying the values(), we use a select statement instead.  So then, for each row that is returned from the select statement, a new row in wp_postmeta will be inserted with the id of the post for the post_id, the string “_thumbnail_id” for the meta_key, and the id of the image that is specified for the meta_value.

And of course, you could always take the category lookup query and place it in the where clause of the final insert/select…

Wrapping Up

I hope this is helpful to someone, but I don’t want to mislead people in the purpose of this.  The utility of this approach is really just for a sort of “bulk” addition of featured images to posts that all belong to the same category. After the updates are finished, this script is really not useful anymore, especially not for managing the application of featured images to new posts (unless you want to do the bulk additions again).  Nonetheless, I found it to be a pretty useful approach for achieving my goals, and just thought I’d share in case anyone else can use it.  Enjoy!