I'm using foreach loops to access records in a nested array.

I need to nest 3 arrays (so the first array contains an array, which also contains an array). I'm having success with 2 arrays but I can't get 3 to work.

I had my code working with 2 arrays (which worked just fine) but I can't get 3 arrays to be nested.

This is the result that I want:

[
  {
    "site_id": "1",
    "user_plants": [
      {
        "user_plant_id": "1",
        "site_id": "1",
        "plant_id": "1",
        "plant_images": [
          {
            "plant_image_id": "1"
          },
          {
            "plant_image_id": "2"
          },
          {
            "plant_image_id": "3"
          },
        ]
      }
    ]
  }
]

My current code:

 $query = "SELECT A.site_id FROM sites A WHERE A.user_id='".$user_id."' GROUP BY A.site_id";
    $result = $this->conn->query($query);

    $json_response = array();
    $sites = array();
    if ($result-> num_rows > 0) {

        while ($item = $result->fetch_object())
            $sites[] = $item;

        foreach($sites as $item) {
            $row_array = (array)$item;
            $site_id = $item->site_id;

            $user_plants = "SELECT A.user_plant_id, A.site_id, A.plant_id FROM user_plants A RIGHT JOIN sites B ON A.site_id ='".$site_id."' 
            JOIN plants C ON A.plant_id = C.plant_id GROUP BY A.user_plant_id";
            $resultSet = $this->conn->query($user_plants);

            $user_plants = array();
            if ($resultSet-> num_rows > 0) {

                while ($item = $resultSet->fetch_object())
                    $user_plants[] = $item;

                foreach ($user_plants as $item) {
                    $row_array['user_plants'][] = (array)$item;

                    $plant_id = $item->plant_id;
                    $user_plant_id = $item->user_plant_id;


                    $plant_images = "SELECT A.plant_image_id FROM plants_images A WHERE A.plant_id ='".$plant_id."' UNION SELECT B.plant_image_id FROM user_plant_image B JOIN user_plants C ON B.user_plant_id ='".$user_plant_id."' WHERE C.user_id ='".$user_id."' GROUP BY B.plant_image_id ORDER BY plant_image_id";
                    $resultSet = $this->conn->query($plant_images);

                    $plant_images = array();
                    if ($resultSet->num_rows > 0) {

                        while ($item = $resultSet->fetch_object())
                            $plant_images[] = $item;


                        foreach ($plant_images as $item) {

                            $row_array['user_plants'][]['plant_images'][] = $item;
                        }

                    } else if ($resultSet->num_rows == 0) {
                        $row_array['plant_images'] = [];
                    }
                }

                $json_response[] = $row_array;
            }
            
        }
        
    }

    return $json_response;

The result of above code:

[
  {
    "site_id": "1",
    "user_plants": [
      {
        "user_plant_id": "1",
        "site_id": "1",
        "plant_id": "1"
      },
      {
      "plant_images": [
          {
            "plant_image_id": "1"
          },
          {
            "plant_image_id": "2"
          },
          {
            "plant_image_id": "3"
          },
        ]
      }
    ]
  }
]

How should I adjust the foreach loops above to cater for this?

CodePudding user response：

There's plenty of room for improvement in this code but I've ignored that and tried to keep the code matching yours in this example.

The main changes are:

• Create a temporary variable $user_plant_array which we store "plant_images" against
• Push that temporary variable to the $site_array at the end of the loop
• Rename some loop variables to making it easier to identify what you're referencing

$json_response = array();
$sites = array();
if ($result->num_rows > 0) {

    while ($site = $result->fetch_object()) {
        $sites[] = $site;
    }

    foreach ($sites as $site) {
        $site_array = (array)$site;
        $site_id = $site->site_id;

        $user_plants = "SELECT A.user_plant_id, A.site_id, A.plant_id FROM user_plants A RIGHT JOIN sites B ON A.site_id ='" . $site_id . "' 
            JOIN plants C ON A.plant_id = C.plant_id GROUP BY A.user_plant_id";
        $resultSet = $this->conn->query($user_plants);

        $user_plants = array();
        if ($resultSet->num_rows > 0) {

            while ($user_plant = $resultSet->fetch_object())
                $user_plants[] = $user_plant;

            foreach ($user_plants as $user_plant) {
                // create a temporary variable here that we will map
                // all "plant_images" to
                $user_plant_array = (array)$user_plant;

                $plant_id = $user_plant->plant_id;
                $user_plant_id = $user_plant->user_plant_id;


                $plant_images = "SELECT A.plant_image_id FROM plants_images A WHERE A.plant_id ='" . $plant_id . "' UNION SELECT B.plant_image_id FROM user_plant_image B JOIN user_plants C ON B.user_plant_id ='" . $user_plant_id . "' WHERE C.user_id ='" . $user_id . "' GROUP BY B.plant_image_id ORDER BY plant_image_id";
                $resultSet = $this->conn->query($plant_images);

                $plant_images = array();
                if ($resultSet->num_rows > 0) {

                    while ($plant_image = $resultSet->fetch_object())
                        $plant_images[] = $plant_image;


                    foreach ($plant_images as $plant_image) {
                        $user_plant_array['plant_images'][] = $plant_image;
                    }
                    
                } else if ($resultSet->num_rows == 0) {
                    $user_plant_array['plant_images'] = [];
                }
                
                // the temporary variable now contains all "plant_images"
                // now we can push that to the site array
                $site_array['user_plants'][] = $user_plant_array;
            }

            $json_response[] = $site_array;
        }
    }
}

return $json_response;