Database Output Question

[artisticre]

Below is my output that I am getting.
Red Cat
Green
Red Cat
Orange
Red Cat
Purple

I want to query the database to get the name of the tshirt (Red Cat) one time and then List the color choices (Green, Orange, Purple). I think I don't have the database relationship correctly but I can't figure it out. Can someone please sort this confusion out?

thanks

CREATE TABLE 1product (
prod_id int(5) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
productid int(5) NOT NULL default '0',
PRIMARY KEY (prod_id)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

CREATE TABLE 1product_color (
color varchar(50) NOT NULL default '',
productid int(5) NOT NULL default '0'
) TYPE=MyISAM;

Red Cat
Green
Red Cat
Orange
Red Cat
Purple

<?php
$db = mysql_connect("xxx", "xxx", "xxx");
mysql_select_db("xxx",$db);
$result = mysql_query("SELECT color, name from 1product, 1product_color where (1product.productid = 1product_color.productid) and (1product.prod_id = $id)",$db);
if ($myrow = mysql_fetch_array($result)) {
do {
echo ("<table border=0 width=65%>
<tr>
<td width=100% class=ShirtLabels align=center>$myrow[earringname]&nbsp;$myrow[name]</td>
</tr>
<tr>
<td width=100% align=center>$myrow[color]></td>
</tr>
");

} while ($myrow = mysql_fetch_array($result));
echo ("</table>\n");
} else {
echo "<br><br><p align=center>There Are Products Listed At This Time!</p>";
echo "<br><br><p align=center>Check Back Soon!</p>";
}
?>

[eoghain]

You are getting exactly what you are asking for, and it's the only way you can get the data. What you need to do is convert your PHP code to only display the shirt name once and then the colors.

if ($myrow = mysql_fetch_array($result)) { 
   $currentShirtName = "";
   do { 
      echo ("<table border=0 width=65%>")
      if ($currentShirtName != $myrow[name])
      {
         $currentShirtName = $myrow[name];
         echo ("
         <tr> 
         <td width=100% class=ShirtLabels align=center>$myrow[earringname] $myrow[name]</td> 
         </tr> 
         ");
      }
      echo ("
      <tr> 
      <td width=100% align=center>$myrow[color]></td> 
      </tr> 
      "); 

   } while ($myrow = mysql_fetch_array($result)); 
   echo ("</table>\n"); 
} else { 
   echo "<br><br><p align=center>There Are Products Listed At This Time!</p>"; 
   echo "<br><br><p align=center>Check Back Soon!</p>"; 
}