具有特定日期的行数

Question

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.

Answer 1

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

This works fine. All thanks.