Show first and last record of each date

Question

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

CURRENT RESULT:

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 WISH YOU WOULD 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 shown in the above example of how I would like the result.

Answer 1

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 have the results in two columns, and you can display it like this:

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

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

There are some tricks that people do to convert at the time of the consultation, and formatting should be at the time of display only, but I will not go into 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 won’t be using conversion at search time, only on output.

See the formatting working on SQL FIDDLE

Answer 2

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

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

From a look at date and time functions mysql.