Bob Xplosion Bob Xplosion - 2 months ago 13
MySQL Question

Order Results with PHP array in Code Igniter

I have an array of numbers in PHP that represent primary keys("pro_id") in my DB that I send through my model in CodeIgniter to get only 10 specific rows.

This numbers might change but for now I'll use this to demonstrate my point.

This is the array:

Array ( [0] => 5 [1] => 3 [2] => 8 [3] => 12 [4] => 30 [5] => 29 [6] => 26 [7] => 28 [8] => 4 [9] => 7 )


And this is the model I send it through:

public function get_flores_home($array_select) {
$this->db->where_in('pro_id', $array_select);
return $query->result_array();
}


However, I would like to have my results sorted by the same order the array was in.

This is the result I'm currently getting:

Array(
[0] => Array(
[pro_id] => 3[pro_nombre] => Ashley[pro_descripcion] => 24RosasBaseDeCerámica[pro_precio] => 480[pro_es_disponible] => 1[pro_imagen] => 0edb4 - ashley . jpg
) [1] => Array(
[pro_id] => 4[pro_nombre] => Abril[pro_descripcion] => 6RosasRojas, LilisBlancas, Alstroemerias[pro_precio] => 320[pro_es_disponible] => 1[pro_imagen] => 3f9f1 - abril . jpg
) [2] => Array(
[pro_id] => 5[pro_nombre] => Amira[pro_descripcion] => 12Rosas . Alstroemerias . MallaBlanca . Moño . [pro_precio] => 280[pro_es_disponible] => 1[pro_imagen] => 81e6c - amira . jpg
) [3] => Array(
[pro_id] => 7[pro_nombre] => Betty[pro_descripcion] => 24Rosas . Alcatraces . Alstroemerias . Moño[pro_precio] => 650[pro_es_disponible] => 1[pro_imagen] => 57219 - betty . jpg
) [4] => Array(
[pro_id] => 8[pro_nombre] => Brittany[pro_descripcion] => 24RosasRojas . MallaElegante . Moño[pro_precio] => 450[pro_es_disponible] => 1[pro_imagen] => 942cf - brittany . jpg
) [5] => Array(
[pro_id] => 12[pro_nombre] => Diana[pro_descripcion] => 24Rosas . LilisBlancas . BaseDeCeramica . [pro_precio] => 490[pro_es_disponible] => 1[pro_imagen] => a9bb8 - diana . jpg
) [6] => Array(
[pro_id] => 26[pro_nombre] => Katy[pro_descripcion] => 48Rosas . LilisAmarillas . Lisianthusmorado . CampanitaDeIrlanda . [pro_precio] => 1200[pro_es_disponible] => 1[pro_imagen] => 478a6 - katy . jpg
) [7] => Array(
[pro_id] => 28[pro_nombre] => Lucia[pro_descripcion] => 24Rosas . 10Tulipanes . CampanitaDeIrlanda . Stargazer . BaseDeCeramica . [pro_precio] => 900[pro_es_disponible] => 1[pro_imagen] => f0829 - lucia . jpg
) [8] => Array(
[pro_id] => 29[pro_nombre] => Magdalena[pro_descripcion] => 24Rosas . LilisBlancas . BaseDeCeramica . [pro_precio] => 590[pro_es_disponible] => 1[pro_imagen] => 29403 - magdalena . jpg
) [9] => Array(
[pro_id] => 30[pro_nombre] => Marisol[pro_descripcion] => 24Rosas . 5Lilis . Alstroemerias . Canasta . [pro_precio] => 750[pro_es_disponible] => 1[pro_imagen] => 1eb20 - marisol . jpg
)
)


This is the array I want my result to be sorted like:

Array ( [0] => 5 [1] => 3 [2] => 8 [3] => 12 [4] => 30 [5] => 29 [6] => 26 [7] => 28 [8] => 4 [9] => 7 )


This is the Result in the desired order:

Array(
[0] => Array(
[pro_id] => 5[pro_nombre] => Amira[pro_descripcion] => 12Rosas . Alstroemerias . MallaBlanca . Moño . [pro_precio] => 280[pro_es_disponible] => 1[pro_imagen] => 81e6c - amira . jpg
) [1] => Array(
[pro_id] => 3[pro_nombre] => Ashley[pro_descripcion] => 24RosasBaseDeCerámica[pro_precio] => 480[pro_es_disponible] => 1[pro_imagen] => 0edb4 - ashley . jpg
) [2] => Array(
[pro_id] => 8[pro_nombre] => Brittany[pro_descripcion] => 24RosasRojas . MallaElegante . Moño[pro_precio] => 450[pro_es_disponible] => 1[pro_imagen] => 942cf - brittany . jpg
) [3] => Array(
[pro_id] => 12[pro_nombre] => Diana[pro_descripcion] => 24Rosas . LilisBlancas . BaseDeCeramica . [pro_precio] => 490[pro_es_disponible] => 1[pro_imagen] => a9bb8 - diana . jpg
) [4] => Array(
[pro_id] => 30[pro_nombre] => Marisol[pro_descripcion] => 24Rosas . 5Lilis . Alstroemerias . Canasta . [pro_precio] => 750[pro_es_disponible] => 1[pro_imagen] => 1eb20 - marisol . jpg
) [5] => Array(
[pro_id] => 29[pro_nombre] => Magdalena[pro_descripcion] => 24Rosas . LilisBlancas . BaseDeCeramica . [pro_precio] => 590[pro_es_disponible] => 1[pro_imagen] => 29403 - magdalena . jpg
) [6] => Array(
[pro_id] => 26[pro_nombre] => Katy[pro_descripcion] => 48Rosas . LilisAmarillas . Lisianthusmorado . CampanitaDeIrlanda . [pro_precio] => 1200[pro_es_disponible] => 1[pro_imagen] => 478a6 - katy . jpg
) [7] => Array(
[pro_id] => 28[pro_nombre] => Lucia[pro_descripcion] => 24Rosas . 10Tulipanes . CampanitaDeIrlanda . Stargazer . BaseDeCeramica . [pro_precio] => 900[pro_es_disponible] => 1[pro_imagen] => f0829 - lucia . jpg
) [8] => Array(
[pro_id] => 4[pro_nombre] => Abril[pro_descripcion] => 6RosasRojas, LilisBlancas, Alstroemerias[pro_precio] => 320[pro_es_disponible] => 1[pro_imagen] => 3f9f1 - abril . jpg
) [9] => Array(
[pro_id] => 7[pro_nombre] => Betty[pro_descripcion] => 24Rosas . Alcatraces . Alstroemerias . Moño[pro_precio] => 650[pro_es_disponible] => 1[pro_imagen] => 57219 - betty . jpg
)
)


As you can see, the order is now based on the same array I used in the "where_in" method. How can I achieve this?

Answer

Disclaimer: I don't know how to use CodeIgniter, but this is what you do if you want to order by some custom order such as yours:

SELECT * FROM your_table WHERE id IN 
(5, 3, 8, 12, 30, 29, 26, 28, 4, 7) 
ORDER BY FIELD(id, 5, 3, 8, 12, 30, 29, 26, 28, 4, 7)

Now, if you know how to adjust this in CodeIgniter so it produces a query like this - your problem is solved via MySQL.

Comments