Creating nested JSON using PHP MySQL

Question

I have an SQL Query returning certain fields, I am using json_encode() to get the data in JSON format, however I am having trouble getting it in the format I want.

PHP Code

<?php

function data() {
    $runDistanceBasedOnCityQuery = "SELECT rc.id, rc.cityId, c.cityName, rc.runId, r.distance, rc.status FROM run_city rc INNER JOIN cities c ON c.id = rc.cityId INNER JOIN run_distance r ON r.id = rc.runId ORDER BY c.cityName";
    $runDistanceBasedOnCityResult = $db->prepare($runDistanceBasedOnCityQuery);
    $runDistanceBasedOnCityResult->bindParam(":cityId", $cityId, PDO::PARAM_INT);
    $runDistanceBasedOnCityResult->execute();
    $runDistanceBasedOnCityOutput = $runDistanceBasedOnCityResult->rowCount();
    if ($runDistanceBasedOnCityOutput > 0) {
        while ($runDistanceBasedOnCityRow = $runDistanceBasedOnCityResult->fetch(PDO::FETCH_ASSOC)) {
            $array1 = array($runDistanceBasedOnCityRow['runId'], $runDistanceBasedOnCityRow['distance'], $runDistanceBasedOnCityRow['status']);
            for ($i = 0; $i < sizeof($array1); $i++) {
                $array2 = array("id" => $runDistanceBasedOnCityRow['id'], "runId" => $runDistanceBasedOnCityRow['cityId'], $runDistanceBasedOnCityRow['cityName'] => $array1);
            }

            $finalResultRunDistanceBasedOnCity[] = $array2;
        }
        $responseRunDistanceBasedOnCity = $finalResultRunDistanceBasedOnCity;
    } else {
        $responseRunDistanceBasedOnCity = 'Runs not found';
    }

    $result = array("status" => true,
        "runsBasedOnCity" => $responseRunDistanceBasedOnCity
    );

    json($result);
}

function json($data) {
    header('Content-Type:application/json');
    if (is_array($data)) {
        echo json_encode($data);
    }
}
?>

The JSON format I am getting

"runsBasedOnCity": [
    {
        "id": "1",
        "runId": "1",
        "Bengaluru": [
            "2",
            "10k",
            "1"
        ]
    },
    {
        "id": "2",
        "runId": "1",
        "Bengaluru": [
            "1",
            "5k",
            "1"
        ]
    },
    {
        "id": "3",
        "runId": "1",
        "Bengaluru": [
            "5",
            "3k",
            "0"
        ]
    },
    {
        "id": "4",
        "runId": "2",
        "Chennai": [
            "1",
            "5k",
            "1"
        ]
    },
    {
        "id": "5",
        "runId": "2",
        "Chennai": [
            "2",
            "10k",
            "1"
        ]
    },
    {
        "id": "6",
        "runId": "2",
        "Chennai": [
            "4",
            "15k",
            "1"
        ]
    }
]

The Format I Require

