CODEWITHSUNDEEP
phparraysjson
Ramon
Ramon

Reputation: 21

PHP array to the right JSON format?

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.

Upvotes: 1

Views: 75

Answers (2)

Sergey Vidusov
Sergey Vidusov

Reputation: 1342

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

Upvotes: 1

Kordi
Kordi

Reputation: 2465

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

Upvotes: 1

Related Questions