Sort by amount of days missing to win

Question

I have this script in my code:

$sql = "SELECT * FROM os WHERE status2 <> 'Fechado' ORDER BY XXXXXXX ";
$resultado = mysql_query($sql) or die ("Erro na consulta");
while ($linha = mysql_fetch_assoc($resultado)) {

$vencimento = $linha["vencimento"];

$a = explode("-","$vencimento");
$b = explode("-","$hoje");
$antiga= mktime(0, 0, 0, $b[1], $b[2], $b[0]);
$atual= mktime(0, 0, 0, $a[1], $a[2], $a[0]);
$diferenca= $atual-$antiga;
$dias = floor($diferenca/84600);

echo $dias = (int) $dias; 
}

Where it returns how many days are left to the x due. My Doubt is how to Sort query in Mysql by record with nearest due dates.

Example below:

 Sendo Hoje - dia 10/01

 data_entrada | vencimento |      Dias

     01/01    |    05/01   |    - 5 "Dias Atrasado"
     01/01    |    11/01   |      1 "Para Vencer"
     01/01    |    12/01   |      2 "Para Vencer"
     01/01    |    13/01   |      3 "Para Vencer"
     01/01    |    14/01   |      4 "Para Vencer"

Answer 1

You don’t have to sort by the amount of days to win, mainly because you don’t even filter out just what you’re winning. An ordering by the due date is sufficient. Due date or number of days to expire will produce the same order. You don’t give a lot of information but I can infer something from what you used and it would look like this:

SELECT * FROM os WHERE status2 <> 'Fechado' ORDER BY vencimento

I put in the Github for future reference.

The rest of the code should not be affected, despite using a very weird logic that can be improved or even removed if you prefer to make a query that gives you the results as you wish.

Answer 2

The field vencimento is the due date of the invoice, correct?

So the logic of your code gets like this:

$sql = "
    SELECT *, DATEDIFF(vencimento, CURDATE()) dias_para_vencimento
    FROM os
    WHERE status2 <> 'Fechado'
    AND dias_para_vencimento >= 0
    ORDER BY dias_para_vencimento";
$resultado = mysql_query($sql) or die ("Erro na consulta");
while ($linha = mysql_fetch_assoc($resultado)) {
    $dias = $linha['dias_para_vencimento'];
    echo $dias . '<br>';
}

PS: I just can’t remember now if DATEDIFF(vencimento, CURDATE()) returns a positive value or not. If it is negative, just change the order of the arguments. :)