MySQL to JSON, won't select distinct values

PHP
1

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_escape_string($theDisease).”’ do not select distinct values and $data[] keeps growing.

Here’s a sample how it looks:

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

maybe you are mistaking distinct with only the first of each key? i don’t see how this should work - you get a distinct list of values, to filter the complete list on each value, getting an equivalent of select * from table as a result? just get everything and group in the loop like

$sqlDisease = mysql_query("SELECT Disease, lat, lng, magnitude FROM DiseaseData") or die(mysql_error());
 
$result = [];
while($rowDisease = mysql_fetch_assoc($sqlDisease)){
		$results[$rowDisease['Disease']][] = $rowDisease['lat'];
		$results[$rowDisease['Disease']][] = $rowDisease['lng'];
		$results[$rowDisease['Disease']][] = $rowDisease['magnitude'];
		// $results[$rowDisease['Disease']][] = array($rowDisease['lat'], $rowDisease['lng'], $rowDisease['magnitude']); // would make more sense
}
3

sorry don’t know the answer to your question but you need to update to the latest mysql code and start using mysqli (note the ‘i’ at the end) at the very least. Not only will your code be safer, if you adopt prepared statements etc, but it will last longer. I think PHP7 does not support ‘mysql’ without the ‘i’ at all, so if you host chooses to upgrade your code will break.

4

We have already been though this. No host is just going to upgrade to a major version without first notifying customers. A host may offer servers with php 7 but not just go upgrade from 5 to 7 out of the blue. Not to mention you don’t know whether this site is on shared hosting, cloud, or a dedicated server. So really you’re kind of making a a mountain out of a mole hill.

That being said I agree that preparing the code base for the latest version of php is an important task to take on when/if time and budget allows. Though the role it has with solving your immediate problem is not critical.

5

just seemed like a good time to upgrade once the problem has been sorted it wouldn’t take a lot of effort to adjust to mysqli rather than close the file and leave it to possibly become a problem in the future. Having switched to prepared statements recently i find it’s second nature now just to do it that way instead and i don’t have to worry that i’ve forgotten to escape a variable or that when our host switches to php 7 (which they have suggested doing) i won’t have so much to check/change.

But yes sorry for derailing the OP

6

The amount of time it takes depends on several factors. What seems like a simple task can be a very time consuming and expensive one. For some legacy code bases it probably just isn’t worth it.

7

Well that would be a question for the OP. If they are just trying to get something working quickly, or just updating a script and don’t expect it to last a long time fair enough. If they are building it to become a huge information database over the next 5 years i’d suggest they do the work now.

The OP may or may not even know there is a newer way of doing it.

As for the problem at hand… is it not because on your second query where you collect the information about the disease you are starting an array and then not emptying it before starting the loop again so it is just adding it on.

If you put $data ='' after the loop does that fix it. I.e

.... 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]; //Now we clean the $data array to start again $data =''; }

Not saying this is a good way of doing it but interested to know if that cures the problem with this particular bit of code. @chorn 's solution is probably better as less lines of code.

8

I wouldn’t. I would suggest they go all in with a modern framework and ORM.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.