How do I create a nested array with three level deep with sql from the same table

I need help structuring my data properly.

So how do I create a nested array with three-level deep with SQL from the same table assets. Say I have a table with the following fields;

id, int PK, asset_name varchar, asset_number varchar, specification varchar, others mostly varchar

enter image description here

Create a similar table with this code

CREATE TABLE IF NOT EXISTS `assets` (
`id` bigint(15) NOT NULL AUTO_INCREMENT,
`asset_name` varchar(300) NOT NULL,
`asset_number` varchar(50) NOT NULL,
`specification` varchar(100) NOT NULL,
`assigned` tinyint(1) NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

INSERT INTO assets (id, asset_name, asset_number, specification, assigned, created_at) values
(1, 'HP', 'AGN/OFF/6784', 'Corei7 16GB 1TB', 0, NOW()),
(2, 'HP', 'AGN/OFF/2087', 'Corei3 16GB 500GB', 0, NOW()),
(5, 'HP', 'AGN/OFF/2952', 'Corei9 16GB 1TB', 0, NOW()),
(9, 'HP', 'AGN/OFF/2087', 'Corei3 16GB 500GB', 1, NOW()),
(10, 'HP', 'AGN/OFF/2087', 'Corei3 16GB 500GB', 0, NOW()),
(11, 'DELL', 'AGN/OFF/6904', 'Corei7 16GB 1TB', 0, NOW()),
(12, 'DELL', 'AGN/OFF/0598', 'Corei7 16GB 1TB', 0, NOW()),
(13, 'DELL', 'AGN/OFF/0598', 'Corei7 16GB 1TB', 1, NOW()),
(14, 'HP', 'AGN/OFF/6784', 'Corei7 16GB 1TB', 1, NOW()),
(19, 'DELL', 'AGN/OFF/6904', 'Corei7 16GB 1TB', 1, NOW());

So the question is, how do I structure my query to return something like this;

Array ( [0] => Array ( [asset_name] => HP [asset_numbers] => Array ( [0] => AGN/OFF/6784
[assets] =>  Array ( [0] => Array ( [id] => 1 [asset_name] => HP [asset_number] => AGN/OFF/6784 [specification] => Corei7 16GB 1TB [assigned] => 0
[created_at] => 2021-03-27 12:14:14) [1] => Array ( [id] => 14 [asset_name] => HP [asset_number] => AGN/OFF/6784 [specification] => Corei7 16GB 1TB [assigned] => 1
[created_at] => 2021-03-27 12:14:14))
[1] => AGN/OFF/2087
[assets] =>  Array ( [0] => Array ( [id] => 2 [asset_name] => HP [asset_number] => AGN/OFF/2087 [specification] => Corei3 16GB 500GB [assigned] => 0
[created_at] => 2021-03-27 12:14:15) [1] => Array ( [id] => 9 [asset_name] => HP [asset_number] => AGN/OFF/2087 [specification] => Corei3 16GB 500GB [assigned] => 1
[created_at] => 2021-03-27 12:14:15) [2] => Array ( [id] => 10 [asset_name] => HP [asset_number] => AGN/OFF/2087 [specification] => Corei3 16GB 500GB [assigned] => 0
[created_at] => 2021-03-27 12:14:15))
[2] => AGN/OFF/2952
[assets] =>  Array ( [0] => Array ( [id] => 5 [asset_name] => HP [asset_number] => AGN/OFF/2952 [specification] => Corei9 16GB 1TB [assigned] => 1
[created_at] => 2021-04-01 12:14:15)))
[1] => Array ( [asset_name] => DELL [asset_numbers] => Array ( [0] => AGN/OFF/6904
[assets] =>  Array ( [0] => Array ( [id] => 11 [asset_name] => DELL [asset_number] => AGN/OFF/6904 [specification] => Corei7 16GB 1TB [assigned] => 0
[created_at] => 2021-03-27 12:14:14) [1] => Array ( [id] => 19 [asset_name] => DELL [asset_number] => AGN/OFF/6904 [specification] => Corei7 16GB 1TB [assigned] => 0
[created_at] => 2021-03-27 12:14:14)
[2] => Array ( [id] => 20 [asset_name] => DELL [asset_number] => AGN/OFF/6904 [specification] => Corei7 16GB 1TB [assigned] => 1
[created_at] => 2021-03-27 12:14:14))
[1] => AGN/OFF/0598
[assets] =>  Array ( [0] => Array ( [id] => 12 [asset_name] => DELL [asset_number] => AGN/OFF/0598 [specification] => Corei7 16GB 1TB [assigned] => 0
[created_at] => 2021-04-03 12:14:14) [1] => Array ( [id] => 13 [asset_name] => DELL [asset_number] => AGN/OFF/0598 [specification] => Corei7 16GB 1TB [assigned] => 1
[created_at] => 2021-04-03 12:14:14))
) ) ) )

I’m sorry if this is too long, I don’t know a better way to make it clear enough

Here is what I have done so far, I mean I am stuck here.

function get_asset_histories(){
    global  $db; //predefined db connection

    $assetArray = array(); //create an array
    $r = $db->query("SELECT DISTINCT asset_name FROM assets ");
    if (!$r || $r->num_rows == 0) return array();
    //loop all assets

    while ($r1 = $r->fetch_assoc())
    {
        $res=array();
         $res['asset_name'] = $r1['asset_name'];

         //add all asset's asset_number
         $asset_name = $res['asset_name'];
         $rr = $db->query ("SELECT DISTINCT `asset_number` from assets where `asset_name` = '$asset_name'   ");
         if (!$rr || $rr->num_rows == 0) return array();
         $res2 = array();
         while($rr1 = $rr->fetch_assoc()){
             $res2[] = $rr1['asset_number'];
             $asset_no = $rr1['asset_number'];

         $res['asset_numbers'] = $res2; //add asset numbers to asset name

    }

        $assetArray[] = $res;
    }

        return $assetArray;
}

I didn’t add the remaining part of my code since its where am getting it wrong. Once I try to add the third layer ‘assets’ I’d mess it up.

I really hope this is clear to understand. Thanks in advance

Answer

So, I don’t think there is need for the amount of queries you’re writing.

This is how I would do it.

I would get all the rows from the assets table, iterating through them, I would group them to the $assetsArray based on the asset_name.

Inside the group, it adds the new asset_numbers and assets value and sets the asset_name. I don’t have to check if it exists to initialize a new array becuase PHP automatically creates a new array when you try to use the [] characters.

I then return the $assetsArray from the function, using the array_values to return it without the asset_name as keys

function get_asset_histories() {
    global  $db;

    $assetArray = [];
    $assets = $db->query('SELECT * FROM assets');
    if (!$assets || $assets->num_rows == 0) return [];

    while ($asset = $assets->fetch_assoc()) { 
        $assetArray[$asset['asset_name']]['asset_name'] = $asset['asset_name'];
        $assetArray[$asset['asset_name']]['asset_numbers'][$asset['asset_number']][] = $asset;
    }

    return array_values($assetArray);
}