Adam Dedanga Adam Dedanga - 5 months ago 25
JSON Question

Mysql to JSON, how to get a nested array?

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 what I've done:

<?php

$sqlDisease = mysql_query("SELECT DISTINCT Disease FROM DiseaseData") or die(mysql_error());

while($rowDisease = mysql_fetch_assoc($sqlDisease)){

$sqlData = mysql_query("
SELECT lat, lng, magnitude
FROM DiseaseData
WHERE Disease = '".$rowDisease."'")
or die(mysql_error());

while($rowData = mysql_fetch_assoc($sqlData)){
$data[] = array_values($rowData);
}

$result[] = array_values($rowDisease, $data);

}

$json = json_encode($result);
$file = 'testRes.json';
file_put_contents($file, $json);


I end up with a JSON-file looking like this:

[null,null,null]


The problem probably lies within the nested while-loops or array_values(), but I can't figure out exactly where.

Answer

There are several problems in your code

  1. mysql_fetch_assoc return an array. To use it in sqls where statement you need to get value from it. Otherwise it will be serialized to string "Array", which is not the disease that you looking for.

  2. array_values can take only 1. If you pass 2 parameters its dosn't extract any values (at least in php 5.6.21 it only print warning).

  3. In $data variable you have an array of arrays like this [[lat,lng,magnitude],[lat,lng,magnitude],...]. If you try to extract values from this array it will create new array which will be absolutely same as stored in $data. You need to merge all sub arrays. For exmaple like this call_user_func_array( 'array_merge', $data ); or by merging them in inner while loop.

Next problems more about your code style.

  1. Mysql extension is deprecated its better to use Mysqli or PDO.

  2. Mysql (also pdo and mysqli) has method that extract values as simple enumerated array. So you don't need to call array_values in inner loop.

  3. It's may be unsafe to pass string data (even if it comes from trusted source) into sql without escaping special symbols.

Try use this.

$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);