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 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!

Tuesday, 12 May 2009

How do MySQL users keep track of digital pictures?

On my non-MySQL blog, I just wrote a blog post called "Photo Manager: How do you keep track of your pictures?". I'm looking for Open Source Software that helps me keep track of my 100,000+ digital pictures. I wrote specs on dreamt-up software called “Robfat” (for rename, order, backup, find, archive, tag) as I want to remove excess fat from my HDs (and CD/DVD cabinets).

And then I thought this may have a MySQL dimension:

What if we had an "EXIF Storage Engine"? What if we could update EXIF tags directly from the MySQL command line, with UPDATE statements, and SELECT picture file names based on tags and other file characteristics?

But beyond this technical aspect, I think many, many MySQL users are avid photographers and may have input about the specs themselves, or even know of existing software that satisfied the needs.

So please go to the blog post and comment!

Wednesday, 6 May 2009

Presenting and blogging in Chinese

Travelling to Hongkong and Taipei has made such an impression on me, that I couldn't help but add two new blogs to my homepage

Guanxi means "relations", as in "Community Relations". It's also a very common word describing how to get things done in China. It even has its own English language Wikipedia entry.

Yi-ling-yi means one-oh-one, as in Taipei 101. This number sequence also means "special" in Chinese. Taipei 101 is the world's highest completed skyscraper. Needless to say, that merits another Wikipedia entry, not to mention a blog entry in Traditional Chinese.

Tomorrow, I'll make another attempt at giving a short MySQL speech in Mandarin. Well, technically speaking, it's already today, as it's 3:30am in Taipei. My internal clock has gone awry.