B2evolution has a really good user interface for managing and updating your blogs, but when it comes to doing bulk changes it falls down a little. That said, none of the blogging applications allow you to do the kinds of operations that only SQL can manage. I came across a problem with deleting categories. If there are lots of posts using the category you want to delete, then you have to go into each post by hand and change the dependency on the category. I wanted to delete my “Announcements” category but when I tried to delete it I got the message:

The following relations prevent deletion: * 44 posts within category through main cat * 44 posts within category through extra cat

Bummer - 88 posts to go through! There must be an easier way! So I started playing! As any system administrator would do to start, back up the database.

mysqldump -u user -p pass database > backup.sql

First we need to find the id of the category we want to select. So log into MySQL:

mysql -u user -p pass database

and issue the command:

SELECT cat_ID, cat_parent_ID, cat_name FROM evo_categories;

+--------+---------------+---------------+
| cat_ID | cat_parent_ID | cat_name      |
+--------+---------------+---------------+
|      4 |          NULL | Announcements |
|      5 |          NULL | Fun           |
|     12 |          NULL | b2evolution   |
|     13 |          NULL | contributors  |
|     14 |          NULL | Techie        |
+--------+---------------+---------------+

The category id for “Announcements” is 4, so remember it:

select @cat := 4;

Now that we have the category id we can get to work. We can check the error report “44 posts within category through main cat” (just to make sure things tie up). First we will handle the “extra cat” categories. Check the number of post links there are in the post<->category table. This number should match the “prevent deletion message”

SELECT COUNT(1) FROM evo_postcats WHERE postcat_cat_ID = @cat;

+----------+
| COUNT(1) |
+----------+
|       44 |
+----------+

If the category you want to delete is a parent then be careful, I don’t have any parent categories so can’t show you have to clean them up. Sorry! The following shows the total numbers of posts per (extra) category.

SELECT postcat_cat_ID, COUNT(1) FROM evo_postcats GROUP BY postcat_cat_ID;

+----------------+----------+
| postcat_cat_ID | COUNT(1) |
+----------------+----------+
|              4 |       44 |
|              5 |        8 |
|             12 |        2 |
|             13 |        9 |
|             14 |       34 |
+----------------+----------+

Next we will link the category names in, if the numbers don’t match with the table above then you have some broken links. This SQL join will allow you to see categories that have no posts.

SELECT cat_name, COUNT(postcat_cat_ID) FROM evo_categories LEFT JOIN evo_postcats ON cat_ID = postcat_cat_ID GROUP BY postcat_cat_ID;

+---------------+-----------------------+
| cat_name      | COUNT(postcat_cat_ID) |
+---------------+-----------------------+
| Announcements |                    44 |
| Fun           |                     8 |
| b2evolution   |                     2 |
| contributors  |                     9 |
| Techie        |                    34 |
+---------------+-----------------------+

Now we can delete the extra categories from the links table:

DELETE FROM evo_postcats WHERE postcat_cat_ID = @cat ; Query OK, 44 rows affected (0.01 sec)

Go back to B2evolution and try to delete the category again. It has changed to:

The following relations prevent deletion: 44 posts within category through main cat

This is where it gets a bit trickier because each post must have a category - there is no choice on this. We need to find out which posts have “Announcements” as their main category. We issue the MySQL command to get the relevant information from the table.

SELECT post_ID, post_title, post_main_cat_ID FROM evo_items__item WHERE post_main_cat_ID = @cat;

+---------+-------------------------------------...--+------------------+
| post_ID | post_title                               | post_main_cat_ID |
+---------+-------------------------------------...--+------------------+
|      25 | Training SpamAssassin, Horde, IMAP, ...  |                4 |
:         :                 :                        :                  :
|      70 | Upgrading Plesk 8.3.0 to 8.4.0 to 8.6.0  |                4 |
+---------+-------------------------------------...-+-------------------+

For all the posts that have their main category set to “Announcements", we have to change it to something else, otherwise it won’t link properly and you will lose articles. From the categories table (above) select a category id which we are going to change the rest of the posts main category to. You may want to change different posts to different main categories. Have a look at your articles and decide where you want to reposition them. Remember this is SQL, so do the smallest number of changes first as an explicit update and the rest as a bulk update. So first, set the main category to “Fun” (5) and the rest to “Techie” (14).

UPDATE evo_items__item SET post_main_cat_ID = 5 WHERE post_ID IN (26, 31, 32, 39, 46, 47, 58); UPDATE evo_items__item SET post_main_cat_ID = 14 WHERE post_main_cat_ID = @cat;

Although a post can have a main category and several optional (referred to as “extra") categories; the main link and the extra link are specified in the same way. To put it another way, the main category is explicitly specified as an extra category as well. To illustrate this we can write a piece of SQL to list the posts which do not have links to the categories table:

SELECT post_id, post_main_cat_ID, post_title FROM evo_items__item LEFT JOIN evo_postcats ON post_id = postcat_post_ID WHERE postcat_post_ID IS NULL;

We will need to add the extra link to join the main category as an extra category otherwise the posts will disappear from the view. We will reuse the SQL above to help with the insert.

INSERT INTO evo_postcats (postcat_post_ID, postcat_cat_ID) SELECT post_id, post_main_cat_ID FROM evo_items__item LEFT JOIN evo_postcats ON post_id = postcat_post_ID WHERE postcat_post_ID IS NULL;

Now when we list the posts with no category extra link we should get no results. The final stage is to go back into the B2evolution web interface and delete the category from there. Job done.