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"
        }]
    }]
}]

}

Could you please give me an array of your raw data : [{row},...,{row}] to test my code. — Ismail RBOUH
– Ismail RBOUH, Commented Jul 3, 2016 at 10:37

trincot · Accepted Answer · 2016-07-03 17:42:43Z

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.

edited Jul 3, 2016 at 17:42

answered Jul 3, 2016 at 10:56

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rawland Hustle Over a year ago

I just applied your code to my real data and it works perfect! I can not thank you enough @trincot! I sincerely appreciate the fact that you took your time to help me out with this.

Rawland Hustle Over a year ago

I'm going to study your code and try to understand what it's doing. There are some things in there which are totally new to me. I also have to make sure that my sql sorts the data by country and city.

trincot Over a year ago

I added some comments in the code, which might help to understand it. I also added a link to the PHP docs concerning the & operator.

Collectives™ on Stack Overflow

Need help turning an associative array into nested JSON using PHP

1 Answer 1

About the helper variables

Assumptions

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

About the helper variables

Assumptions

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related