You would index/pivot the data when you fetch it, using the building_id as the main array index. To get the unique/one-time information per group, such as the buildingName, you would reference the zero’th row in each group of data, then loop over all the rows in the group to reference the individual asset information.
if you are using the PDO database extension, there’s a fetch mode to use with the fetchAll() method that will index/pivot the data by the first column in the SELECT … list - PDO::FETCH_GROUP
$sql = 'SELECT assets.name,assets.asset_id,materials.type,manufacturer,model,racks.name AS rackName,rooms.name AS roomName,rooms.room_id,buildings.name AS buildingName,buildings.building_id,sites.site_id FROM assets
RIGHT JOIN materials ON assets.material_id = materials.material_id
RIGHT JOIN projects ON assets.project_id = projects.project_id
RIGHT JOIN racks ON assets.rack_id = racks.rack_id
RIGHT JOIN rooms ON racks.room_id = rooms.room_id
RIGHT JOIN buildings ON rooms.building_id = buildings.building_id
RIGHT JOIN sites ON projects.site_id = sites.site_id
WHERE projects.project_id = '.$project_id.' AND assets.operational_status = 1';
$result = $pdo->query($sql);
$assets = $result->fetchAll();
//then
$sql = 'SELECT buildings.building_id.assets.name,assets.asset_id,materials.type,manufacturer,model,racks.name AS rackName,rooms.name AS roomName,rooms.room_id,buildings.name AS buildingName,buildings.building_id,sites.site_id FROM assets
RIGHT JOIN materials ON assets.material_id = materials.material_id
RIGHT JOIN projects ON assets.project_id = projects.project_id
RIGHT JOIN racks ON assets.rack_id = racks.rack_id
RIGHT JOIN rooms ON racks.room_id = rooms.room_id
RIGHT JOIN buildings ON rooms.building_id = buildings.building_id
RIGHT JOIN sites ON projects.site_id = sites.site_id
WHERE projects.project_id = '.$project_id.' AND assets.operational_status = 1';
$result = $pdo->query($sql);
$buildings = $result->fetchAll(PDO::FETCH_GROUP);
A. Did it produce the result you expected when you tried it?
B. You introduced a typo mistake when you added the building_id at the start of the SELECT list.
C. I recommend that you use short (1-2 letter) table aliases, to simplify all the typing needed for the sql statement.
D. I recommend that you use table(alias).column syntax for every column in a join query so that anyone reading the sql statement can tell which table each column comes from. This also prevents incorrect results when there are same name columns, with different meaning, in the tables.
E. The AS keyword for aliases is not required.
F. Why are you putting a value directly into the sql query? You should be using a prepared query.
try {
$sql = 'a.name,a.asset_id,m.type,m.manufacturer,m.model,r.name rackName,ri.name roomName,b.name buildingName
FROM assets a
RIGHT JOIN materials m ON a.material_id = m.material_id
RIGHT JOIN projects p ON a.project_id = p.project_id
RIGHT JOIN racks r ON a.rack_id = r.rack_id
RIGHT JOIN rooms r1 ON r.room_id = r1.room_id
RIGHT JOIN buildings b ON r1.building_id = b.building_id
RIGHT JOIN sites s ON p.site_id = s.site_id
WHERE p.project_id = :project_id AND a.operational_status = 1';
$stmt = $conn->prepare($sql);
$stmt->execute(['project_id', $project_id]);
$assets = $stmt->fetchAll();
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
try {
$sql = 'b.building_id,b.name buildingName
FROM assets a
RIGHT JOIN materials m ON a.material_id = m.material_id
RIGHT JOIN projects p ON a.project_id = p.project_id
RIGHT JOIN racks r ON a.rack_id = r.rack_id
RIGHT JOIN rooms r1 ON r.room_id = r1.room_id
RIGHT JOIN buildings b ON r1.building_id = b.building_id
RIGHT JOIN sites s ON p.site_id = s.site_id
WHERE p.project_id = :project_id AND a.operational_status = 1';
$stmt = $conn->prepare($sql);
$stmt->execute(['project_id', $project_id]);
$buildings = $stmt->fetchAll(PDO::FETCH_GROUP);
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
try {
$sql = 'SELECT b.building_id,b.name buildingName
FROM assets a
RIGHT JOIN racks r ON a.rack_id = r.rack_id
RIGHT JOIN rooms r1 ON r.room_id = r1.room_id
RIGHT JOIN buildings b ON r1.building_id = b.building_id
WHERE a.project_id = :project_id AND a.operational_status = 1';
$stmt = $pdo->prepare($sql);
$stmt->execute(['project_id' => $project_id]);
$buildings = $stmt->fetchAll(PDO::FETCH_GROUP);
echo '<pre>';print_r($buildings);echo '</pre>';
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
but am getting
Array
(
[1] => Array
(
[0] => Array
(
[buildingName] => gdfg
)
)
)
The building_id seems to be the index, how do I access it?
To me I see many relationships of the assets so just using a building_id as the primary key doesn’t really allow you to put your data in a logical order. As you are grabbing the field site_id I would assume there is also a siteName and on each site there are many buildings that have many rooms and have many racks that have many assets. It would be nice if the data could be given to us as we would like to display it.
Maybe not the best looking display but the point is that everything is grouped where it should be. Buildings from different sites are listed separately as well as each room, rack and asset. Sure you could make multiple queries to build a display like this but they would probably be nested and within display section and I tend to not make data queries within html if at all possible.
Now a single query could grab all your data and if placed in a structured array in that “logical order” I mentioned with site, building, room, rack and asset as static KEYS then using their IDs as keys to handle multiple occurrences, the display then becomes just a matter of looping through the different sections of the same data array. This array can be built within the WHILE loop or looping through the data set using foreach().
Why are you using RIGHT JOINS? You want to always start your joins from where you know you have data.
So, IF all assets are assigned to a rack, then your query is much faster using INNER JOINS which use indexes rather than table scans.
SELECT a.name
, a.asset_id
, m.type
, m.manufacturer
, m.model
, r.name rackName
, ri.name roomName
, b.building_id
, b.name buildingName
FROM assets a
INNER JOIN materials m ON a.material_id = m.material_id
INNER JOIN projects p ON a.project_id = p.project_id
INNER JOIN racks r ON a.rack_id = r.rack_id
INNER JOIN rooms r1 ON r.room_id = r1.room_id
INNER JOIN buildings b ON r1.building_id = b.building_id
WHERE p.project_id = :project_id
AND a.operational_status = 1
ORDER BY b.building_id
, a.asset_id
If you have assets which are part of the project but not on a rack yet (which I doubt because then the operational status shouldn’t be set), then your query can change but project at the very least projects will be an INNER JOIN because it’s in the where clause. But the driver should be assets, not anything else.
SELECT a.name
, a.asset_id
, m.type
, m.manufacturer
, m.model
, r.name rackName
, ri.name roomName
, b.building_id
, b.name buildingName
FROM assets a
INNER JOIN projects p ON a.project_id = p.project_id
LEFT OUTER JOIN materials m ON a.material_id = m.material_id
LEFT OUTER JOIN racks r ON a.rack_id = r.rack_id
LEFT OUTER JOIN rooms r1 ON r.room_id = r1.room_id
LEFT OUTER JOIN buildings b ON r1.building_id = b.building_id
WHERE p.project_id = :project_id
AND a.operational_status = 1
ORDER BY b.building_id
, a.asset_id
If you order by building_id, then all the items assigned to the same building will be together and you don’t have to do any goofy array manipulation. Just use code similar to what @Drummin provided and display building info when it changes.
