Creating a multidimensional array in a hierarchical form

[elizabethkof]

So I am trying to list photo albums where each album contains individual photos. This is similar to categories where each category cotains posts.

I have two database tables, namely "albums" and "photos". 

The albums table has columns id, album_name, and album_description.

The photos table has columns id, photo_is_in, photo_name and photo_description.

 

I used INNER JOIN to combine  the albums and photos table to produce the flat $albums array below: 

 

<?php

$albums  = [
['album_id' => '1', 'photo_id' => '41', 'album_name' => 'album_1', 'album_description' => 'Album 1 description', 'photo_is_in' => 'album_1', 'photo_name' => 'photo_1.jpg', 'photo_description' => 'Photo 1 description'],

['album_id' => '1', 'photo_id' => '42', 'album_name' => 'album_1', 'album_description' => 'Album 1 description', 'photo_is_in' => 'album_1', 'photo_name' => 'photo_2.jpg', 'photo_description' => 'Photo 2 description'],

//==================

['album_id' => '2', 'photo_id' => '43', 'album_name' => 'album_2', 'album_description' => 'Album 2 description', 'photo_is_in' => 'album_2', 'photo_name' => 'photo_3.jpg', 'photo_description' => 'Photo 3 description'],

['album_id' => '2', 'photo_id' => '44', 'album_name' => 'album_2', 'album_description' => 'Album 2 description', 'photo_is_in' => 'album_2', 'photo_name' => 'photo_4.jpg', 'photo_description' => 'Photo 4 description'],

];

?>

I want to turn the $albums array above into a hierachical array like below:

 

<?php

$sameAlbums = Array(

	'album_1' => Array( //comes from 'album_name' => 'album_1' in the $data array above
		'album_id' => '1',
		'album_description' => 'Album 1 description',
		'album_1' => Array( //comes from 'phot_is_in' => 'album_1' in the $data array above
			
			'photo_1.jpg' => Array( 
				'photo_id' => '41',
				'photo_description' => 'Photo 1 deescription',
				
			), 
		
			
			'photo_2.jpg' => Array( 
				'photo_id' => '42',
				'photo_description' => 'Photo 2 deescription',
				
			) 
			
			
		)
		
	),
	
	//============================================

	'album_2' => Array( //comes from 'album_name' => 'album_2' in the $data array above
		'album_id' => '2',
		'album_description' => 'Album 2 description',
		'album_2' => Array( //comes from 'phot_is_in' => 'album_2' in the $data array above
			
			'photo_3.jpg' => Array( 
				'photo_id' => '43',
				'photo_description' => 'Photo 3 deescription',
				
			), 
		
			
			'photo_3.jpg' => Array( 
				'photo_id' => '44',
				'photo_description' => 'Photo 4 deescription',
				
			) 
			
			
		) 
		
	) 
	
);

?>

I have tried something like this which did not work as expected and I need help here:

<?php
$grouping = [];
foreach($albums as $albumName){
	//Grouping data by album name
	$grouping[$albumName['phot_is_in']] = $albumName[]; 
}

echo "<pre>";
echo print_r($grouping);

?>

 

 

[Barand]

Create the array directly from the query results, not from an intermediate array. Use the unique ids as array keys rather than names

$sql = "SELECT a.id as aid
             , a.album_name
             , a.album_description
             , p.id as pid
             , p.photo_name
             , p.photo_description
        FROM albums a
             INNER JOIN
             photos p ON a.id = p.photo_is_in
        ORDER BY aid, pid";
$result = $pdo->query($sql);

$pics = [];
//
// build the required array
//
foreach ($result as $row) {
    if (!isset($pics[$row['aid']]) {
        // if no array element yet for the album
        // create on with empty pics array
        $pics[$row['aid']]['album_name'] = $row['album_name'];
        $pics[$row['aid']]['album_description'] = $row['album_description'];
        $pics[$row['aid']]['photos'] = [];
    }
    // add the pics to the array for the album
    $pics[$row['aid']]['photos'][$row['pid']] = [  'name' => $row['photo_name'] ,
                                                   'description' => $row['photo_description']   ];
}