Software Technology Tips

This one is a very interesting and tricky tip. In my recent project I faced a problem  executing a query to find all the records from a table in a given order.

The Problem 

In MySQL we can order the records by - desc , asc or in RAND() to a particular field or for multiple fields. But what if my field name is "id_record" which is auto incremented number ( primary ) and I want to run a query to get records whose "id_record" are  1,2,3,4,5,6,7,8,9,10 or 11 or have a similar unconventional pattern ?:)

The Solution

SELECT * FROM `tablename`  WHERE  `id_record` IN (1,2,3,4,5,6,7,8,9,10,11) ORDER BY FIELD (`id_record`,5,4,6,3,2,1,7,9,8,10,11 )

As you can see from the code above ,we are using one more MySQL keyword called FIELD and inside the bracket we are passing the first parameter as the field name `id_record.`You can then order the records in the resultset.

Note : Do not put any space between FIELD and () , else it will throw error while executing this in PHP with mysql_query()