displaying sql records in a certain way

[street_spirit]

Hi, having trouble working out how to get my records in databases to display the way I want. Basically the structure is I have Catogories and within them I have sub catgories and attached to these are products. What I want to do is on one page have all the categories or sub catgories as headings and underneath each heading list the products that are inside it.

 

Better explanation of database structure, largely the same: http://www.phpwebcommerce.com/shopping-cart-database-design.php

 

So far I have almost what I want, but this just displays the product name with the subcategory duplicated for every product when I want it to be all products with just the sub category printed once. Any ideas?

 

<?php

if (!defined('WEB_ROOT')) {

exit;

}

 

 

if (isset($_GET['catId']) && (int)$_GET['catId'] > 0) {

$catId = (int)$_GET['catId'];

$sql2 = " AND p.cat_id = $catId";

$queryString = "catId=$catId";

} else {

$catId = 0;

$sql2  = '';

$queryString = '';

}

 

// for paging

// how many rows to show per page

$rowsPerPage = 10000;

 

$sql = "SELECT pd_id, c.cat_id, cat_name, pd_name

        FROM tbl_product p, tbl_category c

WHERE p.cat_id = c.cat_id $sql2

ORDER BY cat_name";

$result    = dbQuery(getPagingQuery($sql, $rowsPerPage));

$pagingLink = getPagingLink($sql, $rowsPerPage, $queryString);

 

//$categoryList = buildCategoryOptions($catId);

 

?>

<p> </p>

 

  <?php

$parentId = 0;

if (dbNumRows($result) > 0) {

$i = 0;

 

while($row = dbFetchAssoc($result)) {

extract($row);

 

$imgsql = "SELECT pd_thumbnail FROM tbl_product_image WHERE pd_id = {$pd_id} LIMIT 1";

$imgrslt = dbQuery($imgsql);

$imgrow = dbFetchAssoc($imgrslt);

$pd_thumbnail = $imgrow['pd_thumbnail'];

 

 

if ($pd_thumbnail) {

$pd_thumbnail = WEB_ROOT . 'images/product/' . $pd_thumbnail;

} else {

$pd_thumbnail = WEB_ROOT . 'images/no-image-small.png';

}

 

 

 

if ($i%2) {

$class = '';

} else {

$class = '';

}

 

$i += 1;

?>

  <table width="200" border="0">

    <tr>

      <td><span class="class2"><?php echo $class; ?><a href="../index.php?c=&p=<?php echo $pd_id; ?>"><?php echo $pd_name; ?></a>

<a href="../index.php?c=<?php echo $cat_id; ?>"><?php echo $cat_name; ?></a></span></td>

    </tr>

  </table>

 

  <?php

} // end while

?>

 

  <?php

echo $pagingLink;

  ?>

<?php

} else {

?>

  <table width="200" border="0">

    <tr>

      <td>No Products Yet

      <?php

}

?></td>

    </tr>

  </table>

[ignace]

do your subcategories have sub-subcategories? if not then you need to alter your db design to:

 

[categories](1)---(*)[subcategories]

 

if these subcategories can have subcategories then you need to add a column (parent_id) to your categories table to allow recursion

 

+---------------+
| categories    |
+---------------+
| id INT        |
| parent_id INT |
+---------------+

 

Now if you add a subcategory you set the id of the parent category in the parent_id

 

INSERT INTO categories (id, parent_id) VALUES (1, 0); // parent category
INSERT INTO categories (id, parent_id) VALUES (2, 1); // child category
INSERT INTO categories (id, parent_id) VALUES (3, 2); // child-child category
INSERT INTO categories (id, parent_id) VALUES (4, 3); // child-child-child category
..

[ignace]

If you only have a category and a subcategory and nothing deeper then use:

 

[categories](1)---(*)[subcategories]

 

This has the advantage that no products can be coupled to a parent category