Category and Subcategory generation

[bundy]

Hello, i'm stuck with something.


[b][u]1 The Problem[/u][/b]

I have website that uses a directory like this...
[img]http://www.explorp.com/screenshot1.jpg[/img]

if you click the link let's say 'Products' you get this...
[img]http://www.explorp.com/screenshot2.jpg[/img]

now what I want is that the first three of the subcategories of screenshot 2 become links beneath the proper parent-category of screenshot 1
something like this (but [color=#666666]'subs'[/color] become the subcategories of the proper parent-category)...
[img]http://www.explorp.com/screenshot3.jpg[/img]


[b][u]2 The Code[/u][/b]

I give you the code where the table with where the categories are generated and displayed
[code]
<table width="100%" border="0" cellspacing="1" cellpadding="2" class="innertablestyle">
<?
    $cats_sql="select * from sbbleads_categories where sb_pid=$cid ";
    if($config["sb_cat_listing"]=="alpha")
  {
      $cats_sql.=" order by sb_cat_name";
  }
  else
  {
        $cats_sql.="  order by sb_order_index";
  }
  $cats_query=mysql_query($cats_sql);

  $cnt=1;
 
  while($cats=mysql_fetch_array($cats_query))
  {
      $rst_query=mysql_query("Select * from sbbleads_categories where sb_pid=".$cats["sb_id"] );
      $clist=$cats["sb_id"];
      while ( $rst=mysql_fetch_array($rst_query) )
      {
          $clist.="," . $rst["sb_id"];
          $thislist="-1," . $rst["sb_id"];
          while ( $rst=mysql_fetch_array($rst_query) )
          {
              $clist.="," . $rst["sb_id"];
  $thislist.="," . $rst["sb_id"];
          }
          $rst_query=mysql_query("Select * from sbbleads_categories where sb_pid in (" . $thislist . ")" );
        }

        $sbcat_str= " and  sb_cid IN (" .$clist . ")" ;
        $sbq_product_cat="select * from sbbleads_product_cats, sbbleads_products where sb_approved='yes' and sbbleads_products.sb_id=sbbleads_product_cats.sb_offer_id $sbcat_str";
        $sbproduct_count=mysql_num_rows(mysql_query($sbq_product_cat));

        $sbtotal=$sbproduct_count;
        if($cnt%2==1)
        {
        ?>
        <tr>
            <td width="20">&nbsp;</td>
            <?
  }
  ?>
            <td align="left">
    <font class='normal'>
        <a href="cat_products.php?cid=<? echo $cats["sb_id"];?>"><? echo $cats["sb_cat_name"]; ?></a>
    </font>
    <font class="totals">
        (<? echo $sbtotal; ?>)
    </font>
</td>
            <?
  if($cnt%2==0)
  {
  ?>
            </tr>
            <?
  }
  $cnt++;
        }
?>
</table>
[/code]


[b][i]3 The SQL Structure[/i][/b]

Here is a screenshot of the SQL structure from the table 'sbbleads_categories'
[img]http://www.explorp.com/screenshot4.jpg[/img]

and here is a screenshot browsing the table
[img]http://www.explorp.com/screenshot5.jpg[/img]

as you can see every category has a 'sb_id' and a 'sb_pid',
for example the parent-category 'Products' has a sb_pid=0 and a sb_id=70, then the subcategory 'Bottles' has a sb_id=79 and a sb_pid=70, then the subcategory 'Base Cup' has a sb_id=138 and a sb_pid=79 so the hierarchy is as follows

Products
  Bottles
      Base cup
          etc...

so the sb_pid of a subcategory equals the sb_id of the parent-category.

I'm really stuck here what to do  :-\

Thanx,
Bundy

[fenway]

You just have to drill down each level one at a time.