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! Link to comment https://forums.phpfreaks.com/topic/198537-presenting-data-query-issue/ 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 Link to comment https://forums.phpfreaks.com/topic/198537-presenting-data-query-issue/#findComment-1041829 Share on other sites More sharing options...
kool_samule Posted April 14, 2010 Author Share Posted April 14, 2010 JOB DONE! SWEET, Cheers dude! Link to comment https://forums.phpfreaks.com/topic/198537-presenting-data-query-issue/#findComment-1041830 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.