Phoebe Phoebe - 9 days ago 6
JSON Question

Hierarchical data to array / json

I am trying to learn how to properly encode the results of a MySQL query as an array/JSON. I can do the basics, what I'm have difficulty with is getting a specific structure.

My data looks like this:

db_name file_type name value count
archive A send vid 338
archive A perm trrr 323
archive A mod 2016 339
archive B rate 32K 5414
archive B off Bleep 466
archive B level 1222 466


I would like to create an array that I can encode as JSON, looking like this:

{
"archive": {
"A": {
"send": {
"vid": 338
}
"perm": {
"trrr": 323
}
"mod": {
"2016": 339
},
"B": {
"rate": {
"32K": 5414
}
"off": {
"Bleep": 466
}
"level": {
"1222": 466
}
}
}


Any help or push in the right direction is appreciated. Thank you!

EDIT 2: Based on Farzad's answer. Getting close. I am trying to group on db_name, file_type, and name. Ideally this would be done in a way that results in no square brackets in my JSON output. In progress:

$result = mysql_query($myquery);

if ( ! $result ) {
echo mysql_error();
die;
};

while ($row = mysql_fetch_assoc($result)) {
$outp[$row["file_type"]][] = array(
$row["name"] => array($row["value"] => $row["count"])
);
}

$result = array('archive' => $outp);
echo json_encode($result);


EDIT 3 This did the trick:

while ($row = mysql_fetch_assoc($result)) {
$outp[$row["file_type"]][$row["name"]][] = array(
array($row["value"]=> $row["counts"])
);
}

$result = array('original' => $outp);

echo json_encode($result);

Answer

You need something like this :

Edit : I see you do some kind of group by too, add [] too! it will work, however, you need to put all "A" data in list

while ($result->fetch()) {
                        $outp[$file_type][] = array(
                                    $name => array($value=> $count)
                                );


                    }
$result = array('archive' => $outp);
echo json_encode($result);

this is a test

$file_type = 'type';
$name = 'name';
$count = 'cont';
$value = 'value';
$outp= array();
                        $outp[$file_type][] = array(
                                    $name => array($value=> $count)
                                );



echo json_encode($outp);