Amit Sharma Amit Sharma - 3 months ago 12
MySQL Question

rearrangement of IN clause output

I have a

IN
clause query in which I pass this ID'S
(10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29)


My query is,
SELECT
post_jobs
.
id
FROM
post_jobs
WHERE
id
IN(10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29)

It execute correctly and returns result first id likes 1 then 4 then 7 and so on...that is ascending order

My Need is to get first result of id 10 then 19 then 23 as the order of ID that I passed is there any way to get such type of result in output.

Answer

Try this

SELECT post_jobs.id FROM post_jobs WHERE id 
IN(10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29) ORDER BY 
FIELD(id,10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29);

In Codeigniter

$this->db->_protect_identifiers = FALSE; 
$this->db->select('id'); 
$this->db->where_in('id',array(10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29));
$this->db->order_by('FIELD(id, 10,19,23,24,28,33,45,46,1,4,7,8,12,16,18,22,29)'); 
$this->db->get('post_jobs');

If you are using codeigniter 3 then write

$this->db->protect_identifiers(False);