This pissed me off and I couldn't figure out what I was doing wrong. So I'm blogging about my ignorance. Imagine you're using Symfony and Doctrine to access a database. You are using prepared statements to prevent any SQL injection problems. There are two main ways of doing this - and they disagree about how positional variables should be specified. Data Retrieval And Manipulation Here's a fairly trivial SQL statement with a couple of variables: $sql = "SELECT `userID` FROM `users` WHERE…
Continue reading →
Our community website - OpenBenches - has over seventeen thousand crowd-sourced entries. The nature of user-generated content is that there are bound to be duplicates. Especially around popular walking routes. Here's how I culled around 200 duplicates using the awesome power of SOUNDEX! Soundex is a clever algorithm for reducing a string of characters into a string which roughly represents its pronunciation. The "roughly" is key here. We could just search the database for identical entries,…
Continue reading →
A troublesome plugin recently corrupted some of the avatars on my blog's comments. This is a quick HOWTO for regenerating them. Gravatars are based on the MD5 hash of a user's email. For some reason, the plugin had overwritten the avatar field with the text http://identicon This MySQL query finds all the comment IDs which have that dodgy text: SELECT comment_id FROM `wp_commentmeta` WHERE `meta_key` LIKE 'avatar' AND `meta_value` LIKE 'http://identicon' Using a SubQuery we can …
Continue reading →
My good friend Richard Brent has often complained that my blog has very little Shakespeare content. Despite the domain name, I don't think I've ever blogged about The Big S. For shame! Fear not, my Brentish-Boy, this post is all about Shakespeare. And MySQL.... Ahem... When I first started shkspr.mobi it was intended to be an easy way to get Shakespeare on your phone. At that time, there were no mobile formatted texts of his plays and sonnets, so I had to create them. Finding…
Continue reading →
