Jump to content


This topic is now archived and is closed to further replies.


Nested SQL queries

Recommended Posts

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...

$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);


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
    <?php $i=1;
    <?php do { ?>
    if ($i % 2 == 0){$bg_color="#FFFF99";}
  <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>
        <p><?php echo $row_brands['branddesc']; ?>
      <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)); ?>

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.


Share this post

Link to post
Share on other sites
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


Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.