Help with query

Im trying to figure out how this works…
I have a table setup like

CREATE TABLE power_trails (
power_trail_id INT autonumber,
receptacle_id INT,
power_strip_id INT,
asset_id INT,
FOREIGN KEY ( receptacle_id  ) REFERENCES receptacles ( receptacle_id ),
FOREIGN KEY ( power_strip_id ) REFERENCES power_strips( power_strip_id ),
PFOREIGN KEY ( asset_id ) REFERENCES assets( asset_id ),
RIMARY KEY ( power_trail_id )
);

and am trying to make query return an array like

receptacle_id = [0]
power_strip_id = [0]
asset_id = [0,1,2,3]

so I can get an array of the 3 foreign keys…
like this?

$sql = 'SELECT power_trail_id,power_strip_id,receptacle_id,asset_id
		FROM power_trails;		
		
$result = $pdo->query($sql);

$power_strips = $result->fetchAll();

The code

$sql = 'SELECT power_trail_id,power_strip_id,receptacle_id,outlett_placement,circuit_breaker_id,power_panel_id,pdu_id
		FROM power_trails
		WHERE power_strip_id = '.$power_strip_id;		
		
$result = $pdo->query($sql);

$power_strips = $result->fetchAll();

echo '<pre>';print_r($power_strips);echo '</pre>'

results in

Array
(
    [0] => Array
        (
            [power_trail_id] => 2
            [power_strip_id] => 1
            [receptacle_id] => 
            [outlett_placement] => 
            [circuit_breaker_id] => 
            [power_panel_id] => 
            [pdu_id] => 
        )

    [1] => Array
        (
            [power_trail_id] => 3
            [power_strip_id] => 1
            [receptacle_id] => 
            [outlett_placement] => 
            [circuit_breaker_id] => 
            [power_panel_id] => 
            [pdu_id] => 
        )


)

Is there a filter for fetchAll() which would result in

Array
(
    [power_trail_id] =>  [1,1]
    [receptacle_id] => []
    [outlett_placement] => []
    [circuit_breaker_id] => []
    [power_panel_id] => []
    [pdu_id] =>  []
)

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.