MySql按字段顺序上次上传在订购ID的前面获取

Question

I'm trying to order some datas and use ORDER BY FIELD. The order for the given id's looks well, but when i add another record in database this record just get in front of the ordered id's datas. When i add another record i want that record to be the last after the ordered ones. The code i use is below:

$result = mysqli_query($conn, 'SELECT * FROM echipa ORDER BY FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23)');

Answer 1

You can use an if statement in the order by:

SELECT * 
FROM echipa 
ORDER BY IF(FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23)=0,1,0),
    FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23)

The problem is field returns 0 for non matching records, which would return at the top of the list. This forces records not found to the bottom.