How To: Exclude Words Like “An, A, The” From Alphabetized MySQL ORDER

When ordering lists of names or titles it’s sometimes desirable to exclude articles or other words from the order clause (eg. you want “The Burning Hell” to show up before “Great Lake Swimmers” in a list ordered by name). Early on in my career I must have assumed it was not possible and never bothered to look into again because I don’t recall ever ordering a list like this.
Anyways. Here’s how you do it:

SELECT name FROM artists ORDER BY TRIM( LEADING "a " FROM TRIM( LEADING "an " FROM TRIM( LEADING "the " FROM LOWER( name ) ) ) )

[thanks metafilter]

Even Google Makes Mistakes

This is an expert from a Gmail Blog Post re: the recent gmail outage:

Unexpected side effects of some new code that tries to keep data geographically close to its owner caused another data center in Europe to become overloaded, and that caused cascading problems from one data center to another. It took us about an hour to get it all back under control.

This sounds exactly like the types of bugs I create. The fact that Google makes these sorts of mistakes, even though they do a lot more testing and have bigger teams, etc; makes me feel good about my programming skills.

Revisiting Mobile Redirection Using .htaccess ReWrite Rules

Back in 2007 I wrote a post detailing a method for detecting and redirecting mobile browsers using .htaccess. Since then the mobile landscape has changed quite a bit: iPhone and Android have been released, the PDA market is all but dead and phones are better in general.

My original post was based on a PHP script by Andy Moore. It’s worth noting that he has continued to update his script and now has a fancy little generator to help you create a PHP based redirect ruleset. Check out detectmobilebrowsers.mobi for more info.

His solution will probably work for a lot of applications, but there are 2 main reasons I prefer using Apache’s rewrite rules to redirect mobile browsers:
1) Application independent. By putting the rules in the root .htaccess file you can easily redirect traffic across multiple directories, independent of any webapp that might or might not be running in those directories.
2) Faster. Placing the rewrite rules in apache’s httpd.conf file is potentially faster, than running the regexp in a php script for every request.

Enough of that, here’s the updated ruleset.

# don't apply the rules if you're already in the mobile directory, infintine loop
# you'll want to test against the host if you're using a subdomain
RewriteCond %{REQUEST_URI} !^/mobiledirectoryhere/.*$
# if the browser accepts these mime-types, it's definitely mobile, or pretending to be
RewriteCond %{HTTP_ACCEPT} "text\/vnd\.wap\.wml|application\/vnd\.wap\.xhtml\+xml" [NC,OR]
# a bunch of user agent tests
RewriteCond %{HTTP_USER_AGENT} "sony|symbian|nokia|samsung|mobile|windows ce|epoc|opera" [NC,OR]
RewriteCond %{HTTP_USER_AGENT} "mini|nitro|j2me|midp-|cldc-|netfront|mot|up\.browser|up\.link|audiovox"[NC,OR]
RewriteCond %{HTTP_USER_AGENT} "blackberry|ericsson,|panasonic|philips|sanyo|sharp|sie-"[NC,OR]
RewriteCond %{HTTP_USER_AGENT} "portalmmm|blazer|avantgo|danger|palm|series60|palmsource|pocketpc"[NC,OR]
RewriteCond %{HTTP_USER_AGENT} "smartphone|rover|ipaq|au-mic,|alcatel|ericy|vodafone\/|wap1\.|wap2\.|iPhone|android"[NC]
# put your rewrite rules here


Text Version

As you can see, the user agent list is a fair bit less verbose. I’ve been testing the ruleset on a very busy site, based on over 250k mobile site hits this set of rules is causing a lot fewer false positives, while not missing any significant number of mobile browsers.

This is why webprogramming sucks…

Run-time languages don’t have compilers.