Ok, probably not the best title for this article, but it's Monday, so you'll just have to make due. Hopefully, it will make sense by the end of this entry and you will have walked away with a neat trick to hide up your sleeve.

When working with insert language here and MySQL, have you ever had a collected list of record ids that needed the rest of their corresponding record data? Probably. If you've been doing insert language here and MySQL development for more than a few months, it's likely you've done something like the following:

PHP:

$ids = array(33, 22, 545, 234, 6, 343, 99);

$result = $this->db->query
("
    SELECT
        `id`,
        `name`,
        `email`,
        `password`
    FROM `members`
    WHERE `id` IN (" . implode(',', $ids) . ")
    ORDER BY `id` DESC
");

In this example, the above should return the corresponding data assigned to each id provided, if the record exists, that is. The results are sorted by id in descending order.

Well, that's great, but what if you want the database to return the data in the exact order you requested it? Not possible by any normal means outside of using insert language here to manually sort the returned data for you.

Lucky for us, the big swiging dicks over at MySQL have added an indispensable tool to the massive list of the database server's intrinsic functions. Let me introduce to you a little secret; the FIELD() function. Using the FIELD() function in place of the column name within the ORDER BY clause, you can force MySQL to return the data in the order you requested.

Using a modified version of the example above:

PHP:

$ids = array(33, 22, 545, 234, 6, 343, 99);

$result = $this->db->query
("
    SELECT
        `id`,
        `name`,
        `email`,
        `password`
    FROM `members`
    WHERE `id` IN (" . implode(',', $ids) . ")
    ORDER BY FIELD(id, " . implode(',', $ids) . ")
");

Et viola! All the requested records are returned in the same order as the ids within the $ids array. How neat is that? I wish I knew about this a few years ago.

Well, that's it for this article. Enjoy.

Loading mentions Retweet