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
– Nagaraj Tantri, Commented Jan 6, 2014 at 10:58

cocco · Accepted Answer · 2014-01-06 11:07:07Z

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 6, 2014 at 11:07

cocco

16.8k7 gold badges65 silver badges77 bronze badges

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

Comments

Krish R · Accepted Answer · 2014-01-06 11:10:07Z

1

Can you try this,

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

edited Jan 6, 2014 at 11:10

answered Jan 6, 2014 at 10:57

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

2 Comments

user3165037 Over a year ago

This returns {"label":"Windows 7 x86","data":"12"}. The AAA os seems to have disappeared.

Butt4cak3 Over a year ago

I think you meant $count[]['label] instead of $count['label'][].

Pakspul · Accepted Answer · 2014-01-06 11:08:16Z

0

$outputData = array();

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

return json_encode($outputData);

answered Jan 6, 2014 at 11:08

Pakspul

6486 silver badges17 bronze badges

2 Comments

Pakspul Over a year ago

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?

Roopendra Over a year ago

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.

Collectives™ on Stack Overflow

Transforming MySQL array to json array

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related