A nice bit of word count PHP

Firstly, to remind myself and to inform anyone less experienced than me how I made it, here are the basics (with dollar signs in front of words which must be replaced by values relevant to the individual setup) . I SSH’ed into my web host, and logged into the [MySQL] database server using mysql -u $username -p then typing my password. From here I specified which database to use with use $database. Consulting the WordPress Codex, I quickly found the name of the tables and columns involved, and whipped up this short but sweet SQL query: SELECT post_content FROM wp_posts WHERE post_author=1 AND post_status='publish'. This is explained more in the comments of the code, but I will point out that I have specifically limited the word count to just the posts written by me ([WordPress] assigned the number 1 to the username I use) and posts which have actually been published.

Turning this query into an interesting set of results was the job of some serious PHP, involving a lot of regular expression replacement. In general, this meant stripping out all punctuation except hyphens and apostrophes (and I realised quite late that it was simpler to do this by whitelisting the characters I do want than blacklisting those I don’t). To be precise though, I then had to deal with words that began, ended or consisted solely of hyphens, apostrophes and spaces, and words that ended “apostrophe ess” so that “Linux’s” wasn’t counted as a separate word from “Linux” (although that does undervalue the ess slightly).

Then came the use of arrays. There are some powerful array functions in PHP, and they are a natural choice for dealing with list- and table-based data. Using the explode() function I could turn the cleaned up strings from the query into arrays, and then use the array_count_values() function to generate a per-post word count in the form of an array with words as keys and frequencies as values. The biggest algorithmic problem I faced was what came next, namely combining the per-post word count arrays into a whole-blog word count array. Simply merging the two arrays would result in data loss because values from later arrays would overwrite values from earlier arrays with the same keys. Fortunately a little comment on the PHP website provided a solution, as someone had given a function array_sum_values() which had buried in it the simple loop I needed (and confirmation that there was no in-built function to do this).

Unfortunately, even after a lot of refactoring and commenting of the code, the actual table creation part was still only “not so ugly it needed a rewrite” instead of “so beautiful its optimality is unquestionable”. There is presumably some way of iterating over the whole-blog array and creating an array which groups words of the same frequency. One could possibly extract a unique list of frequencies and then pull out an array of words for each of those frequencies. Instead, I generated an array of words for each frequency (which could then be automatically sorted and converted into a comma-separated string) on-the-fly while iterating through the whole-blog array (using the fact that I can sort the whole-blog array in order of frequency). This method has problems when dealing with the first word (because the processing loop has nothing to compare the first value with) and with the last word (because the loop doesn’t know it has reached the last word until it is too late). Still, if you excuse two rather ill-fitting if statements, the whole thing works quite well.

Here then is the page in action, and here is the code:

I should point out, however, that I have deliberately downgraded the security of this script (although the security implications are not serious), and if you wish to use this yourself, either don’t make it available publicly (you could use it to generate static HTML and host that) or review it to make sure you remove any unacceptable risks (mostly information disclosure). Of course, you are Free to add extra security and redistribute your improved version, following the GNU GPL. I include that link in the hope that it counts as “[receiving] a copy of the GNU General Public License along with this program”. How and why I chose to specify version 2 of the GPL will have to wait for another blog post.

How many times have I written hapax legomenon?