Joining two tables and limiting data from only 1

[plugnz]

Hi Everyone,

 

I am having fun trying to sort out a way of joining two tables but only limiting the data from one of them.

 

Here's my plan...

I have one database that holds category names ( ie boats,ships,cars) and another that holds images that relate to those category names. ( ie boat1.jpg,boat2.jpg,ship1.jpg,ship2.jpg,car1.jpg etc).

What I want to do is display just 1 image from each of those category names alongside the category names..

 

ie boat,boat1.jpg, ship,ship1.jpg,car,car1.jpg....

at this point it doesn't matter which image comes from each category as long as it's only one image rather than all of them.

 

I have tried to limit but only end up with 1 image from 1 category.

 

heres my select query...

$query = "SELECT ctg.category_id, ctg.category_name, img.image_id " .
             "FROM cms_categories ctg " .
		 "INNER JOIN cms_images_category img " .
		 "ON ctg.category_name = img.name_category " . 
		 "WHERE ctg.category_id " .
		 "ORDER BY category_name " .
		 "LIMIT 1 ";  
		 			   
      $results1 = mysql_query($query,$conn)
        or die(mysql_error());

    while ($row1 = mysql_fetch_array($results1)) {
      extract($row1);
       $images = $ImageThumb . $image_id . ".jpg";
    echo "<td class='uploadimg1'><a href =\"".$ImageDir . $image_id . ".jpg\" target='top'>"; 
    echo "<img src=\"".$images . "\"></a>\n";
    echo '<h2><a href ="search1.php?category=' . 
       $row1['category_name'] . '">' . htmlspecialchars($row1['category_name']) . "</a></h2\n";
  echo ($x % 5 == 0)? "</tr><tr>" : "";
      $x++;
} 

 

Any thoughts? Thanks in advance....

 

 

[Andy-H]

$query = "SELECT ctg.category_id, ctg.category_name, img.image_id " .
             "FROM cms_categories ctg " .
          "INNER JOIN cms_images_category img " .
          "ON ctg.category_name = img.name_category " . 
          "GROUP BY ctg.category_name ";  
                      
      $results1 = mysql_query($query,$conn)
        or die(mysql_error());

    while ($row1 = mysql_fetch_assoc($results1)) {
      extract($row1);
       $images = $ImageThumb . $image_id . ".jpg";
    echo "<td class='uploadimg1'><a href =\"".$ImageDir . $image_id . ".jpg\" target='top'>"; 
    echo "<img src=\"".$images . "\"></a>\n";
    echo '<h2><a href ="search1.php?category=' . 
          stripslashes(htmlentities($category_name, ENT_QUOTES)) . '">' . stripslashes(htmlentities($category_name, ENT_QUOTES)) . "</a></h2\n";
     echo ($x % 5 == 0)? "</tr><tr>" : "";
      $x++;
   } 

[Psycho]

I don't think Andy's is quite right. That query will group by "category_name" which I believe is a unique field in the category table. So, that grouping wouldn't do anything. Instead, you would want to group by the "img.name_category" field which is not unique in the images table:

SELECT ctg.category_id, ctg.category_name, img.image_id 
FROM cms_categories ctg
INNER JOIN cms_images_category img
    ON ctg.category_name = img.name_category
GROUP BY img.name_category

[plugnz]

aw read about that GROUP BY but didn't quite get it! Thanks heaps.