Query in Mysql to return scheduled records for the next 30 days

Question

I have a table called AGENDA, where I have the fields, ID, Location, Date. Do the insertion and selection inside the database already managed to do correctly. But I can’t select the date in ascending order, showing the events that will happen first, and I wanted to restrict events to just the next 30 days. Have some command in php/mysql for that, or I would have to develop a function for that?

Solution after replies/comments:

Table structure:

id - Auto Increment

date - Date

Place - varchar

Code display.php:

<?php

include "configurar.inc";   

// Capturando o ano atual para quando for selecionar o mês dos eventos não aparecer de todos os anos. 

$dataatual = explode("/", date('d/m/Y'));
$anoatual = $dataatual[2];

// Supondo que quero o mês de maio (5) 

$sql = "SELECT * FROM datas WHERE MONTH(data) = 5 AND YEAR(data) = $anoatual ORDER BY data ASC";
$query = mysql_query($sql);

echo "Proximos Eventos: ";

while($linha = mysql_fetch_array($query)) {
    $datasemformato = $linha['data'];

    $dataformatada = implode("/", array_reverse(explode("-", $datasemformato)));

    echo "<br>";
    echo "$dataformatada";

    // Compara data e diz se o evento é hoje.

    if(date('d/m/Y')==$dataformatada) {
        echo " -> Esse evento é hoje.!";
    }

    // Compara a data e diz se o evento é amanhã.

    if(date('d/m/Y', strtotime("+1 day"))==$dataformatada) {
        echo " -> Esse evento será amanhã.!";
    }
}
  ?>

If you want to view events for the next 30 days: WHERE data between NOW() and DATE_ADD(NOW(), INTERVAL 1 MONTH)

Answer 1

To select the date in order you must use ORDER BY in your consultation.

To display a date of the next 30 days, you can use the command DATE_ADD mysql and add the 1 month interval ( or 30 days )

The consultation should be similar to that:

SELECT *
FROM AGENDA
WHERE data between NOW() and DATE_ADD(NOW(), INTERVAL 1 MONTH)
ORDER BY data

This way, the query should return the desired. You can place ORDER BY ASC/DESC date according to the sort ( ascending or descending ) you want to get.

Answer 2

I think this will help...

  $query = "SELECT nome, celular, profissao, cpf, rg,hora,faltou,codigo_convenio  
     FROM pacientes  
    INNER JOIN agenda  
    ON agenda.codigo_paciente=pacientes.codigo  
    WHERE (DATA = DATE_FORMAT(NOW(), '%Y-%m-%d'));";  
    $resultado = mysql_query($query,$conexao) or die(mysql_error());  


    if(mysql_num_rows($resultado)>0)  
    {  
    print "<table border='3' ALIGN='center'><FONT FACE='Arial' SIZE='10' COLOR='black'>";  
    print "<tr colspan='10' rowspan='10'><td id='celula0'>Hora</td><td id='celula1'>Paciente</td><td id='celula2'>Presente?</td><td id='celula3'>P.Saude</td><td id='celula4'>Tel</td><td id='celula2'>Histórico</td></tr>";  
    while ($info = mysql_fetch_array($resultado))  
                  {  

    print "<tr colspan='10' rowspan='10'><td id='celula0'>$info[hora]</td> <td id='celula1'>$info[nome]</td><td id='celula2'>$info[faltou]</td><td id='celula3'>$info[codigo_convenio]</td><td id='celula4'>$info[celular]</td><td id='celula4'>$info[celular]</td></tr>";  
           }  
    print "</table>";  
     }  
    else{  
    echo "Não há pacientes para hoje!";  


    }   

     ?>  

    </body>  
    </html>