Mysql Codeigniter Active Record - How do I do a where_in query and return the correct order of results?
To order the result by the order in your array, you can do the following:
$array_of_ordered_ids = array(4,5,2,6);
As you already know the order of the numbers, you can use the Mysql FIELD()
Docs function:
ORDER BY FIELD(id, 4, 5, 2, 6);
To create such a string, you can use implode
Docs:
$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));
Give it a try:
$array_of_ordered_ids = array(4,5,2,6);$this->db->where_in('id', $array_of_ordered_ids);$order = sprintf('FIELD(id, %s)', implode(', ', $array_of_ordered_ids));$this->db->order_by($order);
As all answers I found on SO where just semi correct but all gave good hints I implemented successfully the code to retrieve set of rows correct ordered by the order in given array.
To Produce a sql like this:
SELECT * FROM (`product`) WHERE `id` IN (2, 34, 234) ORDER BY FIELD(`id`, 2, 34, 234)
use this code, while $ids contains the array (2, 34, 234).
// select from ... $this->db->where_in('id',$ids);$this->db->_protect_identifiers = FALSE; // stop CI adding backticks$order = sprintf('FIELD(id, %s)', implode(', ', $ids));$this->db->order_by($order);$this->db->_protect_identifiers = TRUE; // switch on again for security reasons// get...
see also here: http://ellislab.com/forums/viewthread/137157/#1058666
Thank for biggest solution.
$orde_num_string = implode(",",$order_num); $this->db->where_in("cir_order.order_num",$order_num); $this->db->_protect_identifiers = FALSE; $this ->db->order_by("FIELD(cir_order.order_num, $orde_num_string)"); $this->db->_protect_identifiers = TRUE;