"runsBasedOnCity": [
    {
        "id": "1",
        "cityId": "1",
        "Bengaluru": 
         [
            {
              runId : "2",
              distance : "10k",
              status : "1"
            },
            {
              runId : "1",
              distance: "5k",
              status : "1"
            },
            {
              runId : "5",
              distance : "3k",
              status : "0"
            }
        ]
     },
     {
        "id": "2",
        "cityId": "2",
        "Chennai": 
         [
            {
              runId : "1",
              distance : "5k",
              status : "1"
            },
            {
              runId : "2",
              distance: "10k",
              status : "1"
            },
            {
              runId : "4",
              distance : "15k",
              status : "1"
            }
        ]
     }

I am not able to figure out a better way of doing this, I am fairly new to this, do help me out. Thanks !

Do you really want the property (that contains the arrays with "runId" etc) to have different names for each city? That doesn't seem consistent and easy to use. Wouldn't it be better to have cityName as a property and have something like runs: [...] on each? Then the json will be much easier to use. — M. Eriksson
– M. Eriksson, Commented Mar 27, 2018 at 6:24
@MagnusEriksson Yes I require that, its needed to populate drop-downs. — hushie
– hushie, Commented Mar 27, 2018 at 6:40
@hushie Chennai doesn't have any id values of 2, it has 4, 5, and 6. I think your expected result is flawed, or do you want to generate new id values for each city starting from 1? Or if the general goal is to group the city runs, perhaps you don't need to store the ids at all. — mickmackusa
– mickmackusa ♦, Commented Mar 27, 2018 at 7:21
@mickmackusa yes I noticed that, I have changed it in my working code . I have added id for each run in the cityName array. — hushie
– hushie, Commented Mar 27, 2018 at 7:36

mickmackusa · Accepted Answer · 2018-03-27 07:54:39Z

To efficiently group the subarray data, you should implement temporary keys. cityId is a suitable value to group by -- because cityNames may intentionally duplicate in the future but cityId must never un/intentionally duplicate in your database table.

When each new cityId is encountered, the conditional isset() call will determine whether a new/full set of data should be stored, or if data should merely be appended to the subarray.

I am calling array_slice() since it cuts down on unnecessary syntax / code-bloat.

After iterating through all of the rows, you can reindex the $result array, nest it inside runBasedOnCity, and add the status element.

I'll show my demo with PRETTY_PRINT so that it is easier to read, but in your actual code, you should remove the parameter. Also, a word of advice -- try to keep your variable names brief for improved readability.

Code: (Demo)

$resultset = [
    ["id" => "1", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "2", "distance" => "10k", "status" => "1"],
    ["id" => "2", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "1", "distance" => "5k", "status" => "1"],
    ["id" => "3", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "5", "distance" => "3k", "status" => "0"],
    ["id" => "4", "cityId" => "2", "cityName" => "Chennai", "runId" => "1", "distance" => "5k", "status" => "1"],
    ["id" => "5", "cityId" => "2", "cityName" => "Chennai", "runId" => "2", "distance" => "10k", "status" => "1"],
    ["id" => "6", "cityId" => "2", "cityName" => "Chennai", "runId" => "4", "distance" => "15k", "status" => "1"]
];

foreach ($resultset as $row) {
    if (!isset($result[$row["cityId"]])) {
        $result[$row["cityId"]] = array("id" => $row["id"], "cityId" => $row["cityId"], $row["cityName"] => array(array_slice($row,-3)));
    } else {
        $result[$row['cityId']][$row["cityName"]][] = array_slice($row,-3);
    }
}

if (!isset($result)) {   // don't need to check rowCount() at all
    $result = 'Runs not found';
} else {
    $result = array_values($result);
}

$result = array("status" => true, "runsBasedOnCity" => $result);

var_export(json_encode($result, JSON_PRETTY_PRINT));

Output:

'{
    "status": true,
    "runsBasedOnCity": [
        {
            "id": "1",
            "cityId": "1",
            "Bengaluru": [
                {
                    "runId": "2",
                    "distance": "10k",
                    "status": "1"
                },
                {
                    "runId": "1",
                    "distance": "5k",
                    "status": "1"
                },
                {
                    "runId": "5",
                    "distance": "3k",
                    "status": "0"
                }
            ]
        },
        {
            "id": "4",
            "cityId": "2",
            "Chennai": [
                {
                    "runId": "1",
                    "distance": "5k",
                    "status": "1"
                },
                {
                    "runId": "2",
                    "distance": "10k",
                    "status": "1"
                },
                {
                    "runId": "4",
                    "distance": "15k",
                    "status": "1"
                }
            ]
        }
    ]
}'

After explaining how you wanted to preserve the id values in the subarrays, here is that solution:

Code: (Demo)

foreach ($resultset as $row) {
    if (!isset($result[$row["cityId"]])) {
        $result[$row["cityId"]] = array("cityId" => $row["cityId"], $row["cityName"] => array(array("id" => $row["id"])+array_slice($row,-3)));
    } else {
        $result[$row['cityId']][$row["cityName"]][] = array("id" => $row["id"])+array_slice($row,-3);
    }
}

if (!isset($result)) {   // don't need to check rowCount() at all
    $result = 'Runs not found';
} else {
    $result = array_values($result);
}

$result = array("status" => true, "runsBasedOnCity" => $result);
var_export(json_encode($result, JSON_PRETTY_PRINT));

@hushie here is a cleaner method. It took me a while to code up because you didn't provide a ready-to-use input array.
@hushie I have added a second code block which stores your data as intended (with id values in the subarrays).
Thanks ! :) This works perfectly and is very clear too ! also the explanation you provided helps a lot.

Collectives™ on Stack Overflow

Creating nested JSON using PHP MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related