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