On one of our sites, Find Derby, I wanted a way to expand the listings so that categories with low numbers of entries would include listings from other towns. Since they include postcodes and we have a database to convert a given postcode to national grid coordinates, calculating which the nearest towns are and therefore creating the mysql query was easy.
It was only after I’d done it that I thought of a problem. Since I was using
... ORDER BY RAND()
it meant that listings from other towns were being listed before ones from that town. Ideally it should list them with that towns first and then those from other towns next.
Turns out that the solution was fairly simple using the FIELD function:
... ORDER BY FIELD(field_name,'town1','town2','town3');
Simple and useful!