I am trying to make some statistics over visits on my site. I know that I could use Google Analytics(wich 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 dont 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();
Thanks to anyone able to help.