Help to optimise mysql query

[TheTiger]

Hi,

I've got the below working so you can see all the items in a shelf, all shelves inside a cabinet, but I know running queries inside loops isn't the best performance wise. Is there a way I can optimise this?

CREATE TABLE IF NOT EXISTS cabinet
(
    cabinet_id int not null auto_increment,
    primary key(cabinet_id)
);

CREATE TABLE IF NOT EXISTS shelf
(
    shelf_id int not null auto_increment,
    cabinet_id int,
    primary key(shelf_id), 
    foreign key(cabinet_id) references cabinet(cabinet_id) 
);

CREATE TABLE IF NOT EXISTS item
(
    item_id int not null auto_increment,
    shelf_id int,
    primary key(item_id), 
    foreign key(shelf_id) references shelf(shelf_id) 
);

$cabinetID = $_GET['cabinetid'];

$sqlShelf = sprintf('   SELECT shelf_id
                            FROM shelf
                            INNER JOIN cabinet ON (cabinet.cabinet_id = shelf.cabinet_id)
                            WHERE cabinet.cabinet_id = %s', $cabinetID);

    $resultShelf = mysql_query($sqlShelf);

while($shelf = mysql_fetch_assoc($resultShelf)) {
                $shelfID = $shelf['shelf_id'];

                $sqlItem = sprintf('SELECT *
                        FROM item
                        INNER JOIN shelf ON (item.shelf_id = shelf.shelf_id)
                        INNER JOIN cabinet ON (cabinet.cabinet_id = shelf.cabinet_id)
                        WHERE cabinet.cabinet_id = %s
                        AND shelf.shelf_id = %s', $cabinetID, $shelfID);

                $resultItem = mysql_query($sqlItem);

                echo('<div id="' . $shelfID . '">');

                while($item = mysql_fetch_assoc($resultItem)) {
                    echo('<p>' . $item['item_name'] . '</p>');
                }

                echo('</div>');

 

[nervex]

this prob wont work but just an example (ive had a drink)- select everything possible and put it in an array (only running one query) and loop through the array to do stuff.

$sqlShelf = "SELECT item.*, shelf.shelf_id, cabinet.cabinet_id
		FROM item, shelf, cabinet
		WHERE item.shelf_id = shelf.shelf_id
		AND shelf.cabinet_id = cabinet.cabinet_id";
$result = mysql_query($sqlShelf);

$items=array();
while($res = mysql_fetch_assoc($result)) {
	$items[$res['cabinet_id']][$res['shelf_id']] = $res;
}

foreach($items as $cabinetID => $shelf){
	echo '<div id="'.$cabinetID.'">';
	foreach($shelf as $shelfID => $itemArray){
		echo '<div id="'.$shelfID.'">';
			foreach($itemArray as $item){
				echo '<div id="'.$item['item_id'].'">item #'.$item['item_id'].'</div>';
			}
		echo '</div>';
	}
	echo '</div>';
}