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

Question

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

Navigation

#1 by (6 votes)
#2 by (1 votes)

4

I have a table called AGENDA, where I have the fields, ID, Location, Date. Doing the insertion and selection within the database I've already been able to do correctly. But I can not select the date in ascending order, showing the events that will happen first, and I wanted to restrict events only in the next 30 days. Is there any command in php / mysql for this, or would I have to develop a function for this?

Solution after responses / comments:

Table structure:

id - Auto Increment

date - Date

Local - varchar

Display.php code:

<?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)

php mysql data

asked by anonymous 03.05.2014 / 04:22

2 answers

1

I think this helps ...

  $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>

02.05.2017 / 00:57

Include link in a container Format mask for CNPJ

score 6 · Accepted Answer

To select the date in order you should use ORDER BY in your query.

To show a date for the next 30 days, you can use the DATE_ADD from mySQL and add the 1-month (or 30-day) range

The query should look like this:

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

In this way, the query should return the desired one. You can put ORDER BY ASC / DESC data according to the sort order (ascending or descending) you want to get.