Adrian4263 Posted January 9, 2014 Share Posted January 9, 2014 $query = "SELECT COUNT(*) as totalno FROM product GROUP BY prodCat ORDER BY prodName"; $result = mysql_query($query); while($data=mysql_fetch_array($result)) { $count = $data['totalno']; $query6="SELECT * FROM categories ORDER BY category"; $result6=mysql_query($query6); while($row6=mysql_fetch_array($result6)) { ?> <li> <?php echo $row6['category']; ?> (<strong><?php echo $count['prodID']; ?></strong>) </br> </br> </li> <?php $query="SELECT * FROM product where prodCat='".$row6['category']."' LIMIT 6"; $result=mysql_query($query); while($row=mysql_fetch_array($result)) { ?> <div id="product"> <a href="pDetails.php?id=<?php echo $row['id']; ?>"> <img src="data:image/jpeg;base64,<?php echo base64_encode($row["prodImage"]);?>" width="190px" height="190px" /> </a> <p> <b><?php echo $row["prodID"]; ?></b> </p> <p><?php echo $row["prodName"]; ?></p> <p>RM<?php echo $row["prodPrice"]; ?></p> <p> <a href="pDetails.php?id=<?php echo $row['id']; ?>" class="button">Product Details</a> </p> </div> <?php } } } ?> prodCat is product category.prodName is product Name. now my output is.. Category - product quantity --------------------------- Accessories - 4 Mouse - 4 Monitor - 4 CPU - 4 Bags - 4 this was wrong. I want the output is... Category - product quantity --------------------------- Accessories - 11 Mouse - 2 Monitor - 0 CPU - 5 Bags - 0 based on database to show the product quantity. How could i write the code.. please help. Thanks in advance Regards. Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/ Share on other sites More sharing options...
Barand Posted January 9, 2014 Share Posted January 9, 2014 It would help us greatly to help you if you gave us your table structures and some sample fable data Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464550 Share on other sites More sharing options...
Adrian4263 Posted January 9, 2014 Author Share Posted January 9, 2014 Product table prodID prodName prodPrice prodCat prodDet homepage prodImage ------------------------------------------------------------------------- 123 Razer 150.00 Mouse 122 Pendrive 30.00 Accessories Categories table category -------- Mouse Accessories this two table is part of my database table structures Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464551 Share on other sites More sharing options...
Barand Posted January 9, 2014 Share Posted January 9, 2014 I'm only using partial data but this should give the idea $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT prodCat, prodName, prodPrice FROM product ORDER BY prodCat"; $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $products[$row['prodCat']][] = $row; } foreach ($products as $cat => $prodArray) { echo '<h3>' . $cat . ' - ' . count($prodArray) . '</h3>'; foreach ($prodArray as $p) { echo "<div style='border: 1px solid gray; margin-bottom: 10px; padding:5px'> <p>{$p['prodName']}</p> <p>{$p['prodPrice']}</p> </div>"; } } Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464556 Share on other sites More sharing options...
adam_bray Posted January 9, 2014 Share Posted January 9, 2014 Count done using a left join - <?php $query = mysql_query( ' SELECT categories.category , COALESCE(cat_products.totalProducts,0) AS `totalProds` FROM categories LEFT OUTER JOIN ( SELECT prodCat , COUNT(*) AS totalProducts FROM product GROUP BY prodCat ) AS cat_products ON cat_products.prodCat = categories.category ORDER BY categories.category ASC;' ) or die( 'Query Error on line ' . __LINE__ . '<br />' . mysql_error() ); if( mysql_num_rows( $query ) > 0 ) { $return_string = ''; while( $cats = mysql_fetch_array( $query ) ) { $return_string .= '<li>'.$cats['category'].' (<strong>'.$cats['totalProds'].'</strong>)</li>'; } print $return_string; } else { print '<h3>There are no categories to display!</h3>'; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464557 Share on other sites More sharing options...
Adrian4263 Posted January 11, 2014 Author Share Posted January 11, 2014 I'm only using partial data but this should give the idea $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT prodCat, prodName, prodPrice FROM product ORDER BY prodCat"; $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $products[$row['prodCat']][] = $row; } foreach ($products as $cat => $prodArray) { echo '<h3>' . $cat . ' - ' . count($prodArray) . '</h3>'; foreach ($prodArray as $p) { echo "<div style='border: 1px solid gray; margin-bottom: 10px; padding:5px'> <p>{$p['prodName']}</p> <p>{$p['prodPrice']}</p> </div>"; } } thanks for your answered. if i do not want this part of code ($db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE) then $res = $db->query($sql); while ($row = $res->fetch_assoc()) { $products[$row['prodCat']][] = $row; } how should i change the code here? Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464800 Share on other sites More sharing options...
jazzman1 Posted January 11, 2014 Share Posted January 11, 2014 You shouldn't use a set (series) of nested loops to select data from the tables in the way you done. We still need to know, what the structure of those given tables is? http://dev.mysql.com/doc/refman/5.0/en/getting-information.html Quote Link to comment https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464807 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.