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

1

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);

edited Feb 18, 2016 at 10:37

answered Feb 18, 2016 at 9:18

Sergey Vidusov

1,3421 gold badge7 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ramon Over a year ago

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?

Sergey Vidusov Over a year ago

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

1

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]]

edited Feb 18, 2016 at 9:32

answered Feb 18, 2016 at 9:17

Kordi

2,4651 gold badge17 silver badges13 bronze badges

2 Comments

Ramon Over a year ago

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).

Kordi Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related