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

Wordpress: Theme or Plugin not working as it should? Check for duplicate CSS or JS files

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.

 

 

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. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.