Nested SQL queries

[Levan]

Hi there

I am having trouble with a sql query I may be going about this entirely the wrong way. I am trying to set
up a product database where each product is classified by its Brand and each brand by a category. However some brands may fall into 2 categories. SO I created a 4th table which consisted of a primarykey
a BrandID column and a categoryID column. SO I have 4 tables - products, brands, categories, and brandcat the link between categories and brands.

Now I have created the following query which takes a http get variable which is the category id and I want to return the brand names and brand descriptions that fall in that category.
here is what I came up with...

[code]
$colname_BrandID = "-1";
if (isset($_GET['id_brand'])) {
  $colname_BrandID = (get_magic_quotes_gpc()) ? $_GET['id_brand'] : addslashes($_GET['id_brand']);
}
//This first query gets the brandID that fall into the category selected
mysql_select_db($database_dbconnect, $dbconnect);
$query_BrandID = sprintf("SELECT BrandID FROM brandcat WHERE CatID = '%s'", $colname_BrandID);
$BrandID = mysql_query($query_BrandID, $dbconnect) or die(mysql_error());
$row_BrandID = mysql_fetch_assoc($BrandID);
$totalRows_BrandID = mysql_num_rows($BrandID);

//This Query then uses the returned id's to bring the results from the brands table
mysql_select_db($database_dbconnect, $dbconnect);
$query_brands = sprintf("SELECT * FROM brand WHERE brandkey = '%s'", $row_BrandID);
$brands = mysql_query($query_brands, $dbconnect) or die(mysql_error());
$row_brands = mysql_fetch_assoc($brands);
$totalRows_brands = mysql_num_rows($brands);

[/code]

Essentially as you can see the second query actually nests the first.
When I run it with a value of 1 in the id_brand I get no results..mind you I get no php errors either
I am running using a repeating do-while function as below
[code]
    <?php $i=1;
          $bg_color="#FFFFFF";?>
    <?php do { ?>
    <?php
    $bg_color="#FFFFFF";
    if ($i % 2 == 0){$bg_color="#FFFF99";}
         $brandname=$row_brands['brandname'];
        $brandid=$row_brands['brandkey'];?>        
  <tr bgcolor=<?php echo $bg_color; ?>><td><div align="left">
      <h3><a href="items.php?id_item=<?php echo $brandid; ?>&amp;id_itemcat=<?php echo $row_catcheck['catkey']; ?>"><?php echo $i; ?> . <?php echo $brandname; ?></a></a></h3>
      <blockquote>
        <p><?php echo $row_brands['branddesc']; ?>
            </p>
          </blockquote>
      </div>
  </td>
      <td width="10%"><div id="image"><a href="#nogo" class="p1"><img src="images/products/brand<?php echo $brandid; ?>.gif" alt="Hover for full image" width="95px" height="95px"/><img class="large" src="images/products/brand<?php echo $brandid; ?>.gif" alt="Hover for full image"/></div></td>
  </tr><?php $i++;?> <?php
      }
      while ($row_brands = mysql_fetch_assoc($brands)); ?>
[/code]

brandname, brandkey, branddesc are the column names from the sqldatabase

I think that what I need to do is make the first query into a function that I can call from within nested do while loop is that correct - well until someone points me in another direction thats what i will do.

Lev

[Levan]

DON'T WORRY !!! I sorted it out
the answer of course was to use a INNER JOIN
SELECT * FROM brand INNER JOIN brandcat ON brand.brandkey = brandcat.BrandID WHERE brandcat.CatID = ?

Brilliant I am slowly getting the hang of this MySQL/PHP stuff

Lev