Group query result set data and convert to JSON for Highcharts scatter plot

I am trying to get the right JSON output for my Highcharts scatter plot.

JSON output that I want:

[{
        "name": "Female",
        "color": "red",
        "data": [{
            "name": "Anna",
            "x": 161.2,
            "y": 51.6
        }, {
            "name": "Clair",
            "x": 167.5,
            "y": 59.0
        }]
    },
    {
        "name": "Male",
        "color": "blue",
        "data": [{
            "name": "James",
            "x": 174.0,
            "y": 65.6
        }]
    },
    {
        "name": "Peet",
        "color": "black",
        "data": [{
            "name": "Peet",
            "x": 175.3,
            "y": 71.8
        }]
    }
]

data.php

<?php

$arr    = array();
$arr1   = array();
$arr2   = array();
$result = array();

$sql = "SELECT `gender`, `name`, `length`, `weight`
            FROM `highcharts_data`
            WHERE `gender` = 'Female'";
$q   = mysqli_query($mysqli,$sql);

while ($row = mysqli_fetch_assoc($q)) {
    $arr['name'] = 'Female';
    $arr['data'] = array([(float)$row['length'], (float)$row['weight']]);
    $arr['color'] = 'red';

    array_push($result,$arr);
}

$sql1 = "SELECT `gender`, `name`, `length`, `weight`
         FROM `highcharts_data`
         WHERE `gender` = 'Male' AND `name` != 'Peet'";
$q1  = mysqli_query($mysqli,$sql1);

while ($row = mysqli_fetch_assoc($q1)) {
    $arr1['name'] = 'Male';
    $arr1['data'] = array([(float)$row['length'], (float)$row['weight']]);
    $arr1['color'] = 'blue';

    array_push($result,$arr1);
}

$sql2 = "SELECT `gender`, `name`, `length`, `weight`
         FROM `highcharts_data`
         WHERE `gender` = 'Male' AND `name` = 'Peet'";
$q2  = mysqli_query($mysqli,$sql2);

while ($row = mysqli_fetch_assoc($q2)) {
    $arr2['name'] = 'Peet';
    $arr2['data'] = array([(float)$row['length'], (float)$row['weight']]);
    $arr2['color'] = 'black';

    array_push($result,$arr2);
}

print json_encode($result, JSON_NUMERIC_CHECK);
mysqli_close($mysqli);
?>

JSON output that I get:

[{
    "name": "Female",
    "data": [
        [161.2, 51.6]
    ],
    "color": "red"
}, {
    "name": "Female",
    "data": [
        [167.5, 59]
    ],
    "color": "red"
}, {
    "name": "Male",
    "data": [
        [174, 65.6]
    ],
    "color": "blue"
}, {
    "name": "Peet",
    "data": [
        [175.3, 71.8]
    ],
    "color": "black"
}]

Can someone tell me how to change the data.php file, so I get the correct JSON output?

Here is a working example of how you can format the PHP into JSON. This does not include the mysql part, but that should not be hard to figure out.

Updated 2017/10/12, corrected mistake with double data:

Updated 2017/10/16, corrected mistake without array push:

<?php
$males   = array();
$malepersons = array();
$females = array();
$femalepersons = array();
$result  = array();

$values = [[
    "name" => 'Anna',
    "height" => 175,
    "weight" => 53.4,
    "gender" => 'Female'
    ],[
    "name" => 'Dan',
    "height" => 185.1,
    "weight" => 90.4,
    "gender" => 'Male'
    ] ];

//Simplified for loop
foreach ($values as $row){
    $person = array();
    $person['name'] = $row['name'];
    $person['x'] = $row['height'];
    $person['y'] = $row['weight'];

    if($row['gender'] == 'Male'){
        array_push($malepersons, $person);        
    } elseif($row['gender'] == 'Female') {
        array_push($femalepersons, $person);
    }
}

//took these out of the for loop, only need to be set once
$males['color'] = 'black'; 
$males['name'] = 'Male';
$males['data'] = $malepersons;
$females['color'] = 'green';
$females['name'] = 'Female';
$females['data'] = $femalepersons;


array_push($result, $males);
array_push($result, $females);
print json_encode($result, JSON_NUMERIC_CHECK);
?>

This will give you JSON that looks like this:

[{
  "color": "black",
  "name": "Male",
  "data": [{
    "name": "Dan",
    "x": 185.1,
    "y": 90.4
  }]
}, {
  "color": "green",
  "name": "Female",
  "data": [{
    "name": "Anna",
    "x": 175,
    "y": 53.4
  }]
}]

Which is what highcharts wants as series input.

In your case, the $values array is actually the data returned by SQL, you just need to change the loop to loop what you want it to loop.

Added working example: https://ideone.com/RPp3DO

PHP DOCS on keyed arrays: http://php.net/manual/en/language.types.array.php