How to select count?

[Adrian4263]

$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.

[Barand]

It would help us greatly to help you if you gave us your table structures and some sample fable data

[Adrian4263]

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

[Barand]

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>";
    }
}

[adam_bray]

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>';	
	}

?>

[Adrian4263]

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?

[jazzman1]

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