kool_samule Posted April 14, 2010 Share Posted April 14, 2010 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! Quote Link to comment Share on other sites More sharing options...
aeroswat Posted April 14, 2010 Share Posted April 14, 2010 Use a count() and group by shop id, dress Quote Link to comment Share on other sites More sharing options...
kool_samule Posted April 14, 2010 Author Share Posted April 14, 2010 JOB DONE! SWEET, Cheers dude! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.