Sorting from an array-CodePudding

I got my data from other database, added it to an array and imploded it:

$userStr = implode("', '", $rows);

It is arranged like ('1024', '953','2034'), but when I try to query it and display the result:

$query = "SELECT * FROM books WHERE id in('$userStr') LIMIT $start_from, $limit";

$bookselect = mysqli_query($conn, $query);

The result will be displayed based on the arrangement of those values in ASC order.

('953','1024','2034')

Is there anyway to display the result based on its arrangement on the array?

CodePudding user response：

MySQL supports ORDER BY FIELD(...) expression exactly for your case:

SELECT * FROM books WHERE id in('$userStr') ORDER BY FIELD(id, '$userStr') LIMIT $start_from, $limit"

P.S. A side comment for your The result will be displayed based on the arrangement of those values in ASC order reply: if you don't specify any order, then MySQL does not guarantee any specific order. You may see ASC most of the time mostly because your MySQL query optimizer uses PRIMARY index for some internal operations. At the same time, you should never expect any specific order if you don't mention ORDER BY in your query.

CodePudding user response：

ORDER BY FIELD() should work in your case

$query = "SELECT *
FROM books
WHERE id in('$userStr')
ORDER BY FIELD(id, '$userStr')
LIMIT $start_from,$limit";

$bookselect = mysqli_query($conn, $query);

FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists. If there are values in your search result which are not part of $userStr then it will add that in the beginning of your result.