WordPress – How to retrieve all posts from one or more given categories with a SQL query

Today a client asked me to remove all the posts from a given category to the auto-generated XML Sitemap of his WordPress blog and also redirect these posts to a new website (same slug, different host): here’s a quick report of how I managed to do that, hoping that it would also help other System Administration that will be given these same tasks.

Get all posts by one or more categories

Since the sitemaps were generated by the Yoast SEO plugin, the first thing I did was to look up its configuration settings to see if there already was an option to exclude a specific category. Unfortunately, the plugin doesn’t give the chance to do that, at least with the free version: luckily enough there was an “exclude posts by ID” feature that I could use instead, since the blog owner had told me that the category wouldn’t been used anymore. All I had to do was retrieve a comma-separated list of post IDs to put into that textbox.

I managed to do that with the following SQL query, which will work in MySQL and MariaDB:

The above query will only fetch published posts: be sure to change that if you want to include drafts and/or post revisions as well. Once done, put the query result into the Yoast SEO XML Sitemap exclusion textbox and you should be good to go.

Redirect all posts by one or more categories

I already explained the technique in this other post, however here’s the deal:

  • open the functions.php file of the current theme.
  • add the following:
Replace the sample $catArray values with your chosen category (or categories), and you’ll be good to go. Notice that I also appended a ?r=1 parameter to the target URL in order to allow the redirects issued here to be easily tracked by Google Analytics or other traffic monitoring tools.

That’s it: for additional info regarding the redirection technique by taxonomies, I strongly suggest to read this post.

 

 

RELATED POSTS

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it’s also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies.

View all posts by Ryan