Thursday, 14 May 2009

SELECTing SELECT statements for Wordpress MU blogging statistics

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!

No comments:

Post a Comment