SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT Count not working as wanted

    Hey everyone,

    I have the following SQL query:

    PHP Code:
        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
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by billy_111 View Post
    I think i need to change the SQL so it groups by brand?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/hangin...e=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/hangin...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:

    PHP Code:
            <?        
            $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

    Can you help?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •