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.

Over the weekend, I posted three new code snippets:

The Image Upload Form has everything you need to create a simple HTML form allowing users to upload their own JPEG images. After it verifies that the image is valid, it saves it and generates a thumbnail using the PHP GD library of image functions. The code is clean, so it's pretty easy to manipulate it to suit your needs (other file formats, different resizing, etc.)

The Cron Job Tester is the result of an epic battle I had with the server hosting one of my client's sites. I couldn't figure out why these cron jobs weren't running and so constructed this little tester to simply log an entry in a text file every minute. From there, I was eventually able to find out that the host's "Easy Cron" function was overwriting my crontab file. Arrrgh...

Finally, I posted the PHP code to Copy All the Rows in a MySQL Table to another, identical MySQL table. You can, of course, do this easily in phpMyAdmin, but I needed to have the function automated for this particular project. Hopefully you can get some use out of it too.

I've been trying to juggle a million different projects lately and haven't had much of a chance to write up here. I wish I could just pause time for a month or two and get caught up on this gigantic checklist of tasks that I want to get done on my sites.

One of the bigger tasks on the list is switching all the Lantenengo Industries stuff over to Ink Plant (I'm going to be officially changing my business name in the near future for branding reasons). Tonight, I got a decent-sized chunk of that switch taken care of: I closed down my old blog (redirecting it to here, of course) and pulled out the code snippets that I had published there, creating the new Code section of this site.

I also published a new code snippet that generates the PHP to display a MySQL table automatically. I've probably written this out by hand a couple hundred times, so it'll be nice to finally have this little shortcut. Hopefully some of you will benefit from it too. Enjoy!