Presenting DATA / QUERY issue

PHP
1

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>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <?php $previousShop = $row_rsShop['shop_id']; } ?>
      <tr>
      <td>&nbsp;</td>
      <td><?php echo $row_rsShop['prodtitle']; ?>&nbsp;</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!

2

use a sum() and group by shop_id, prod_id

sorted