ดึงสินค้าขึนมาแสดง และ แจ้งจำนวนสินค้าด้วย (นับจำนวนเรคคอร์ต) เช่น มะพร้าว (20) ชมพู่ (10)

$sql = mysql_query("SELECT *, COUNT(b.cat_id) AS num_product FROM category a LEFT JOIN products b ON a.id = b.cat_id GROUP BY a.id ORDER BY a.id ASC") or die("DataBase Error!");

while($row=mysql_fetch_assoc($sql)){

echo $row['cat_name']." ";  // ชื่อหมวดสินค้าในตาราง category
echo "(".$row['num_product'].")<br>";  // จำนวนสินค้าที่มีในหมวดนั้นๆ

}

$sql = mysql_query("SELECT *, COUNT(b.cat_id) AS num_product FROM category a LEFT JOIN products b ON a.id = b.cat_id GROUP BY a.id ORDER BY a.id ASC") or die("DataBase Error!");

while($row=mysql_fetch_assoc($sql)){

echo $row['title']." ";  // ชื่อหมวดสินค้าในตาราง category
echo "(".$row['num_product'].")<br>";  // จำนวนสินค้าที่มีในหมวดนั้นๆ

}

 <?php
 $con = mysqli_connect('localhost', '', '', '');
 mysqli_query($con, "SET NAMES UTF8");
 
 $perpage = 12;
 if (isset($_GET['page'])) {
 $page = $_GET['page'];
 } else {
 $page = 1;
 }
 
 $start = ($page - 1) * $perpage;
 
 
 if(isset($_REQUEST['filter']))
 {
 $filter = $_REQUEST['filter'];
 $result = mysqli_query($con, "SELECT * FROM products where cat_id = '". $filter."' LIMIT ".$start.",".$perpage);
 $sql2 = "SELECT * FROM products WHERE cat_id = '". $filter."' ORDER BY ID ASC";
 }
 else
 {
 $result = mysqli_query($con, "SELECT * FROM products ORDER BY ID LIMIT ".$start.",".$perpage);
 $sql2 = "SELECT * FROM products ORDER BY ID ASC";
 }
 
 if($result){	
 
 while($row=mysqli_fetch_array($result)){	
 
 $prodID = $row["ID"];	
 
 echo '<ul class="col-sm-4">';
 echo '<div class="product-image-wrapper">
 
 <div class="single-products">
 <div class="productinfo text-center">
 
 
 <a href="product-details.php?prodid='.$prodID.'" rel="bookmark" title="'.$row['Product'].'"><img src="reservation/img/products/'.$row['imgUrl'].'" alt="'.$row['Product'].'" title="'.$row['Product'].'" width="219" height="150" /></a>
 
 <h5><a href="product-details.php?prodid='.$prodID.'" rel="bookmark" title="'.$row['Product'].'">'.$row['Product'].'</a></h5>
 <b> '.$row['Price'].' </b>THB : <a href="category.php?filter='.$row['Category'].'"> '.$row['Category'].' </p></a>
 
 <a href="product-details.php?prodid='.$prodID.'" class="btn active btn-dark btn-xs"><i class="fa fa-camera"></i> Details </a>
 </div>';	
 echo '</ul>';
 }
 }
 ?>
 
 <?php
 $query2 = mysqli_query($con, $sql2);
 $total_record = mysqli_num_rows($query2);
 $total_page = ceil($total_record / $perpage);
 
 if($page>=2){
   $Ppage = ($page-1);
 ?>
 
 </div>
 
 
 <hr>
 
 <div align="right">
 <nav>
 <ul class="pagination">
 <li><a href="index.php?page=<?php echo $Ppage ?>"></a></li></ul>
 
 <?php 
 }
 for($i=1;$i<=$total_page;$i++){ 
 $numPage = $page==$i?"[".$i."]":$i;
 ?>
 
 <ul class="pagination">
 <li><a href="index.php?page=<?php echo $i; ?>"><?php echo $numPage." "; ?></a></li></ul>
 
 <?php } 
 if($page<$total_page){
   $Npage = ($page+1);
 ?>
 
 <ul class="pagination">
  <li><a href="index.php?page=<?php echo $Npage;?>"></b></a></li>
 
 </ul>
 </nav>
 </div>
 
 <?php } ?>
 <!--php ends here-->