Mysql order by values in array

Question

I want to manually sort items from a table. The background is that I have different items listed grouped on item type and then sorted by the id so the article numbers are presented as

Item type 1 - 4109
Item type 1 - 1342
Item type 1 - 3193
Item type 2 - 6971
Item type 2 - 0484

The sql string is shown below.

$sql = "SELECT * FROM '".$table."' WHERE model = '".$model."' ORDER BY item, id ASC";

Instead of sorting the article numbers based on id ASC I want to sort them by a defined array like

$sortItem1 = [1342, 4109, 3193];
$sortItem2 = [0484, 6971];

The items shall be listed grouped on item and the article numbers shall be sorted as the arrays $sortItem1 and $sortItem2. The result should then be

    Item type 1 - 1342
    Item type 1 - 4109
    Item type 1 - 3193
    Item type 2 - 0484
    Item type 2 - 6971

I don't think the code below is possible but I want it to be sorted like

ORDER BY item, $sortItem1, $sortItem2

What is the best solution for this?

this code is **vulnerable** to **sql injection** please read https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and use **prepared statements with parameters**. — nbk, Dec 11 '21 at 10:20

score 0 · Answer 1 · answered Dec 11 '21 at 20:53

I think I have to use usort instead. My idea is to save all parameters that I need like

    $totalItems = 0;
    $sortOrder = ['4109', '1342', '3193', '6971', '0484'];
    while ($row = mysqli_fetch_array($result))
    {
        $itemsToSort[$totalItems][0] = $row['item'];
        $itemsToSort[$totalItems][1] = $row['articleNumber'];
        $totalItems++;
    }

and then sort the array

    usort($itemsToSort, function($a, $b) use ($sortOrder){
    $valA = array_search($a[0], $sortOrder);
    $valB = array_search($b[0], $sortOrder);
    if ($valA === false)
        return -1;
    if ($valB === false)
        return 0;
    if ($valA > $valB)
        return 1;
    if ($valA < $valB)
        return -1;
    return 0;
    });

Then I can loop through the $itemsToSort.

This seems to work but I need to have several arrays for $sortOrder. The $sortOrder shall get its value from other arrays that can be named like $sort1, $sort2, $sort3 etc. Is it possible to copy the $sort1 array to $sortOrder?

score -1 · Answer 2 · answered Dec 11 '21 at 10:19

-1

Use FIELD() function in ORDER clause ORDER BY FIELD(item, 1342, 4109, 3193, 0484, 6971)

answered Dec 11 '21 at 10:19

Aleksey Vaganov

487
3
9

Is it possible to put arrays in the FIELD() instead of the numbers? I need to define the numbers in external arrays and then let the function follow that. – jigga Dec 11 '21 at 10:28
The MySQL does not have an array data type. See https://dev.mysql.com/doc/refman/8.0/en/data-types.html – Aleksey Vaganov Dec 11 '21 at 11:49

Mysql order by values in array

2 Answers2