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();
    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;
  • \$\begingroup\$ It's been over a week and you haven't replied or chosen an answer, so I wonder: was my response helpful? \$\endgroup\$ Commented Jun 28, 2016 at 12:22
  • \$\begingroup\$ @Patrick Apologies; I haven't yet had the opportunity to test your answer until now. It works flawlessly :-). \$\endgroup\$
    – AStopher
    Commented Jun 28, 2016 at 12:23

1 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:

    DATEDIFF(CURRENT_DATE,FROM_UNIXTIME(last_seen)) as inactive_days,
    COUNT(*) AS num_rows
FROM example.servers
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";

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.