CODEWITHSUNDEEP
phpmysqlsql-order-by
Rwi
Rwi

Reputation: 137

MySql Order by Field last uploaded get first in front of ordered id's

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)');

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One solution where you don't have to repeat the expression is to convert the 0 to a NULL and then the NULL to something else:

ORDER BY COALESCE(NULLIF(FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23), 0), 9999)

Another is to use the fact that a descending sort puts NULLs last:

ORDER BY FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23) DESC

However, you might want to reverse the order of the list to keep the rows in the original order.

Or, if you really want to be fancy, you can do something like this:

ORDER BY - NULLIF(FIELD(id_profesor, 14,17,16,24,25,22,26,19,20,21,18,23), 0) DESC

I would consider the intention rather inscrutable in this case.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

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.

Upvotes: 0

Related Questions