Converting Query result to D3.js hierarchical JSON format

Question

I have a database result that I need to convert to the correct json format for D3.js to use. This the code so far, and the result

Database query result:

desti_cat       class_cat               area_cat                 totalcost
UK              Multi media services    Multi media services     80.361
UK              Other                   Service Number AC & SC   85.369
UK              Other                   Multimedia Services Sent 1.628
UK              Other                   Multimedia Services Recd 32.394
UK              Other                   Traffic Service          0.85
UK              Other                   #Charity Donation        5
UK              Other                   Unknown                  0.25
UK              Other                   Multimedia Service       4.113
Roaming         GPRS                    GPRS                     2574.926
Roaming         Multi media services    Multi media services     5.34
Roaming         SMS                     SMS                      105.9
Roaming         Voice                   Voice                    833.611
International   SMS                     SMS                      25
International   Voice                   Voice                    830.014

PHP:

foreach($result as $line)
{
    $desticat = $line['desti_cat'];
    $classcat = $line['class_cat'];
    $areacat = $line['area_cat'];
    $cost = $line['totalcost'];

    if($classcat != $areacat){
        $data[$desticat]['children'][$classcat]['children'][$areacat]['cost'] = $cost;
        $data[$desticat]['name'] = $desticat;
        $data[$desticat]['children'][$classcat]['name'] = $classcat;
        $data[$desticat]['children'][$classcat]['children'][$areacat]['name'] = $areacat;
    }else{
        $data[$desticat]['children'][$classcat]['cost'] = $cost;
        $data[$desticat]['name'] = $desticat;
        $data[$desticat]['children'][$classcat]['name'] = $classcat;
    }
}

Result after json_encode:

{
"UK":{
    "children":{
        "Multi media services":{
            "cost":80.361,
            "name":"Multi media services"
        },
        "Other":{
            "children":{
                "Service Number AC & SC":{
                    "cost":85.369,
                    "name":"Service Number AC & SC"
                },
                "Multimedia Services Sent":{
                    "cost":1.628,
                    "name":"Multimedia Services Sent"
                },
                "Multimedia Services Recd":{
                    "cost":32.394,
                    "name":"Multimedia Services Recd"
                },
                "Traffic Service":{
                    "cost":0.85,
                    "name":"Traffic Service"
                },
                "#Charity Donation":{
                    "cost":5,
                    "name":"#Charity Donation"
                },
                "Unknown":{
                    "cost":0.25,
                    "name":"Unknown"
                },
                "Multimedia Service":{
                    "cost":4.113,
                    "name":"Multimedia Service"
                }
            },
            "name":"Other"
        }
    },
    "name":"UK"
} ... Roaming ... International

This is clearly wrong, and I can see how it is wrong, but not why, or how to fix it. It needs to match the format of flare.js shown here https://bl.ocks.org/mbostock/4348373

Answer 1

For anyone that may come accross this issue in the future I have managed to solve it with the following code

function array_values_recursive($arr)
{
    $arr2=[];
    foreach ($arr as $key => $value)
    {
        //var_dump($key);
        if($key != 'children' && $key != 'name' && $key != 'size'){
            if(is_array($value))
            {            
                $arr2[] = array_values_recursive($value);
            }else{
                $arr2[] =  $value;
            }
        }else{
            if(is_array($value))
            {            
                $arr2[$key] = array_values_recursive($value);
            }else{
                $arr2[$key] =  $value;
            }
        }

    }

    return $arr2;
}

$data = [
    'name' => 'Spend'
];

foreach($result as $line)
{
    $desticat = $line['desti_cat'];
    $classcat = $line['class_cat'];
    $areacat = $line['area_cat'];
    $cost = $line['totalcost'];

    if($classcat != $areacat){
        $data['children'][$desticat]['name'] = $desticat;
        $data['children'][$desticat]['children'][$classcat]['name'] = $classcat;
        $data['children'][$desticat]['children'][$classcat]['children'][$areacat]['name'] = $areacat;
        $data['children'][$desticat]['children'][$classcat]['children'][$areacat]['size'] = $cost;
    }else{
        $data['children'][$desticat]['name'] = $desticat;
        $data['children'][$desticat]['children'][$classcat]['name'] = $classcat;
        $data['children'][$desticat]['children'][$classcat]['size'] = $cost;
    }
}

//return $result;

return array_values_recursive($data);