How to Bulk Deselect a Category for Posts without Plugin

"Category" is a default taxonomy in WordPress. Operators can use categories to sort and group blog posts into different sections. Categories also help visitors navigate sites faster, as well as knowing what topics your websites are about. A website operator can implement basic operations with categories and posts with WordPress admin panels as well as using WordPress plugins to manipulate posts in different manners.

However, overusing plugins for WordPress sites should not be an option since it probably causes potential performance issues and security issues. In this article, let's learn about how to bulk deselect a category for posts without using plugins.

Understanding How WordPress Handles Web Requests from Clients?

Understanding how a web server handles a request from a client is necessary to manipulate the WordPress site. The detailed operations include bulk deselect posts in a category.

The typical web server is the LAMP stack (Linux --- Apache --- MySQL/MariaDB --- PHP). Other equivalent concepts can be found in other web server platforms.

LAMP stacks server-client communication

1. The client sends HTTP requests to ask for web data from the webserver.

2. The web server receives valid HTTP requests from clients (after passing firewall rules):

• Analyze the structure of HTTP requests, identify what exactly HTML pages that clients need;
• Parsing the requests to the scripting layers (PHP).

3. The scripting layers PHP:

• Query to the database, obtain all necessary information;
• Combine with source code in the themes to render dynamic web pages;
• Return to Web Server.

4. The web server:

• Construct HTTP response messages.
• Send HTTP response messages to clients.

5. The client receives HTTP response messages from the webserver, then displaying the web data in its web browsers.

Important Tables for WordPress Posts' Bulk Manipulation

There is a pack of tables in the database' schema of WordPress sites. The schemas vary from site to site. However, when manipulating posts of WordPress site, these tables should be under higher concern:

Table nameDescriptionswp_postmetaStore additional content related data of a WordPress site.wp_postStore all important content-related data of a WordPress site.wp_term_relationshipsStore all conjunction data connecting posts to categories, tags, links. The information from this table acts as a map between the terms objects and the terms.wp_term_taxonomyStore additional data for wp_terms tables.wp_termStore categories, tags for posts, pages, and links.

Move All Posts from a Category to Another Category --- Using SQL Commands

In order to manipulate the WordPress database directly, operators need to decide which DBMS (Database Management System) to use. Because the lab environment uses LAMP stack as a web platform, operators can work with WordPress databases using phpMyAdmin, WordPress plugins, even MySQL client (terminal command) through SSH sessions ... In this scenario, the DBMS is MySQL WorkBench for a better illustration purpose.

Before implementing any change in WordPress databases, it is advisable to have a full backup of the WordPress site. This full backup includes WordPress source code (themes, PHP files ...) and database structure. For ideal purposes, operators can create a full backup of the whole host running the WordPress site.

Here are the complete SQL scripts to move all posts from the "Uncategorized" category to the "Inspirations" category.

SET @listID = (\
SELECT GROUP_CONCAT(ID) FROM wp_posts\
WHERE post_type="post"\
AND post_status = "publish"\
AND ID IN (\
SELECT object_id FROM wp_term_relationships\
WHERE term_taxonomy_id IN (\
SELECT term_taxonomy_id FROM wp_term_taxonomy\
WHERE taxonomy = "category"\
AND term_id IN (\
SELECT t.term_id FROM wp_terms t\
WHERE t.name="Uncategorized"\
)\
)\
)\
);

SET @newCategoryID = (\
SELECT t.term_id FROM wp_terms t WHERE t.name='Inspirations'\
);

SELECT @newCategoryID;

set @sqlCmd = CONCAT(\
'UPDATE wp_term_relationships tr ',\
'SET tr.term_taxonomy_id = ', @newCategoryID , ' ',\
'WHERE tr.object_id IN (', @listID , ');'\
);

SELECT @sqlCmd;

PREPARE stmt FROM @sqlCmd;\
SET SQL_SAFE_UPDATES = 0;\
EXECUTE stmt;\
SET SQL_SAFE_UPDATES = 1;\
DEALLOCATE PREPARE stmt;

The detailed script will be explained as follows.

Get an ID List of All Posts Belonging to a Specific Category

In this demo, we will demonstrate how to bulk deselect all posts from the "Uncategorized" category then move them to a new category named "Inspirations".

From the WordPress admin panel, these 3 posts are sorted in the "Uncategorized" category.

The SQL commands below will retrieve all posts ID of a category named "Uncategorized".

SET @listID = (\
SELECT GROUP_CONCAT(ID) FROM wp_posts\
WHERE post_type="post"\
AND post_status = "publish"\
AND ID IN (\
SELECT object_id FROM wp_term_relationships\
WHERE term_taxonomy_id IN (\
SELECT term_taxonomy_id FROM wp_term_taxonomy\
WHERE taxonomy = "category"\
AND term_id IN (\
SELECT t.term_id FROM wp_terms t\
WHERE t.name="Uncategorized"\
)\
)\
)\
);

Verify the value of @listID:

Get an ID of the New Category

SET @newCategoryID = (\
SELECT t.term_id FROM wp_terms t WHERE t.name='Inspirations'\
);

Verify the value of @newCategoryID:

Update the Posts with Given ID Lists

First, build a SQL command and store it in a variable @sqlCmd.

/* Expected command stored in variable @sqlCmd:\
UPDATE wp_term_relationships tr\
SET tr.term_taxonomy_id = 5\
WHERE tr.object_id IN (131,132,133);

*/\
set @sqlCmd = CONCAT(\
'UPDATE wp_term_relationships tr ',\
'SET tr.term_taxonomy_id = ', @newCategoryID , ' ',\
'WHERE tr.object_id IN (', @listID , ');'\
);

Second, verify it:

Finally, issue the SQL commands stored in the @sqlCmd variable with the below statements:

PREPARE stmt FROM @sqlCmd;\
SET SQL_SAFE_UPDATES = 0;\
EXECUTE stmt;\
SET SQL_SAFE_UPDATES = 1;\
DEALLOCATE PREPARE stmt;

Here is the result:

Last Words

There are various ways to bulk deselect a category for posts in WordPress sites. Either using plugins to interact via a web interface, or using SQL commands to make a direct impact on the WordPress database, operators should understand what are essential objects being modified.

Hopefully, this article gives you one more choice to bulk deselect a category for posts. To get more useful techniques in WordPress, let's follow our upcoming articles!

--- --- ---

The publication at GretaThemes.