Sorting cars by year and registration with MySQL

Just published the updates to tjlobb.com

The sort order on the car listing page was tricky.

http://tjlobb.com/stocklist.php

The cars needed to be listed by type and within type by year, then registration and then price. The trick is that the registration is a mixture of letters and numbers (e.g. W, X, 05, 51) and simple MySQL "order desc" doesn't work.

Used the following nifty trick which might be useful if anyone else is doing a car sales webpage. Set up three fields regyear (int), regcode (varchar length 2) and price (float). Remembering to trim whitespace off regcode in case someone mistakingly enters some. Then MySQL does all the sorting.

SELECT ... ORDER BY regyear DESC, LENGTH(regcode) DESC, regcode DESC, price DESC

The LENGTH(regcode) ensures that 51 is higher in the list than 01 and both are higher than any letters.