While B2Evolution’s search and back office interfaces are excellent they cater for the general use case. Search is a good example of this as there are many things to search for but a limited amount of search criteria in the interface. There are also tasks you might want to perform that would normally mean spending hours going through the web interface.

SQL is the way to go. It allows you to update your blogs structure underneath in a couple of well crafted statements. So I thought I’d share them with you.

Deleting a category with many dependencies
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.
http://www.bigsoft.co.uk/blog/index.php/2008/09/28/b2evolution-deleting-a-category-with-man

Finding articles without any tags
I wrote a related article plug-in that relies on tags so I wanted to find out which of my live blog articles don’t have enough tags. This piece of SQL gives you a list of blog article titles followed by the number of tags that are associated with each article. I have added a filter to ignore articles that have more than a set number of tags.

SELECT @mintags := 2;
SELECT post_ID, post_title, count(itag_tag_ID) ntags FROM evo_items__item
LEFT JOIN evo_items__itemtag ON itag_itm_ID = post_ID
WHERE post_status = ‘published’
GROUP BY post_ID
HAVING ntags < @mintags
LIMIT 20
;

You can pull out the post_id and use it in this SQL that displays the full set of tags for an article.

SELECT @postid := 30;
SELECT itag_itm_ID, tag_name
FROM evo_items__itemtag, evo_items__tag
WHERE itag_itm_ID = @postid
AND itag_tag_ID = tag_id
;

Viewing comments and deleting spam

SELECT comment_ID, comment_date, comment_author, comment_author_IP, SUBSTRING(TRIM(comment_content),1,150)
FROM evo_comments
WHERE comment_status="draft"
ORDER BY comment_author_IP
;

Then delete them all:

DELETE FROM evo_comments
WHERE comment_status="draft"
;

Or delete by IP address:

DELETE FROM evo_comments
WHERE comment_status="draft"
AND comment_author_IP IN (’xxx,xxx,xxx,xxx’, ‘yyy,yyy,yyy,yyy’)
;