Not that I know of.
You can always build a structured array the way you want by placing fields keys in the logical relation order, e.g. A power_strip has many receptacles has many assets.
So the power_strip_id is the primary key followed by the receptacle_id as the second.
You can use a static KEY assets as I did here to define that array and use open natural keys [], which holds each asset_id.
$power_strips = array();
while($row = $result->fetch(PDO::FETCH_ASSOC)){
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][] = $row['asset_id'];
}
Results like this
Array
(
[1] => Array
(
[1] => Array
(
[assets] => Array
(
[0] => 1
[1] => 7
[2] => 8
)
)
[2] => Array
(
[assets] => Array
(
[0] => 2
[1] => 9
[2] => 10
)
)
[3] => Array
(
[assets] => Array
(
[0] => 3
)
)
Now you can also place other related fields into this asset array by defining things a little differently. Say you want that power_trail_id. Instead using an open natural KEY for each asset, define the asset with the asset_id and define the fields and there corresponding values.
while($row = $result->fetch(PDO::FETCH_ASSOC)){
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_id'] = $row['asset_id'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['power_trail_id'] = $row['power_trail_id'];
}
which looks like this.
Array
(
[1] => Array
(
[1] => Array
(
[assets] => Array
(
[1] => Array
(
[asset_id] => 1
[power_trail_id] => 1
)
[7] => Array
(
[asset_id] => 7
[power_trail_id] => 10
)
[8] => Array
(
[asset_id] => 8
[power_trail_id] => 11
)
)
)
[2] => Array
(
[assets] => Array
(
[2] => Array
(
[asset_id] => 2
[power_trail_id] => 2
)
[9] => Array
(
[asset_id] => 9
[power_trail_id] => 12
)
[10] => Array
(
[asset_id] => 10
[power_trail_id] => 13
)
)
)
Now if you wanted to grab a field from another table like the asset_name you just change the query to join that asset table on the asset id then apply the field to the array building.
$sql = "SELECT
tr.power_trail_id
, tr.power_strip_id
, tr.receptacle_id
, tr.asset_id
, a.asset_name
FROM `power_trails` tr
LEFT JOIN `assets` a
ON a.asset_id = tr.asset_id";
and
while($row = $result->fetch(PDO::FETCH_ASSOC)){
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_id'] = $row['asset_id'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['power_trail_id'] = $row['power_trail_id'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_name'] = $row['asset_name'];
}
results in
Array
(
[1] => Array
(
[1] => Array
(
[assets] => Array
(
[1] => Array
(
[asset_id] => 1
[power_trail_id] => 1
[asset_name] => Asset 1
)
[7] => Array
(
[asset_id] => 7
[power_trail_id] => 10
[asset_name] => Asset 7
)
[8] => Array
(
[asset_id] => 8
[power_trail_id] => 11
[asset_name] => Asset 8
)
)
)
[2] => Array
(
[assets] => Array
(
[2] => Array
(
[asset_id] => 2
[power_trail_id] => 2
[asset_name] => Asset 2
)
[9] => Array
(
[asset_id] => 9
[power_trail_id] => 12
[asset_name] => Asset 9
)
[10] => Array
(
[asset_id] => 10
[power_trail_id] => 13
[asset_name] => Asset 10
)
)
)
Assuming the field outlet_placement is in the table receptacle you could join to that table and add the value to your array as well.
$sql = "SELECT
tr.power_trail_id
, tr.power_strip_id
, tr.receptacle_id
, tr.asset_id
, a.asset_name
, r.outlet_placement
FROM `power_trails` tr
LEFT JOIN `assets` a
ON a.asset_id = tr.asset_id
LEFT JOIN `receptacles` r
ON r.receptacle_id = tr.receptacle_id ";
and
while($row = $result->fetch(PDO::FETCH_ASSOC)){
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_id'] = $row['asset_id'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['power_trail_id'] = $row['power_trail_id'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_name'] = $row['asset_name'];
$power_strips[$row['power_strip_id']][$row['receptacle_id']]['assets'][$row['asset_id']]['outlet_placement'] = $row['outlet_placement'];
}
result
Array
(
[1] => Array
(
[1] => Array
(
[assets] => Array
(
[1] => Array
(
[asset_id] => 1
[power_trail_id] => 1
[asset_name] => Asset 1
[outlet_placement] => placement 1
)
[7] => Array
(
[asset_id] => 7
[power_trail_id] => 10
[asset_name] => Asset 7
[outlet_placement] => placement 1
)
[8] => Array
(
[asset_id] => 8
[power_trail_id] => 11
[asset_name] => Asset 8
[outlet_placement] => placement 1
)
)
)
[2] => Array
(
[assets] => Array
(
[2] => Array
(
[asset_id] => 2
[power_trail_id] => 2
[asset_name] => Asset 2
[outlet_placement] => placement 2
)
[9] => Array
(
[asset_id] => 9
[power_trail_id] => 12
[asset_name] => Asset 9
[outlet_placement] => placement 2
)
[10] => Array
(
[asset_id] => 10
[power_trail_id] => 13
[asset_name] => Asset 10
[outlet_placement] => placement 2
)
)
)
To further demonstrate the point, say we also hypothetically query the power panel and circuit breaker tables to get other values based on their IDs. It might look something like this .
$sql = "SELECT
tr.power_trail_id
, tr.power_strip_id
, tr.receptacle_id
, tr.asset_id
, a.asset_name
, r.outlet_placement
, pp.power_panel
, cb.circuit_breaker
FROM `power_trails` tr
LEFT JOIN `assets` a
ON a.asset_id = tr.asset_id
LEFT JOIN `receptacles` r
ON r.receptacle_id = tr.receptacle_id
LEFT JOIN `power_panels` pp
ON pp.power_panel_id = tr.power_panel_id
LEFT JOIN `circuit_breakers` cb
ON cb.circuit_breaker_id = tr.circuit_breaker_id";
But now this information is not so much related to the receptacles or their assets but more to the power strip and what supplies it. So in our array building we have the power_strip_id as the primary key. It is right after this key that we want to separate our information between power_strip and receptacles information.so we can start by adding the static key receptacles to the array we already built.
$power_strips[$row['power_strip_id']]['receptacles'][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_id'] = $row['asset_id'];
$power_strips[$row['power_strip_id']]['receptacles'][$row['receptacle_id']]['assets'][$row['asset_id']]['power_trail_id'] = $row['power_trail_id'];
$power_strips[$row['power_strip_id']]['receptacles'][$row['receptacle_id']]['assets'][$row['asset_id']]['asset_name'] = $row['asset_name'];
$power_strips[$row['power_strip_id']]['receptacles'][$row['receptacle_id']]['assets'][$row['asset_id']]['outlet_placement'] = $row['outlet_placement'];
We can then build a new section with static key power_strip and the related data.
$power_strips[$row['power_strip_id']]['power_strip']['power_panel'] = $row['power_panel'];
$power_strips[$row['power_strip_id']]['power_strip']['circuit_breaker'] = $row['circuit_breaker'];
resulting in something like this.
Array
(
[1] => Array
(
[power_strip] => Array
(
[power_panel] => MAIN 24
[circuit_breaker] => L1
)
[receptacles] => Array
(
[1] => Array
(
[assets] => Array
(
[1] => Array
(
[asset_id] => 1
[power_trail_id] => 1
[asset_name] => Asset 1
[outlet_placement] => placement 1
)
[7] => Array
(
[asset_id] => 7
[power_trail_id] => 10
[asset_name] => Asset 7
[outlet_placement] => placement 1
)
[8] => Array
(
[asset_id] => 8
[power_trail_id] => 11
[asset_name] => Asset 8
[outlet_placement] => placement 1
)
)
)
[2] => Array
(
[assets] => Array
(
[2] => Array
(
[asset_id] => 2
[power_trail_id] => 2
[asset_name] => Asset 2
[outlet_placement] => placement 2
)
[9] => Array
(
[asset_id] => 9
[power_trail_id] => 12
[asset_name] => Asset 9
[outlet_placement] => placement 2
)
[10] => Array
(
[asset_id] => 10
[power_trail_id] => 13
[asset_name] => Asset 10
[outlet_placement] => placement 2
)
)
)
Anyway, just pointing out that you can query and build an array to hold the values you want. So instead of using fetchAll and making multiple queries for each part of a page often ONE query can grab all the data for the page and loop through different sections of your array. You just need to structure the result in a format to suit your needs.