Jump to content

displaying sql records in a certain way


street_spirit

Recommended Posts

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>

Link to comment
https://forums.phpfreaks.com/topic/165076-displaying-sql-records-in-a-certain-way/
Share on other sites

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

Archived

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

×
×
  • Create New...

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.