One title and several sample images for each subcategory on a list

Frozen_Kiwi

I have a site with lots of images organized in categories and subcategories. Whenever a category is chosen (say, "Pets"), I want to display the list of all available subcategories so that each subcategory has a title and 3 sample images, like that:

Dogs
image image image

Cats
image image image

Rats
image image image

The tables are structured as follows:

img_id  subcat_title  img_title                 sample
---------------------------------------------------------------
1          dogs              someimage                 1
2          cats               another_image             0
3          rats               yet_another_image     1
4          rats               someimage2                1
5          dogs              another_image3          0
5          cats               someimage6                1

The query couldn't have been simpler:

$samples = mysql_query("SELECT subcat_title, img_title FROM table WHERE sample = '1'") or die(mysql_error());

while ($sample = mysql_fetch_array($samples))
{
       echo $sample['subcat_title'] .  '<br /><img src="/pets/' . $sample['img_title'] . '.jpg"  />';
}

That's where I'm stuck: the above code gives me either 1) one subcategory title and one image or 2) several images and the subcategory titles attached to each and every image. Is there a way to display the subcategory title just once? I'd appreciate any fresh ideas...

NogDog

$sql = "SELECT subcat_title, GROUP_CONCAT(img_title SEPARATOR ',') AS images FROM table WHERE sample = '1' GROUP BY subcat_title";
$result = mysql_query($sql) or die("Query failed ($sql): " . mysql_error());
while($row = mysql_fetch_assoc($result))
{
   echo "<h3>{$row['subcat_title']}</h3>\n";
   echo "<ul>\n";
   $images = explode(',', $row['images']);
   foreach($images as $image)
   {
      echo "<li><img src='/pets/{$sample['img_title']}.jpg'  /></li>\n"; 
   }
   echo "</ul>\n";
}

NOTE: GROUP_CONCAT() requires MySQL 4.1 or later.

Frozen_Kiwi

Many thanks but unfortunately, it's MySQL 4.0.24. Sorry, should have mentioned that from the very beginning. Is there any workaround?

NogDog

Only other thing I can think of is something like:

$sql = "SELECT subcat_title, img_title FROM table WHERE sample = '1' ORDER BY subcat_title, img_title";
$result = mysql_query($sql) or die("Query failed ($sql): " . mysql_error());
$imgArray = array();
while($row = mysql_fetch_assoc($result))
{
   $imgArray[$row['subcat_title']][] = $row['image_title'];
}
foreach($imgArray as $subcat => $images)
{
   echo "<h3>$subcat</h3>\n";
   echo "<ul>\n";
   foreach($images as $img)
   {
      echo "<li><img src='/pets/$img.jpg'  /></li>\n";
   }
   echo "</ul>\n";
}

Frozen_Kiwi

Ah I was thinking of using the nested foreach loops but hoped to avoid them. But since there seems to be no other way, I'll give it a try. Thanks a lot for your help. 🙂