Show first and last record of each date

Question

Show first and last record of each date

Navigation

#1 by (1 votes)
#2 by (-1 votes)

0

I would like to search the database for all records according to the "DOCTOR" between two dates DATA_INICIAL and DATA_FINAL Without repeating records of the day.

CURRENT RESULTS:

BRUNO BARBOSAS   02/04/2018 12:18   02/04/2018 12:18
BRUNO BARBOSAS   02/04/2018 12:22   02/04/2018 12:22
BRUNO BARBOSAS   09/04/2018 08:26   09/04/2018 08:26
BRUNO BARBOSAS   09/04/2018 08:30   09/04/2018 08:30

I WOULD LIKE TO JUST SHOW:

BRUNO BARBOSAS   02/04/2018 12:18   02/04/2018 12:22
BRUNO BARBOSAS   09/04/2018 08:26   09/04/2018 08:30

CODE:

$nome= $_POST['nome'];
$data_i = $_POST['data_inicio'];
$data_f = $_POST['data_fim'];

$consulta= "SELECT * FROM $tabela 
          WHERE medico = '$medico' 
          AND data >= '$data_i' 
          AND data <= '$data_f' ";

$resultado = mysqli_query($conn, $consulta);

while($rows_registro = mysqli_fetch_array($resultado)){
    echo $rows_registro['nome'];
    echo $rows_registro['data']; //Aqui tem que vir o PRIMEIRO registro do dia sem repetir
    echo $rows_registro['data']; //Aqui tem que vir o ULTIMO registro do dia sem repetir
}

After searching between the two dates I would like to show the first record and the last one for each DAY without repeating as the example above shows how you would like the result.

php data loop query mysqli

asked by anonymous 26.08.2018 / 04:18

2 answers

-1

One approach is to perform a query where you group by date.

SELECT count(*), DATE_FORMAT(timestamp, "%Y-%m-01") AS month FROM tabela GROUP BY month

Take a look at date and time functions > from MySQL.

26.08.2018 / 18:39

Android app does not connect to local webservice Visualization of the values of a vector in ModelSim

score 1 · Accepted Answer

Just one

SELECT
       MIN(data) AS primeiro,
       MAX(data) AS ultimo
FROM
       tabela
WHERE
       ... (condiçoes aqui)
GROUP BY
       DATE(data) -- extrai só a parte da data para agrupar por dia

See working on SQL FIDDLE

So you will have the results in two columns, and you can see this:

if ($rows_registro = mysqli_fetch_array($resultado)) {
    echo $medico;
    echo $rows_registro['primeiro'];
    echo $rows_registro['ultimo'];
}

Now, this is if the DB has the dates stored correctly. If it is not, you have to fix the DB.

There are some gambiarras that the staff does to convert at the time of the query, and that formatting should be at the time of the display only, but I will not go in this line here to avoid encouraging the creation of problems.

The date being in the right format, you can format for display like this:

DATE_FORMAT(MIN(data), '%d/%m/%Y %H:%i') AS primeiro

This way you will not be using conversion at the time of the search, just at the exit.

See the formatting working on SQL FIDDLE