Group SQL data by date column and count occurrences per date

I am trying to make some statistics over visits on my site. I know that I could use Google Analytics(which I do), but I want to try to do something myself and it is a good way to learn it.

Problem: I select dates in my database and sort them to fit this week. After that, I want to add them to a json file. That json file are used by CanvasJS to make a Chart. I have tried some different ways, just to get it simi-working. But the format of the json array, is not the one CanvasJS want.

What I need:

{ visits:[[2019-02-12, 49,],[2019-02-13,40,],[2019-02-14,46,],[2019-02-15,37,], [2019-02-16,31,],[2019-02-17,38,],[2019-02-18,4,] }

What I get:

{ "visits":{"2019-02-12":49,"2019-02-13":40,"2019-02-14":46,"2019-02-15":37,"2019-02-16":31,"2019-02-17":38,"2019-02-18":4} }

My PHP Script:

// Get first and last day of the current week
$first_day = date('Y-m-d', strtotime("- 6 days"));
$last_day = date('Y-m-d', strtotime("+ 1 days "));

// Return all results the past 7 days
$sql = "SELECT date FROM table WHERE date >= '" . $first_day . "' AND date < '" . $last_day . "'";
if($result = $conn->query($sql)){
    
    $response = array(); 
    $visits = array();

    while($row = $result->fetch_array(MYSQLI_ASSOC)){
        
        $old_date = $row['date'];
        $old_date_timestamp = strtotime($old_date);
        $new_date = date('Y-m-d', $old_date_timestamp);
        
                // I don't need the keys, but I cant avoid it to
                // get it to work....
        $visits[] = array(
            'date' => $new_date
        );
        
    }
    
    // Add sum of Dates
    $response['visits'] = array_count_values(array_column($visits, 'date'));
    
    // Save to json File
    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);
}
$conn->close();

Solution

Ignoring any quote-related issues you might think are problems (but aren't), it seems your main difference is between what you want...

[[2019-02-12, 49,],...

and what you have...

{"2019-02-12":49,...

This is because array_count_values() creates an associative array, with your dates as keys.

Your issue could be greatly simplified by getting your database to group and count instead of doing it in PHP. You can also benefit from using a prepared statement instead of direct value injection.

// Get first and last day of the current week
$first_day = date('Y-m-d', strtotime("- 6 days"));
$last_day = date('Y-m-d', strtotime("+ 1 days "));

$sql = <<<_SQL
SELECT DATE(`date`), COUNT(1)
FROM `table` WHERE `date` BETWEEN ? AND ?
GROUP BY DATE(`date`)
_SQL;

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $first_day, $last_day);
$stmt->execute();
$stmt->bind_result($date, $count);
while ($stmt->fetch()) {
    $visits[] = [$date, $count];
}
$response = [ 'visits' => $visits ];

// Save to json File
$fp = fopen('results.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);