codeigniter fetch mysql table with where_in + keep order of key array in result array

Question

I have a string with image ids (fetched from another mysql table) and converted to an array:

$idstring = "12, 18, 3, 392, 0, 9, 44";
$idarray = explode(',', $idstring);

Based on this array of ids, I want get all the rows from my "media" mysql table.

$result = $this->db->select('*')
->from('media')
->where_in('id', $ids)
->get()->result_array();

The problem is the $result array's values are in a weird order like this:

$result's order : 44, 9 ,0 ,18 ,3 ,392 ,12 ...

But i need them to stay like in my $id string/array order...

I've tried 4 approaches to solve the issue so far:

Fetch rows in a loop without where_in() - what creates a lot of queries - but works for now ...
Reorder the $result array based on the order of the $idstring or the $idarray, though I could not manage to to find a working result and I don't get the point why this step is necessary at all
Try to get the query itself fixed. I've heard about ORDER_BY and FIND_IN_SET, $ids but I could not get it into my a working codeigniter query and don't know about the performance if this is really a help

So in conclusion, I think this should be a simple everyday task, i just want to fetch a bunch of pictures in a given order with codeigniter.

Am I missing a simple solution here?

score 5 · Answer 1 · answered Dec 13 '17 at 11:24

5

Use Field() function of mysql

$result = $this->db->select('*')
            ->from('media')
            ->where_in('id', $ids)
            ->order_by("FIELD(id,".join(',',$ids).")")
            ->get()
            ->result_array()

it should be something like

FIELD(id,12, 18, 3, 392, 0, 9, 44)

Reference

Field() returns the index position of a comma-delimited list

answered Dec 13 '17 at 11:24

M Khalid Junaid

63,861
10
90
118

`$idstring` can be used instead of `join(',',$idarray)` – Philipp Maurer Dec 13 '17 at 11:27
@PhilippMaurer i guess this `"FIELD(id,".$idstring.")"`will work if you want to do with `$idstring` – M Khalid Junaid Dec 13 '17 at 11:47
thanks alot philipp and m khalid junaid. i got it working - and learned something new. and my gut was right - there must be a simple way:) – scco Dec 13 '17 at 12:18

score 0 · Answer 2 · answered Dec 21 '19 at 03:15

The accepted answer should be M Khalid Junaid answer. But just in case, if you are generating from an array like I do, use it like this:

$filters = [
   "order_by" => [
       "title" => "DESC",
       ""FIELD(id,".join(',',$ids).")" => "" // The option should be empty...
   ]
];

foreach($filters["order_by"] as $attribute => $option){
    $this->db->order_by( $attribute, $option );
}

The output will be:

...
ORDER BY `name` DESC, FIELD(id, 2017, 2031, 2032, 2034, 2035)
LIMIT 50

Just to for clarification.

codeigniter fetch mysql table with where_in + keep order of key array in result array

2 Answers2