How to build a nested array using nested for using PHP

Question

I'm trying to build an array with this structure. I have all the data on one single intermediate table which contains all the keys (if you see the code below it will be better understood). Basically I need to get this structure of array because I want to build a tree view.

        10
        |---14
        |   |---17
        |   |   |---20
        |   |   |   |---11
        |   |   |   |---12
        |   |   |
        |   |   |---21
        |   |       |---11
        |   |       |---13
        |   |---18
        |       |---30
        |       |   |---11
        |       |   |---14
        |       |
        |       |---31
        |           |---15
        |           |---16
        |---15
            |---16
            |   |---40
            |   |   |---11
            |   |   |---12
            |   |
            |   |---41 
            |       |---13
            |       |---14
            |
            |---19
                |---42
                |   |---11
                |   |---12
                |
                |---43
                    |---13
                    |---14

Of which the logic is as follow

$country_id = 10;

$cities = DB::table('table_general')->select('city_id')->where('country_id', $country_id)->distinct()->get();

foreach($cities as $city){
    $departments = DB::table('table_general')->select('depart_id')->where('city_id', $city->id)
                                                                  ->where('country_id', $country_id)->distinct()->get();

    foreach($departments as $departament){
        $provinces = DB::table('table_general')->select('province_id')->where('depart_id', $departament->id)
                                                                      ->where('city_id', $city->id)
                                                                      ->where('country_id', $country_id)->distinct()->get();

        foreach($provinces as $province){
            $districts = DB::table('table_general')->select('district_id')->where('province_id', $province->id)
                                                                         ->where('depart_id', $departament->id)
                                                                         ->where('city_id', $city->id)
                                                                         ->where('country_id', $country_id)
                                                                         ->distinct()->get();
            foreach($districts as $district){
                // I've tried something like this to build the array
                // But isn't the correct way
                $array = array();

                $array["country"] = $country_id;
                $array["country"][]["city"][] = $city->id;
                $array = array("country" => $country_id, array("city" => $city->id));
            }                                                             
        }
    }
}

How should I build with the best way my arrays to build an array like the example above?

Answer 1

You're resetting the value of $array on each loop. You need to initialize it before anything happens, and then just add to each array as needed.

Also, it looks like you're using Laravel - if you use Eloquent Models, you can simplify the querying and make it way more efficient! The current setup will not scale well.

Try something like this:

$country_id = 10;
$finalArray = [];
$finalArray[$country_id] = [];


$cities = DB::table('table_general')->select('city_id')->where('country_id', $country_id)->distinct()->get();

foreach($cities as $city){
    $finalArray[$country_id][$city->city_id] = [];

    $departments = DB::table('table_general')->select('depart_id')->where('city_id', $city->id)
                                                                  ->where('country_id', $country_id)->distinct()->get();

    foreach($departments as $departament){
        $finalArray[$country_id][$city->city_id][$department->depart_id] = [];

        $provinces = DB::table('table_general')->select('province_id')->where('depart_id', $departament->id)
                                                                      ->where('city_id', $city->id)
                                                                      ->where('country_id', $country_id)->distinct()->get();

        foreach($provinces as $province){
            $finalArray[$country_id][$city->city_id][$department->depart_id][$province->province_id] = [];

            $districts = DB::table('table_general')->select('district_id')->where('province_id', $province->id)
                                                                         ->where('depart_id', $departament->id)
                                                                         ->where('city_id', $city->id)
                                                                         ->where('country_id', $country_id)
                                                                         ->distinct()->get();
            foreach($districts as $district){
                $finalArray[$country_id][$city->city_id][$department->depart_id][$province->province_id][] = $district->district_id;
            }                                                             
        }
    }
}

var_dump($finalArray);