PDO -> Nested selects allowed?

For my photography website, I have a photo gallery, and the user first sees a list of categories. They can click down to see thumbnails, and again to see individual photographs. I want to enhance this to, with the category listing, show the first thumbnail. I have a category table, a photo table and the photos and thumbnails are files. The photo table has a category_id column and a filename column. Now, I Googled a bit, and it is not clear that i) Select the categories ii) Loop through the result set

iii) Within the loop select the filename     will work

Does the select within the loop clobber the result set of the category selection? I saw suggestions to fetch the entire result set into a PHP array, or to use |$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); The queries I have created are: $categoryquery = 'SELECT c.category_id, c.category_name, c.category_description,

                 COUNT(photo_id) FROM gallery_category as c LEFT JOIN gallery_photos
                 as p ON p.photo_category = c.category_id GROUP BY c.category_id
                 ORDER BY c.category_order';
$catthumbquery = 'SELECT photo_filename FROM gallery_photos WHERE photo_category=:cid
                 ORDER BY photo_order LIMIT 1';

and the PHP code I have drafted is: try { $stmt = $dbh->prepare($categoryquery); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

     $id          = $row['category_id'];
     $name        = $row['category_name'];
     $description = $row['category_description'];
     $count       = $row['COUNT(photo_id)'];

     $stmt2 = $dbh->prepare($catthumbquery);
     $stmt2->bindParam(':cid', $id, PDO::PARAM_STR);
     $stmt2->execute();

     $result_array[] = "\t\t<div class=\"categoryname\">\n" .
       "\t\t\t<a href=\"viewgallery.php?cid=" . $id . "\">" .
       $name . "</a> ($count)\n\t\t</div>\n \t\t<div class=\"categorydescription\">" .
       "\n\t\t\t$description\n\t\t</div>";

} } catch (PDOException $e) { echo "Building category list failed: " . $e->getMessage(); } Thanks to those who can help! Stephen |