I have an object oriented PHP application. I have a simple hierarchy stored in an SQL table (Chapters and Authors that can be assigned to a chapter).

I wrote the following method to fetch the chapters and the authors in a single query and then loop through the result, figuring out which rows belong to the same chapter and creating both Chapter objects and arrays of Author objects.

However, I feel like this can be made a lot neater. Can someone help?

function getChaptersWithAuthors($monographId, $rangeInfo = null) {
    $result =& $this->retrieveRange(
        'SELECT mc.chapter_id, mc.monograph_id, mc.chapter_seq, ma.author_id, ma.monograph_id, mca.primary_contact, mca.seq, ma.first_name, ma.middle_name, ma.last_name, ma.affiliation, ma.country, ma.email, ma.url
        FROM monograph_chapters mc
        LEFT JOIN monograph_chapter_authors mca ON mc.chapter_id = mca.chapter_id
        LEFT JOIN monograph_authors ma ON ma.author_id = mca.author_id
        WHERE mc.monograph_id = ?
        ORDER BY mc.chapter_seq, mca.seq',
        $monographId, $rangeInfo
    );

    $chapterAuthorDao =& DAORegistry::getDAO('ChapterAuthorDAO');
    $chapters = array();
    $authors = array();
    while (!$result->EOF) {
        $row = $result->GetRowAssoc(false);
        // initialize $currentChapterId for the first row
        if ( !isset($currentChapterId) ) $currentChapterId = $row['chapter_id'];

        if ( $row['chapter_id'] != $currentChapterId) {
            // we're on a new row. create a chapter from the previous one
            $chapter =& $this->_returnFromRow($prevRow);
            // set the authors with all the authors found so far
            $chapter->setAuthors($authors);
            // clear the authors array
            unset($authors);
            $authors = array();
            // add the chapters to the returner
            $chapters[$currentChapterId] =& $chapter;

            // set the current id for this row
            $currentChapterId = $row['chapter_id'];
        }

        // add every author to the authors array
        if ( $row['author_id'] )
            $authors[$row['author_id']] =& $chapterAuthorDao->_returnFromRow($row);

        // keep a copy of the previous row for creating the chapter once we're on a new chapter row
        $prevRow = $row;
        $result->MoveNext();

        if ( $result->EOF ) {
            // The result set is at the end
            $chapter =& $this->_returnFromRow($row);
            // set the authors with all the authors found so far
            $chapter->setAuthors($authors);
            unset($authors);
            // add the chapters to the returner
            $chapters[$currentChapterId] =& $chapter;
        }
    }

    $result->Close();
    unset($result);
    return $chapters;
}

PS: the _returnFromRow methods simply construct an Chapter or Author object given the SQL row. If needed, I can post those methods here.