Highlight Data Filter

Question

I’m having a problem highlighting data from a filter. In other words, I filter and show many dates.

<?php
   include("conectar.php");

$quantidade = 1;
$pagina = (isset($_GET ['pagina'])) ? (int)$_GET['pagina'] : 1;
$inicio = ($quantidade * $pagina) - $quantidade;
$sql = "select * from tb_detalhe_trabalhador inner join tb_trabalhador on      tb_detalhe_trabalhador.id = tb_trabalhador.id inner join tb_equipamentos on  tb_detalhe_trabalhador.id = tb_equipamentos.id ORDER BY tb_trabalhador.id asc LIMIT $inicio,  $quantidade";
$qr = mysql_query($sql) or die(mysql_error());
    while($exibe = mysql_fetch_array($qr)){
    echo "<table>"; 

    echo  "<tr><td>Nome:</td>";
    echo "<td>".$exibe["Nome"]."</td></tr>";

    echo  "<tr><td>Morada:</td>"; echo "<td>";
    if ($exibe['Morada']){ echo $exibe['Morada'];}else{echo 'N/D';} echo "</td></tr>";

    echo "<tr><td>Tipo:</td>";echo"<td>";
    if($exibe['Tipo']){ echo $exibe['Tipo'];}else{echo 'N/D';} echo "</td></tr>";

    echo "<tr><td>Email:</td>"; echo "<td>";
    if($exibe['Email']){ echo $exibe['Email'];}else{echo 'N/D';} echo "</td></tr>";

    echo "<tr><td>Alvara Numero:</td>";echo"<td>";
    if($exibe['AlvaraNumero']){ echo $exibe['AlvaraNumero'];}else{echo 'N/D';} echo " </td></tr>";

     echo "<tr><td>Alvara Validade:</td>";echo"<td>";
     if($exibe['AlvaraValidade']){ echo $exibe['AlvaraValidade'];}else{echo 'N/D';} echo "</td></tr>";

The query is still quite large and would like to highlight all the dates that are outdated with a color or something. I can do this with PHP?

Answer 1

You will use PHP to check if the date has passed (you have not clarified where the date is to be checked, I am assuming it is in "Alvaravalidade"):

if (strtotime($exibe['AlvaraValidade']) < time()) {
    // Coloca uma cor diferente
}

Being specific to your case, putting the color Red on the date:

echo "<tr><td>Alvara Validade:</td>";
echo"<td>";
if ($exibe['AlvaraValidade']) { 
    if (strtotime($exibe['AlvaraValidade']) < time()) {
        echo '<span style="color:red">'.$exibe['AlvaraValidade'].'</span>';
    } else {
        echo $exibe['AlvaraValidade'];
    }
} else { 
    echo 'N/D';
} 
echo "</td></tr>";

There is one important detail: You will be using the strtotime() function to turn a string into timestamp. This string needs to be in the format that is usually in the database, which is for example: 2014-03-24 13:21:00 or only the date 2013-03-24, so it can compare to the current date that is obtained with time() in an integer timestamp. If the date is in Brazilian format for example, you need to change it to d-m-Y format

Answer 2

You can also put the conditional directly in the SQL query, which will abstract a little more your logic:

SQL:

SELECT 
    id, nome, morada, etc, IF (datacadastro < '2014-02-01', 'ultrapassada', '') AS ultrapassada
FROM
    tb_detalhe_trabalhador 
    INNER JOIN tb_trabalhador on tb_detalhe_trabalhador.id = tb_trabalhador.id 
    INNER JOIN tb_equipamentos on  tb_detalhe_trabalhador.id = tb_equipamentos.id

PHP:

while($exibe = mysql_fetch_array($qr)){
    echo("
        <table>
            <tr class='{$exibe['datacadastro']}' >
                <td >{$exibe['datacadastro']}</td>
                <td>{$exibe['morada']}</td>
                <td>{$exibe['nome']}</td>
            </tr>
        </table>"
    );
}

CSS:

tr.ultrapassada {
    background: red;
}

In this rustic example, all lines with dates before 2014/February will be highlighted in red.

Other options could be (in the database):

IF (DATEDIFF(NOW(), c.datacadastro) > 30, 'mais-de-30-dias', '') AS maisDe30
IF (DATEDIFF(NOW(), c.datacadastro) < 7, 'ultimos-7-dias', '') AS ultimos7dias

In addition, putting the conditionals in the database, the performance will be much faster than in PHP.