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]

How To Round Unixtime To Midnight

This might be pretty obvious to anyone with basic math skills. It took me a few minutes to figure out, so I thought I’d share with the world. To round a unix timestamp to the previous midnight (UTC) use the following function:

function unixtime_round_to_midnight ($t) {
return $t – ($t%86400);
}

Thanks to Ian for pointing out my math ineptitude.

Top 5%

According to Kevin Rose (of digg and diggnation fame) I am in the top 5% of php developers. In the last episode of This Week in Tech, he mentioned that 95% of the resumes he receives for digg.com positions are from developers who haven’t even worked on a site that gets 1 million uniques, let alone anything near the 26 million digg gets.

I have!

*toot toot* (that’s the sound of my own horn)

TinyMy, Quick and Dirty MySQL Shell

Stumbled across a tiny mysql shell – appropriately named ‘tinymy’ – a few months ago.

It’s quite a useful little bit of PHP code. Very lightweight and function interface and shell for MySQL. I use it whenever I need to verify the contents of a database table or test a couple of queries but don’t need a full install phpMyAdmin.

Thought I’d share.
Link