Sometimes I miss the coding I did last century. Today I was reminded of some of the fun, when I had set my mind to doing some statistics on my blogging.

In a blog entry on http://blogs.arno.fi/isit/2009/05/14/home-made-blog-statistics-from-wordpress-mu/ I describe what I did.

The blog entry may be of interest for those who use WordPress and are set back by the huge amounts of tables it generates. I happen to host 18 blogs and with each blog requiring 8 tables, that’s a total of 144 tables. Add the 9 top-level blogs and I’ve got 153 tables to navigate.

The blog entry I wrote 

• identifies the key fields
• shows how to do stats on individual blogs
• creates a statistics table into which I aggregate relevant entries from individual blog tables
• uses SELECT to generate SELECT statements
• and ends up with some statistics on the 253 blog entries in my WordPress.

I also studied the wp_n_comments tables, and came to the conclusion that I’ll need to use some global DELETEs to clean up spam comments that have found their way to my site.

I found out that, strangely enough, I haven’t approved a single comment that includes the character string “viagra”

select comment_approved,count(*)
 from wp_4_comments
 where comment_content like "%viagra%"
 group by comment_approved;

The same applies to “cialis“.

So I issued

delete from wp_4_comments 
 where comment_content like "%cialis%";

delete from wp_4_comments
 where comment_content like "%viagra%";

but I still have quite a bit of cleaning up to do, since despite deleting first 759 cialis entries and then 411 viagra entries, I still have 3683 unapproved comments to clean up (and I suspect there are less than 10 real comments that have slipped my attention in my inbox, when I’ve got notification of them).

At any rate, I got my statistics and had more efficient and fun (albeit incomplete) spam cleaning than ever before!

This entry was posted on Thursday, May 14th, 2009 at 10:43 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.