Sorting data in MySQL can be really easy if you're just trying to sort alphabetically or numerically. All you have to do is slap in the ORDER BY clause and list out the columns in order of importance. For example:

SELECT * FROM `animals` ORDER BY `color` DESC,`name`

But, what if you want to do something more complicated? Like sorting the animals by the lightness of their color? That's where ORDER BY FIELD can come in handy. You can use it to specify the exact order the values should be sorted by. Check this out:

SELECT * FROM `animals` ORDER BY FIELD(`color`,'light blue','blue','dark blue'),`name`

Still, that doesn't quite cover all the bases. What if you want to show all the light blue and blue animals first (sorted by name) followed by all the dark blue animals? You could do two separate queries and then UNION them together, but that creates it's own set of issues... Or you can try this cool trick involving CASE that I figured out the other day:

SELECT *,CASE WHEN `color` IN ('light blue','blue') THEN '1' WHEN `color` ='dark blue' THEN '2' END AS `customsort` FROM `animals` ORDER BY `customsort`,`name`

Using the CASE statement, you create a temporary column that you can then sort by. Of course, this example is really simple but you can get much more complex with the queries. Hope this helps save you some time. And, if you have any similar tricks, please paste them in the comments below.

I added a couple new code snippets to the Code section today, including a function that act's like PHP's explode(), but separates every character instead of requiring a delimeter.  You can achieve the same thing using PHP's split() and RegEx, but this is simple and works...

PHP Explode Each Character Function

If you've ever tried to run a shell script as superuser in Linux by using the syntax sudo ./example-shell-script, you'll know that doesn't work. Instead, this is how you need to do it:

1. Create your script
   vi example-shell-script
2. Change the permissions
   chmod 755 example-shell-script
3. Switch into superuser mode
   sudo su -
4. Run the script
   ./example-shell-script
5. Log out of superuser mode
   exit

This will be a quick post because I've been slammed with work lately, but I just wanted to mention something alarming that I discovered the other day. As I started using Bluefish Editor, I started noticing all sorts of extra files on my server with a ~ at the end of the filename (index.php~ as an example). So, I tried typing one of those into my browser and, to my alarm, found that my server was displaying the raw PHP code! Of course, this is a huge security risk. So, here's how I fixed it:

1. I disabled the auto-backup feature in the editor. In Bluefish, this was just an option in the preferences menu. Note: I have no idea why they would have this as a default setting. It makes no sense to me. Anyway...
2. I deleted all of those extra files I could find.
3. I modified the .htaccess file to prevent any of them that I missed in the cleanup from being displayed. By adding this code to your highest level .htaccess, you'll now get a forbidden 403 when attempting to access:
   <Files ~ "~$">
  Order allow,deny
  Deny from all
</Files>

Hope that helps someone out there.

The great thing about the internet is that it allows people to disseminate massive amounts of information at amazingly cheap prices.  This is also one of the worst things about the internet...  Because it's so cheap to send out information, there are a lot of people out there spewing out pure garbage on a colossal scale.

Everyone knows about spam email and how annoying it is. But, really, it's not that big of a deal. Be careful who you give your address to, change addresses often, and delete the random junk messages that do make their way in.

Spam's ugly online cousin, spam-bots, can be much more difficult. Of course, not all bots are bad. Without them, we wouldn't have search engines or aggregated news feeds. But, even those useful bots can do a lot of damage. Looking over my access logs and reporting, I find that bots are consistently around 50% of the traffic to all of my sites. That means that 50% of my server load is spent delivering pages to non-humans.

And then, even worse, there are the evil little bots sent out by the same jerks who think sending out bulk spam emails qualifies as a fun hobby. The thing that was good about them in the past was that they were so dumb you could trick 'em real easy. Throw anything in a JavaScript tag and suddenly they don't see it. Or just do the most basic of keyword filtering and knock out virtually all the spam comments on a blog (for some reason, they all seem to talk about viagra, porn, and online degrees a lot). Lately, though, I've seen some alarmingly smart little guys trolling out there.

I had an Ajax script being used for cost-per-click displays of phone numbers and got a huge wave of impressions on it from the same IP in a really short time. I figured it was some prankster, but when I investigated, I found that it was GoogleBot. Of course, this isn't spam, but still it was alarming to see that a spider was out there hitting links that were only available by running a JavaScript function. So, clearly, I'm going to have to do a little re-thinking there and also start worrying about nefarious bots picking up JS capabilities too.

Then, today, I got a notification from WordPress.com that someone had posted a follow-up to one of my comments on another blog. I went back to the post and was very surprised to see my comment reposted again below the original with my name and a link to some spammy site (as my URL). That's impressive. And scary. Now, it's going to take more than a casual glance to figure out which comments are real and which are not. And, it'll probably result in some legitimate comments getting blocked and visa versa.

So, moral of the story is, this isn't the nineties anymore and you can't just assume bots are stupid like they used to be. Watch out.