CODEWITHSUNDEEP
phpmysqljson
Adam
Adam

Reputation: 441

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.

Upvotes: 1

Views: 1040

Answers (2)

Arnial
Arnial

Reputation: 1441

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

Upvotes: 2

simon.ro
simon.ro

Reputation: 3302

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.

Upvotes: 1

Related Questions