Mysql order by values in array-CodePudding

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?

CodePudding user response：

You could try using order by field(column_name, val1, val2 ,....)

$sql = "SELECT * FROM '".$table."' WHERE model = '".$model."' 
    ORDER BY field(item, 1342, 4109, 3193 ), id ASC";

CodePudding user response：

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