具有特定日期的行数

There is a sql query that displays the number of rows created in the database for a certain date (I create a histogram by dates).

$timemoscow = date("d.m.Y", time());
$todaycount = mysqli_query($link, "select count(*) FROM logs WHERE time LIKE '%".$timemoscow."%'") or die(mysqli_error($link));
$todaycountres = mysqli_fetch_row($todaycount);

The histogram script looks like this.

$('#sparklinedash').sparkline([4, 5, 2, 4, 3, 6, 7], {
      type: 'bar',
      height: '30',
      barWidth: '4',
      resize: true,
      barSpacing: '5',
      barColor: '#7ace4c'
});

Strips in the histogram will be 7 pieces. Ie 7 days. I'm trying to deduce the number of rows from the database for yesterday, the day before yesterday, etc. days. But my code will look very great if I do it the way I thought to do.

$oneDay = date ("d.m.Y", strtotime ($timemoscow ."-1 days"));
$oneDaycount = mysqli_query($link, "select count(*) FROM logs WHERE time LIKE '%".$oneDay."%'") or die(mysqli_error($link));
$oneDaycountres = mysqli_fetch_row($oneDaycount);

$secondDay = date ("d.m.Y", strtotime ($timemoscow ."-2 days")); //and another days...

with an approximate output in Js:

$('#sparklinedash').sparkline([7, 6, 5, 4, 3, <?php echo $secondDaycountres[0]; ?>, <?php echo $oneDaycountres[0]; ?>], {
      type: 'bar',
      height: '30',
      barWidth: '4',
      resize: true,
      barSpacing: '5',
      barColor: '#7ace4c'
});

There will be too many requests, because such histograms three pieces of 7 days. How can this be done more reasonably and more compactly?

Thank you in advance for your cooperation.

select time, count(*) from rcc_logs group by STR_TO_DATE(time, '%d.%m.%Y')

This works fine. All thanks.

you need to use aggregation..

something like

select time, count(*) from logs group by time

which will then give you multiples rows each with the time and the count of rows withthe same value.

see https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count

you have to group by DATE

select DATE(time), count(*) FROM logs GROUP BY DATE(time)