Need help turning an associative array into nested JSON using PHP

Question

I've been struggling with this for the past week and it's driving me crazy so if anyone could help me out I'd be forever grateful.

After querying my database I iterate through the data with:

while ($row=mysqli_fetch_assoc($result)) {...

This is how the rows are constructed:

Example row 1:

(
"countryId" => "2",
"countryDescription" => "Canada",
"cityId" => "3",
"cityDescription" => "Montreal",
"restaurantFranchiseId" => "2",
"restaurantFranchiseDescription" => "Kentucky Fried Chicken"
)

Example row 2:

(
"countryId" => "2",
"countryDescription" => "Canada",
"cityId" => "3",
"cityDescription" => "Montreal",
"restaurantFranchiseId" => "3",
"restaurantFranchiseDescription" => "Taco Bell"
)

Notice that only the restaurant franchise differs in the two rows above. The country and the city are the same in both rows.

I want to turn the rows in to a nested JSON-file like the one below. As you can see below, each country is a unique object. Each city is an unique object and a child element of it's corresponding country object. The restaurant franchises however aren't unique, since they aren't tied to a specific country or city.

How can create the JSON-file below from my data, which is structured as described above?

THANKS!!!

{
"Countries": [{
    "countryId": "1",
    "countryDescription": "USA",
    "cities": [{
        "cityId": "1",
        "cityDescription": "Houston",
        "restaurantFranchises": [{
            "restaurantFranchiseId": "1",
            "restaurantFranchiseDescription": "Mc Donald's"
        }, {
            "restaurantFranchiseId": "2",
            "restaurantFranchiseDescription": "Kentucky Fried Chicken"
        }, {
            "restaurantFranchiseId": "4",
            "restaurantFranchiseDescription": "Pizza Hut"
        }]
    }, {
        "cityId": "2",
        "cityDescription": "New york",
        "restaurantFranchises": [{
            "restaurantFranchiseId": "1",
            "restaurantFranchiseDescription": "Mc Donald's"
        }, {
            "restaurantFranchiseId": "4",
            "restaurantFranchiseDescription": "Pizza Hut"
        }]
    }]
}, {
    "countryId": "2",
    "countryDescription": "Canada",
    "cities": [{
        "cityId": "3",
        "cityDescription": "Montreal",
        "restaurantFranchises": [{
            "restaurantFranchiseId": "1",
            "restaurantFranchiseDescription": "Mc Donald's"
        }, {
            "restaurantFranchiseId": "3",
            "restaurantFranchiseDescription": "Taco Bell"
        }, {
            "restaurantFranchiseId": "4",
            "restaurantFranchiseDescription": "Pizza Hut"
        }]
    }, {
        "cityId": "4",
        "cityDescription": "Ottawa",
        "restaurantFranchises": [{
            "restaurantFranchiseId": "2",
            "restaurantFranchiseDescription": "Kentucky Fried Chicken"
        }, {
            "restaurantFranchiseId": "3",
            "restaurantFranchiseDescription": "Taco Bell"
        }, {
            "restaurantFranchiseId": "4",
            "restaurantFranchiseDescription": "Pizza Hut"
        }]
    }]
}]

}

Answer 1

You could use this code:

$result = [];
$lastCity = [ "cityId" => null ];
$lastCountry = [ "countryId" => null ];
while ($row=mysqli_fetch_assoc($result)) {
    if ($row["countryId"] !== $lastCountry["countryId"]) {
        // Country is not the same as in previous row, so create
        // a new entry for it in the first level of the result array. 
        // The city and franchises data will be inserted further down.
        $result[] = [
            "countryId" => $row["countryId"],
            "countryDescription" => $row["countryDescription"],
            "cities" => []
        ];
        // Get a reference (`&`) to the new country entry added to `$result`.
        // Whatever is later changed in `$lastCountry` will change inside the
        // `$result` data structure.
        $lastCountry = &$result[count($result)-1];
    }
    if ($row["cityId"] !== $lastCity["cityId"]) {
        // City is not the same as in previous row, so create
        // a new entry for it in the second level of `$result`.
        // We use the `$lastCountry` "shortcut" to manipulate `$result`.
        $lastCountry["cities"][] = [
            "cityId" => $row["cityId"],
            "cityDescription" => $row["cityDescription"],
            "restaurantFranchises" => []
        ];
        // Get a reference (`&`) to the new city entry added to `$result`.
        // Whatever is later changed in `$lastCity` will change inside the
        // `$result` data structure (and `$lastCountry`).
        $lastCity = &$lastCountry["cities"][count($lastCountry["cities"])-1];
    }
    // Create a new entry for the franchise in the third level of `$result`.
    // We use the `$lastCity` "shortcut" to manipulate `$result`.
    $lastCity["restaurantFranchises"][] = [
        "restaurantFranchiseId" => $row["restaurantFranchiseId"],
        "restaurantFranchiseDescription" => $row["restaurantFranchiseDescription"],
    ];
}

See it run on eval.in.

About the helper variables

The two variables $lastCity and $lastCountry are references to locations in the $result data structure (except at the start of the loop, when they are dummy values). To get such references inside the $result array, the & operator is used. It could be done without these two variables, but it would make the assignment statements quite long, as you would need to reference each time the last element in the $result array, get from that element the last element in its cities array, ...etc.

Assumptions

This algorithm needs your query result set to be ordered by country and city, i.e. a city should not first be "New York", then "Los Angeles" and then "New York" again.

Also, it is assumed that cityId values are unique. For example, a city in the US should not have the same cityId as a city in Canada. If this is the case, then the above code should be slightly adapted.