Presenting DATA / QUERY issue

[kool_samule]

Hi Chaps,

 

I have a basic Online Shop Application:

 

 

tbl_category (cat_id, category, fk_cat_id, sub_cat_id, sub_category)

tbl_product (prod_id, fk_sub_cat_id, prod_title, prod_price, prod_thumb, prod_image, prod_price)

tbl_product_size (prod_size_id, prod_size)

tbl_product_has_size (prod_has_size_id, fk_prod_id, fk_prod_size_id, prod_quantity)

tbl_shopid (shop_id, shop_title)

tbl_shop_has_product (shop_has_prod_id, fk_shop_id, fk_prodid)

 

A product can be in more than one shop, have various sizes and different quantities of those sizes.

 

In the Backend Admin pages, I want to have a Shop Summary page, which will list the shops and its products + product quantities.

 

At the moment, I have a query:

 

// QUERY

mysql_select_db($database_dbconnect, $dbconnect);
$query_rsShop = "SELECT tbl_shop_has_prod.shop_has_prod_id, tbl_shop_has_prod.FK_shop_id, tbl_shop_has_prod.FK_prod_id, tbl_shop.shop_id, tbl_shop.shop_title, tbl_product.prodtitle, tbl_product_has_size.prod_quantity FROM tbl_shop_has_prod INNER JOIN tbl_shop ON tbl_shop.shop_id=tbl_shop_has_prod.FK_shop_id INNER JOIN tbl_product ON tbl_product.prod_id=tbl_shop_has_prod.FK_prod_id INNER JOIN tbl_product_has_size ON tbl_product_has_size.FK_prod_id=tbl_product.prod_id WHERE tbl_shop_has_prod.FK_shop_id=tbl_shop.shop_id AND tbl_product.prod_id=tbl_shop_has_prod.FK_prod_id";
$rsShop = mysql_query($query_rsShop, $dbconnect) or die(mysql_error());
$row_rsShop = mysql_fetch_assoc($rsShop);
$totalRows_rsShop = mysql_num_rows($rsShop);

 

 

// PHP

<table border="0" cellpadding="0" cellspacing="0" id="tblrepeat">
  <tr>
    <td>Shop</td>
    <td>Product</td>
    <td>Quantity</td>
  </tr>//
  <?php
      $previousShop = '';
      $previousProduct = '';
  if ($totalRows_rsShop > 0) {
      // Show if recordset not empty
      do {
      if ($previousShop != $row_rsShop['shop_id']) {
  // for every Shop, show the Shop Name
  //if ($previousProduct != $row_rsShop['prod_id']) {
      // for every Product, show the Product Name
  ?>
      <tr>
        <td><?php echo $row_rsShop['shop_title']; ?></td>
        <td> </td>
        <td> </td>
      </tr>
      <?php $previousShop = $row_rsShop['shop_id']; } ?>
      <tr>
      <td> </td>
      <td><?php echo $row_rsShop['prodtitle']; ?> </td>
      <td><?php echo $row_rsShop['prod_quantity']; ?></td>
    </tr>
    <?php //$previousProduct = $row_rsShop['proj_id']; } ?>
      <?php } while ($row_rsShop = mysql_fetch_assoc($rsShop)); ?>
      <?php } // Show if recordset not empty ?>
</table>

 

that produces something like this(e.g. for SHOP 1):

 

SHOP 1

BLUE DRESS (25 / Size S)

BLUE DRESS (25 / Size M)

BLUE DRESS (25 / Size L)

BLUE DRESS (25 / Size XL)

RED TOP (50 / Size M)

 

What I'm after is to produce something like this:

 

- SHOP 1

BLUE DRESS (Total: 100)

RED TOP (Total: 50)

- SHOP 2

RED TOP (Total: 50)

- SHOP 3

GREEN SKIRT (Total: 75)

How do I either alter my QUERY to group it together, or alter the PHP to produce the results as I require????? Arrrghhhh!

[aeroswat]

Use a count() and group by shop id, dress

[kool_samule]

JOB DONE!

 

SWEET,

 

Cheers dude!