Grouping/categorizing Results

[JoeyH3]

Hi all!

 

I'm working on a simple script to manage a webcomic, which will have multiple pages in each chapter. I'm trying to create an archive page which will show thumbnails for every comic, organized by chapter, such as:

 

Chapter 1 Title

- Page 1

- Page 2

- Page 3

Chapter 2 Title

- Page 1

- Page 2

- Page 3

 

Etc

 

In MySQL, I have two tables:

Chapter (ChapterID, Name)

Page (PageID, Chapter, Image, Thumbnail)

 

The only method I could find for achieving this on Google suggested using a query to obtain all of the chapters from the chapter table, and then while looping through the results, use a second statement with a WHERE clause to select the comics for that chapter. However, that just seems like poor practice and sounds horribly inefficient once too many chapters are added.

 

I'm wondering if there's a more efficient way to approach this, so that I can organize my results into groups? If someone could simply give me the logic to use or nudge me in the right direction, I'd gladly appreciate it

 

Thanks,

Joey

[Jessica]

You'll do a query using a JOIN, then loop through one set of results. 

 

Pseudo code:

$chapter = 0;
$sql = "SELECT * FROM chapters INNER JOIN pages ON pages.chapterID = chapter.chapterID ORDER BY chapterNumber";
//mysql query
//loop results
    if($chapter != $row['chapterNumber']){
       $chapter = $row['chapterNumber'];
       //echo chapter heading
    }
    //echo page number
//end loop.

[JoeyH3]

That works like a charm, thank you very much