The text on the homepage and the about-us for the photo archive has now been updated.
Bit of a fiddle to sort out the style sheets to make all the hyperlinks look consistent.
Friday 17 July 2009
Thursday 2 July 2009
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.
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.
Subscribe to:
Posts (Atom)