Return value grouped by date

Question

I have a table on Mysql, which stores my customers' confirmations on my website, having as fields: id | cod_cliente | data, the date being the main.

What I need is to group and return the result of the day.

Example:

From 05/10/2017 | 80 confirmations
Of 06/10/2017 | 100 confirmations

<?php

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT COUNT(*) AS `DATA` FROM `confirmacao` WHERE `ID_CLI` = 27001";
$result = $conn->query($sql);

$sql2 = "SELECT * FROM `confirmacao` WHERE `ID_CLI` = 27002 GROUP BY `DATA` ORDER BY `ID` DESC";
$result2 = $conn->query($sql2);

while($row2 = $result2->fetch_assoc()) {
    echo $row2["DATA"];
    echo "<br />";
}

while($row = $result->fetch_assoc()) {
    echo $row["DATA"];
    echo "<br />";
}

?>

But he doesn’t return in the right way. How do you do that? Thank you.

Answer 1

<?php

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT DATE_FORMAT(data, "%d/%m/%Y"), COUNT(*) FROM confirmacao WHERE cod_cliente = 27001 GROUP BY data ORDER BY id DESC";
$result = $conn->query($sql);


while($row2 = $result2->fetch_assoc()) {
    echo $row2["DATA"];
    echo "<br />";
}

while($row = $result->fetch_assoc()) {
    echo $row["DATA"];
    echo "<br />";
}

?>