Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464551
Share on other sites

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>";
    }
}
Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464556
Share on other sites

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

?>
Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464557
Share on other sites

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?
Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464800
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/285228-how-to-select-count/#findComment-1464807
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.