MYSQL: Instead of using SELECT statements in a while loop - what are my options?

Question

I have a table which contains a list of categories and and another table which contains a list of products in each category.

e.g.

CatID | Cat_Name
----------------
1     | Books
2     | CDs

and

ProductID | Product_Name     | CatID
------------------------------------
1         |The Bible         | 1
2         |The Koran         | 1
3         |90s Greatest Hits | 2
4         |80s Greatest Hits | 2

what I want to do is get

<ul>
<li>Books</li>
   <ul>
   <li>The Bible</li>
   <li>The Koran</li>
   </ul>
<li>Cds</li>
   <ul>
   <li>90s Greatest Hits </li>
   <li>00s Greatest Hits </li>
   </ul>
 </ul>

without doing (PHP)

$query = mysql_query("SELECT ... FROM categories")

while($row = mysql_fetch_assoc($query)):

$query2 = mysql_query("SELECT ... FROM products WHERE catId = $row['CatId'])

endwhile;

How can I move away from using nested while/SELECT statements, can I do it efficiently with one query - and how do I access the relevant data using PHP?

EDIT: I was under the impression that if your tables become quite large, multiple SQL queries will slow your page, and therefore to optimize page load you should try to reduce the number of queries?

Answer 1

You could select all the data at once using a join...

SELECT *
    FROM category JOIN products ON catgegory.CatID = products.CatID
    ORDER BY products.CatID;

but I can't really see anything terribly wrong with your current approach.

Answer 2

SELECT * FROM category JOIN products USING(CatID);

Answer 3

select all products and category names using JOIN

select * from category c, products p where c.catID = p.catID order by p.catID

fetch and group results

$products_by_cat = array();
foreach(fetch(....) as $record)
    $products_by_cat[$record->catName][] = $record;

you'll get a 2-D array like

 [books] => array(bible, koran)
 [CDs]   => array(cd1, cd2, ....)

output this array with two nested loops

 foreach($products_by_cat as $cat => $products)
    <li> $cat  <ul>
    foreach($products as $p)
        <li> $p->name </li>
    </li></ul>

Answer 4

stereofrog's answer is very good. I'd like to point out that you can print your list without looping through the result set more than once. This can be useful if you have a lot of items.

To do so, make sure the query result is ordered by category. Create a variable to keep track of the 'current category' and set it to something that will not be a category id, like 'x'. Loop through the result and every time the CatID does not match your current category value, you know you've changed categories and need to print the category header. Reset the current category value to the new CatID value.

That's enough in some list situations. In your case, you'll also need to figure out when a category is ending so you can end your lists properly. You can do this by looking-ahead in the result array to see if the next category matches your current category value and acting accordingly.

With only a few categories, it really doesn't matter how you do it. If you have thousands of rows with a lot of data, this method can be a little faster and use less memory. It's a little harder to figure out logically than stereofrog's method. But it only loops through the data once, and it does not create a duplicate of the data, which can be handy if you have megabytes of stuff to loop through.