MySQL to JSON, trouble while creating a nested array.

[dqjqb]

I'm trying to get a JSON-file looking like this:

[
    [
    'Disease', [ latitude, longitude, magnitude, latitude, longitude, magnitude, ... ]
    ],
    [
    'Disease', [ latitude, longitude, magnitude, latitude, longitude, magnitude, ... ]
    ]
];

This is the current code:

$sqlDisease = mysql_query("SELECT DISTINCT Disease FROM DiseaseData") or die(mysql_error());
 
$result = [];
while($rowDisease = mysql_fetch_assoc($sqlDisease)){
        $theDisease = $rowDisease['Disease'];
        $sqlData = mysql_query("
        SELECT lat, lng, magnitude 
        FROM DiseaseData 
        WHERE Disease = '".mysql_real_escape_string($theDisease)."'") 
        or die(mysql_error());
        $data = [];
        while($rowData = mysql_fetch_row($sqlData)){
            $data[] = $rowData;
        }
        $mergedData = call_user_func_array( 'array_merge', $data );
        $result[] =  [$theDisease, $mergedData];
}
 
$json = json_encode($result);
$file = 'testRes.json';
file_put_contents($file, $json);

I get a JSON-file in the right format, but there's two problems. It seems like WHERE Disease = '".mysql_real_e scape_string($t heDisease)."' do not select distinct values and $data[] keeps growing.

Here's a sample how it looks with current code:

[ 
["Malaria", ["40.555", "-122.222", "0.5", "85.111", "-33.4444", "0.7"]], 
["HIV", ["40.555", "-122.222", "0.5", "85.111", "-33.4444", "0.7",
"40.555", "-122.222", "0.5", "85.111", "-33.4444", "0.7"]] 
]

[Dormilich]

https://www.sitepoint.com/community/...alues/228037/6