PHP array to the right JSON format?

Question

I've made a script to track visitors on my website and now I want to display the amount of visitors per day in a Google Line Chart with JSON.

This is some PHP code I wrote to display the date and amount of visitors for that day in an array:

$dateQuery = $db->prepare("SELECT date FROM tracked"); $dateQuery->execute(); $rows = $dateQuery->fetchAll(PDO::FETCH_ASSOC); $array = array(); foreach ( $rows as $row ) { $day = explode(' ', $row['date']); if ( !key_exists($day[0], $array) ) { $array[$day[0]] = 0; } $array[$day[0]] += 1; }

This is the output when I print_r the array:

array output

Now for this output to work in the Google Charts API I need the JSON format to be like this:

[["2016-02-18",6],["2016-02-17",5]]

Instead I get this with json_encode($array):

{"2016-02-17":5,"2016-02-18":6}

I have a basic understanding now of arrays and loops but I can't seem to think of a solution to solve my problem.

Don't use the date as key. Make an array which consists of the date and visitor count. — Charlotte Dunois
– Charlotte Dunois, Commented Feb 18, 2016 at 9:10

Sergey Vidusov · Accepted Answer · 2016-02-18 10:37:16Z

You can easily optimize your query in the first place, making your data look exactly like you need without unnecessary loops later:

$dateQuery = $db->prepare("SELECT DATE(date) AS date, COUNT(tracked.id) AS count FROM tracked GROUP BY DATE(date)"); $dateQuery->execute(); $rows = $dateQuery->fetchAll(PDO::FETCH_ASSOC);

(I don't know if your table has an "id" column, but I'm sure you get the idea). And then it's as easy as

$result = array(); foreach ( $rows as $row ) { $result[] = array($row['date'], $row['count']); } echo json_encode($result);

That is some very convenient code, but now it echo's something like this: ["2016-02-17","1"],["2016-02-17","1"]. The visitors needs to be grouped by date. What is the solution?
I've modified my answer, for what it's now worth. Had to be grouped by DATE(date) instead of simply date.

Kordi · Accepted Answer · 2016-02-18 09:32:27Z

Just wrote some simple code to convert your array.

<?php $array = []; $array['2016-01-17'] = 7; $array['2016-02-18'] = 6; echo json_encode($array); // output is: {"2016-01-17":7,"2016-02-18":6} $arrayInRightFormat = []; foreach ($array as $i => $value) { $arrayInRightFormat[] = [$i, $value]; } echo json_encode($arrayInRightFormat); // output is: [["2016-01-17",7],["2016-02-18",6]]

Defacto you use your Array is like a HashMap with String keys so its converted to a dict.

Better Idea is to let the mysql Database aggregate the data, and then convert it directly to the right format!

<?php $dateQuery = $db->prepare("SELECT DATE(date) as day,count(*) as hits FROM tracked GROUP BY DATE(date)"); $dateQuery->execute(); $rows = $dateQuery->fetchAll(PDO::FETCH_ASSOC); $array = array(); foreach ( $rows as $row ) { $array[] = [$row['day'], $row['hits']]; } echo json_encode($array); // output is: [["2016-01-17",7],["2016-02-18",6]]

Awesome! It did the trick. Unfortunately the Google Charts API now thinks my date is a string. Do you know any solution to that? It only accepts something like this: [new Date(2016, 2, 17), 5] (5 being the visitors count).
You can just convert it, via javascript: new Date('2011-04-11') does the trick. If you array names ChartArray in js: for (i=0;i < chartArray.length; ++i) { chartArray[i][0] = new Date(chartArray[i][0]) }

Collectives™ on Stack Overflow

PHP array to the right JSON format?

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related