PDO -> Nested selects allowed?

[Stephen <stephen-d@xxxxxxxxxx>]

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
|

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php