Get SQL data individually for days between now and 30 days ago

I’m building an analytics chart where I need to display the number of visits for each day, for the past 30 days. I’ve tried using the loop below, but it fails to pull certain data and drastically slows down page load time, due to 30 queries being sent in one request.

Are there any better ways to get this data from the MySQL database while saving server resources and improving the load time of the page? Any help is appreciated.

              $conn = new mysqli("localhost", "db_host", "db_pwd", "db_name");
  
              $d2 = date('c', strtotime('-29 days'));

              $date = date("Y-m-d");

              $begin = new DateTime($d2);
              $end = new DateTime($date);
              $end = $end->modify('+1 day');

              $interval = new DateInterval('P1D');
              $daterange = new DatePeriod($begin, $interval ,$end);

              foreach($daterange as $date){

                $current = $date->format("Y-m-d");

                $sql = "SELECT COUNT(*) FROM stats WHERE link_id=$_GET[id] AND date='$current'";
                $result = mysqli_query($conn, $sql);
                if (mysqli_num_rows($result) > 0) {
                  while($row = mysqli_fetch_assoc($result)) { echo $row["COUNT(*)"]; }
                } else { echo "no results";}
              }

              $conn->close();        

Answer

If you have at least one visit per day, you can just use aggregation:

select date(dt) as dt_day, count(*) as cnt_visits
from stats 
where link_id = ? and dt >= current_date - interval 29 day
group by date(dt)
order by dt_day

Having a column called date is somehow misleading (although MySQL allows it). For the sake of clarity, I renamed the column to dt in the query.

If there are days without visits, and you still want to display them, with a count of 0 visits, then it is a bit more tricky. You need to generate the dates first, then left join the table. You would typically use a calendar table – or, in MySQL 8.0, you can do this on the fly with a recursive common table expression:

with recursive cte as (
    select current_date as dt_day
    union all
    select dt_day - interval 1 day from cte where dt_day > current_date - interval 29 day
)
select c.dt_day, count(s.dt) as cnt_visits
from cte c
left join stats s 
    on  s.link_id = ? 
    and s.dt >= c.dt_day 
    and s.dt < c.dt_day + interval 1 day
group by c.dt_day
order by c.dt_day