SELECT Count not working as wanted

Databases
1

Hey everyone,

I have the following SQL query:


    public function selectAllProductsByCatID($catID){

        $sql = "SELECT tbl_products.*, brands.brandCount
				FROM tbl_products INNER JOIN (
				    SELECT brand, count(brand) AS brandCount
				    FROM tbl_products
				    GROUP BY brand
				) AS brands ON tbl_products.brand = brands.brand
				WHERE
				    catID = $catID
				    AND deleted = 0
				ORDER BY brand, name";
        $result = mysql_query($sql) or die(mysql_error());
        return $result;
    }

My table is as follows:

ID, brand, name, cost, country, date_added, catID, deleted.

What i want to do is check the brand of a product. You can see a typical page here.

Notice the line “brandCount is 31” and “brandCount is 2”, this retuns the number of rows but it should be ONE, I think i need to change the SQL so it groups by brand? I did try doing this as you can see in my sql above but it doesnt work. Or should i use LIMIT?

Can someone please help?

Thanks again

2

check your query again, it already groups by brand

what did you mean, the count should be ONE? why?

as far as i can tell, your sample page lists 31 brands of beer, so the count of beer brands is correct

3

Hey,

the brand of beer is correct, i understand that.

However, what I’m trying to accomplish is to show the Brand ONLY if there is more than one type of brand.

For example, If you look at this page:

http://www.freemanholland.com/hanging/product-range/?catID=1&type=White%20wine

There are many different types of Wines: Aromatic, Aromatic Magnum, Blended etc…

You can see these by scrolling down the page, they are displayed in subheadings which is perfect and exactly how i want it. However if you then look at this page:

http://www.freemanholland.com/hanging/product-range/?catID=6&type=Dessert

There is only one type of brand called “Dessert”. So there is no need to show “Dessert” as a heading as there is only one type, you see what i mean?

My code to display the page is shown below:


        <?		
        $products = Products::selectAllProductsByCatID($_GET['catID']);
        ?>
			<table cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th style="width: 400px; text-align:left; color:#b92923; font-weight:bold">Name</th>
                        <th style="width: 103px; text-align:left; color:#b92923; font-weight:bold">Cost:</th>
                        <th style="color:#b92923; font-weight:bold; text-align:left;">Country</th>
                    </tr>
                </thead>
        <?
		//start iterating   
        $saveBrand = '';
        $i=0;
            while($row = mysql_fetch_array($products)) {  
                if($row['brand'] != $saveBrand) { 
	        		if($row['brandCount'] > 1){           
	                    echo "<tr class='tdrow' style='font-size: 16px; height: 20px; line-height: 38px; border-bottom:1px solid #999'><td>$row[brand]</td></tr>";               
	                    $cycle = false;
	                    $saveBrand = $row['brand'];   
	                }              
                }          
                echo "<tr class=\\"row$i\\"><td class='tdrow'>$row[name]</td><td class='tdrow'>&pound;$row[cost]</td><td class='tdrow'>$row[country]</td></tr>"; 
                $i++;
			    $i = $i % 2;   
			    echo "<tr><td><strong>brandCount is $row[brandCount]</strong></td></tr>";         
            }      
            ?>	 
           </table>

So the reason why i tried doing this: if($row[‘brandCount’] > 1) is because i wanted to check to see if there was only one type of “brand” and if not show the name of the brand.

Apologies if i have confused you :rolleyes:

Can you help?

4

sorry, can’t help you, i don’t do php

you’ll want some kind of IF statement to determine if the number is greater than 1

and you’ll also want to display the brand only at the top of the list, rather than for each entry

don’t you already have another thread going in the php forum?