Mysql to JSON, how to get a nested array?

Question

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.

try to: $data[] = implode($rowData); instead of array_values — Alex
– Alex, Commented Jun 21, 2016 at 13:58

Arnial · Accepted Answer · 2016-06-21 14:41:17Z

2

There are several problems in your code

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.
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).
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.

Mysql extension is deprecated its better to use Mysqli or PDO.
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.
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);

answered Jun 21, 2016 at 14:41

Arnial

1,4611 gold badge11 silver badges10 bronze badges

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

2 Comments

Adam Over a year ago

Actually there's still problems. It seems like it doesn't select distinct values WHERE Disease = $theDisease. And also $data[] just keeps growing. So the JSON looks like this now:

[ 	["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"]], ]

I tried to change $theDisease to $theDisease[] and then looping it through a for-loop, although that messed up the array structure.

Adam Over a year ago

Got it to work now, I added unset($data) after $mergedData at the end of the while-loop.

simon.ro · Accepted Answer · 2016-06-21 14:37:18Z

1

Your inner select fails, because of

WHERE Disease = '".$rowDisease."'")

$rowDisease is an array. Try:

WHERE Disease = '".$rowDisease['Disease']."'")

And @Alex is right, your $data keeps growing, you have to reset it $data = [], before fetching more data from the inner query.

And btw, I'm sure, you'll find a way to fetch that data in one single query, and bring it into the desired array structure later.

answered Jun 21, 2016 at 14:37

simon.ro

3,3503 gold badges25 silver badges45 bronze badges

Collectives™ on Stack Overflow

Mysql to JSON, how to get a nested array?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related