Listing Items by Category in PHP

Can someone help me with listing items by category in PHP?

I'm trying to create a simple list of books by category:

JavaScript

JavaScript Patterns

Object-Oriented JavaScript

Ajax

Ajax Definitive Guide

Bulletproof Ajax

jQuery

jQuery Cookbook

Learning jQuery 1.3

I have no problems with the data structure or SQL 开发者_StackOverflow中文版query:

BOOK:     book_id, book_title, fk_category_id  
CATEGORY: category_id, category_name

SELECT category.category_name, book.book_title
FROM category LEFT OUTER JOIN book
ON category.category_id = book.fk_category_id;

My problem is that I don't know how to write the PHP script to list the books under each category header.

I know enough to get the result set into an array, but then I'm stumped on using that array to group the items as shown.

Another Stack question addresses almost the same thing, but the responses stop short of solving the PHP code: List items by category

Thanks!

Add an ORDER BY category.category_name clause to your query so that as you loop through the resulting rows, the items in each category will be grouped together. Then, each time the category is different from the previous one seen, print out the category as the heading before printing the title.

$category = null;
foreach ($rows as $row) {
    if ($row['category_name'] != $category) {
        $category = $row['category_name'];
        print "<h1>".$category."</h1>\n";
    }
    print $row['book_title']."<br/>\n";
}

Order the results by category and then just iterate thru, putting a category header whenever the category name changes.

The ordering is most easily done in the SQL query. You don't even need an intermediate array.

SELECT category.category_name, book.book_title
FROM category LEFT OUTER JOIN book
               ON category.category_id = book.fk_category_id
ORDER BY category.category_name

And then for the PHP

$res = mysql_query($query);
$lastCategory = '';
while ($row = mysql_fetch_assoc($res))
{
    if($row['category_name'] != $lastCategory)
    {
       $lastCategory = $row['category_name'];
       echo "<br /><strong>$lastCategory</strong>";
    }        
    echo $row['book_title'] . ' <br />';
}

You do not need to put all your results into an array first. You can just fetch them as you go.