How to use SQL to avoid queries within loops

Question

I currently have two related tables, one for 'groups' and another for 'items'. Items are stored in groups by an associated group ID. Very basic table relations but unfortunately my SQL is not very good so I'm unsure what is the best way to loop through all groups and, within them, loop through all items to end up with a list of all items categorised by their groups.

Currently I have this code:

$query = "SELECT ID AS GRPID, Description FROM Groups_Inv ORDER BY SortNum ASC";
try { 
  $result = odbc_exec($connect,$query); 
  if($result){ 
    while ($groups = odbc_fetch_array($result)) {
      echo "<li title='".$groups['DESCRIPTION']."' class='category'>
            <h3><span><a class='category'>".
              $groups['DESCRIPTION'].
           "</a></span></h3><ul class='sub-menu'>\n";
      $subquery = "SELECT ID AS ITEM_ID, Description
                   FROM Items_Inv WHERE grpID = ".$groups['GRPID']."";
      $subresult = odbc_exec($connect,$subquery);
      while ($items = odbc_fetch_array($subresult)) {
        echo "<li class='sub_item' title='".$items['DESCRIPTION']."'>
              <input type='radio' class='addRow'>
                <label for='".$items['ITEM_ID']."'>".
                  $items['DESCRIPTION'].
               "</label></li>\n";
      }
      echo "</ul></li>\n";
    }
  } 
  else{ 
    throw new RuntimeException("Failed to connect."); 
  } 
} 
catch (RuntimeException $e) { 
  print("Exception caught: $e");
}

I know that having a SQL query within a loop is a very bad idea which is why I would like to optimise this code but my knowledge of SQL is unfortunately pretty limited outside of basic CRUD stuff.

I would also like to be able to not display groups that have no items, but I can't really do that with this code. What would be the best way to be able to get all groups and associated items within one query, as well as being able to specify only selecting groups with associated items?

score 0 · Answer 1 · answered Sep 25 '13 at 10:22

0

Have your tried:

$query = "SELECT ID AS GRPID, Description, ID AS ITEM_ID, Description AS ITEM_DESC
FROM Groups_Inv 
LEFT JOIN Items_Inv ON (Groups_Inv.ID = ITEM_Inv.ID)
ORDER BY SortNum ASC";

answered Sep 25 '13 at 10:22

Thanks for you help but unfortunately this does not quite work for me as it seems to select groups without items too. I'd also like to order the results so each group description is listed once and all items are listed under that group. How would I achieve this? – Roy Sep 25 '13 at 11:04
Change LEFT JOIN to INNER JOIN. If that doesn't work try RIGHT JOIN. – Sep 25 '13 at 11:05

score 0 · Answer 2 · answered Sep 25 '13 at 10:25

I think, this is good, u can use one query, for ex:

SELECT ii.ID AS ITEM_ID, ii.Description AS item_desc, gi.Description AS group_desc  FROM Items_Inv ii INNER JOIN Groups_Inv gi ON ii.grpID=gi.ID

You will get a table with all items, with group descriptions, but group descriptions will be duplicate next to the items. U can sort by foreach all, but better idea is your solution.

score 0 · Answer 3 · answered Sep 25 '13 at 10:27

0

SELECT g.ID AS GRPID, g.Description AS GrpDesc,i.ID AS ItemId, i.Description AS ItemDescription
FROM Groups_Inv g
LEFT JOIN Items_Inv i
ON g.ID=i.grpID
ORDER BY g.SortNum ASC

This query will return all the group with corresponding items.

answered Sep 25 '13 at 10:27

Chamal

1,439
10
15

Thanks, this works but then how do I loop it so I only print the group description once but print all items for that group under it? – Roy Sep 25 '13 at 10:44

How to use SQL to avoid queries within loops

3 Answers3