Transforming MySQL array to json array

Question

I'm trying to make a pie chart to display data of operating systems, and am counting the operating system of each individual client with the following SQL query

SELECT os, COUNT( * ) AS count FROM clients GROUP BY os

I then put them inside an array with the following PHP

$query->execute();
$count = array();
while($row = $query->fetch()){
 $currOS = $row['os'];
 $count[$currOS] = $row['count'];
}
return json_encode($count);

This outputs the following when json_encode'd:

{"AAA":"1","Windows 7 x86":"12"}

However, the pie chart javascript plugin requires the following markup

var data = [
 { label: "AAA", data: 50},
 { label: "Windews 7", data: 7},
 { label: "Windews XP", data: 8},
 { label: "Windows 8", data: 9}
];

What would be the correct PHP syntax for me to use?

Then you building it wrongly, you should be building an array of dictionaries that will have label and data as key and add values against it. — Nagaraj Tantri, Jan 06 '14 at 10:58

Krish R · Answer 1 · 2014-01-06T11:10:07.533

1

Can you try this,

    while($row = $query->fetch()){
        $currOS = $row['os'];
        $count[]['label'] = $currOS;
        $count[]['data'] = $row['count'];
    }

edited Jan 06 '14 at 11:10

answered Jan 06 '14 at 10:57

Krish R

22,583
7
50
59

This returns `{"label":"Windows 7 x86","data":"12"}`. The AAA os seems to have disappeared. – Jan 06 '14 at 10:59
I think you meant `$count[]['label]` instead of `$count['label'][]`. – Butt4cak3 Jan 06 '14 at 11:02

score 1 · Accepted Answer · answered Jan 06 '14 at 11:07

1

Use label & data as association

SELECT os AS label , COUNT( * ) AS data FROM clients GROUP BY os

and then use mysql_fetch_assoc

$data=array();
while($row=$query->fetch_assoc()){
 $data[]=$row;
}
return json_encode($data);

answered Jan 06 '14 at 11:07

cocco

16,442
7
62
77

score 0 · Answer 3 · answered Jan 06 '14 at 11:08

0

$outputData = array();

while($row = $query->fetch())
{
    $outputData[] = array(
        'label' => $row['os'],
        'data' => $row['count'],
    );
}

return json_encode($outputData);

answered Jan 06 '14 at 11:08

Pakspul

648
5
17

I'm new here, so I'm figure out how to give good answers, but you mean explaining what exacly there is wrong, and how to adjust the code to get that what the question owner wants? – Pakspul Jan 06 '14 at 11:42
No Nothing wrong in your answer.e.g. As you created `outputData` array and assign value into it. So this is the difference or correction from OP code. So if you explain these thing it will more better to understand to others as well. – Roopendra Jan 06 '14 at 11:47

Transforming MySQL array to json array

3 Answers3

Linked