php - Mysql to JSON, how to get a nested array?
Get the solution ↓↓↓Solution:
There are several problems in your code
mysql_fetch_assoc
return an array. To use it in sqlswhere
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 thiscall_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);
Answer
Solution:
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.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: the requested url was not found on this server. xampp
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.