Queries for the number of users seen each day

Question

The below code returns an array filled with the number of users last seen for each day for $dayCount number of days that is specified via a parameter into the function. It does this by executing a database query for each day from the previous day going back $dayCount amount of days, and another function produces from this data to provide an output in CSV-style formatting for a graph.

This essentially means that if, say, I wanted the number of users last seen on each day for the last thirty days, a total of 30 database queries will be run.

To me this seems incredibly inefficient, but I can't think of a way around this myself. Can anything be done to make this more efficient?

function GetUserCountFromDays($dayCount) {
 $arrCnt = array();
 $baseQuery;
 $result;
 for ($day = $dayCount; $day >= 1; $day--) {
 $baseQuery = "SELECT * FROM example.servers WHERE last_seen > 
 UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -" . $day . " DAY))";
 $result = ConnectToMySQL()->query($baseQuery);
 if ($result){
 array_push($arrCnt, $result->num_rows);
 }
 }
 return $arrCnt;
}

• BeetleJuice: It's been over a week and you haven't replied or chosen an answer, so I wonder: was my response helpful?
• AStopher: @Patrick Apologies; I haven't yet had the opportunity to test your answer until now. It works flawlessly :-).

Answer 1

You're right, you should avoid querying in a loop as much as you can. If $dayCount were 30, I would run a query like:

SELECT 
 DATEDIFF(CURRENT_DATE,FROM_UNIXTIME(last_seen)) as inactive_days,
 COUNT(*) AS num_rows
FROM example.servers
WHERE UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 30 DAY)) <= last_seen
GROUP BY inactive_days
ORDER BY inactive_days ASC

This should give you an array of rows, each row would have inactive_days holding the number of days between today and last seen day, and num_rows holding the number of records that were grouped into that row. The result set is also ordered with the fewest inactive days first. Your PHP script could then use that result as needed. If you fetch the array and call it $result, you could do

foreach($result as $row):
 echo "$row[num_rows] users where last seen $row[inactive_days] days ago";
endforeach;

Quick notes:

1. Be sure to test! I may have a bug in my code, but you get the gist. If you find a bug, leave a comment asking me to fix it.
2. Don't do anything you don't understand. Take the time to learn what each line of code does, so you can get better, catch errors and even improve upon it.
3. If the order of the results doesn't matter to your logic, don't use the ORDER BY clause; sorting is computationally expensive and should only be used when needed