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

Question

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

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;

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

Hi Strawberry, thanks for the feedback, I have edited my question to include create and insert statements. If there's any more improvement I need to make, kindly point it out. Cheers! — Willydbd
– Willydbd, Commented Apr 3, 2021 at 19:19

a.mola · Accepted Answer · 2021-04-04 04:46:24Z

1

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);
}

edited Apr 4, 2021 at 4:46

answered Apr 4, 2021 at 3:43

a.mola

4,0119 silver badges23 bronze badges

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

3 Comments

Willydbd Over a year ago

Thank you @a.mola for your response, I do appreciate it. So, here are the things wrong with this way, it won't group individual asset_number under asset_name and the assets under individual asset_number. That's exactly what I want to do

a.mola Over a year ago

So the asset should be under the asset_number?

Willydbd Over a year ago

The assets that have the same asset_number should be under the asset_number, same applies to asset_numbers to asset_name.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related