SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    show sub category if not empty

    hi all

    i have 5 categories in "category table". with 2 fields
    "category_id" and "category_name".
    Code:
    1. Adidas
    2. Reebok
    etc
    i have 10 sub categories in "sub categories" table with 2 fields
    "sub_category_id" and "sub_category_name".

    Code:
    1. Shoes
    2. Tshirts
    etc
    There are "category_id" and "sub_category_id" fields in the "product table" also.

    i m showing these sub categories in the <select>drop down on my product pages
    PHP Code:
    <select name="sub_category">
    $qry="select * from sub_categories";
    $result=mysql_query($qry);
    while(
    $row=mysql_fetch_array($result))
    {
    echo 
    "<option value='1'>$row['sub_category_name']</option>
    }
    </select> 
    When sub category "shoes" is selected from drop down, then all shoes are displayed fine.
    PHP Code:
    qry "select * from product_table where category_id=1 and sub_category = 1" 

    But i want if there is no products for sub category "shoes" then this sub category "shoes" should not be displayed in the drop down.

    vineet

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    You can resolve this using a better query, one that grabs the sub-categories based on whether or not there is an associated product.

    Something like the following should get this started in the right direction.
    Code:
    SELECT DISTINCT sub_cat.id, sub_cat.name FROM sub_categories AS sub_cat RIGHT JOIN product_table ON sub_cat.id = product_table.sub_category
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cpradio View Post
    You can resolve this using a better query, one that grabs the sub-categories based on whether or not there is an associated product.

    Something like the following should get this started in the right direction.
    Code:
    SELECT DISTINCT sub_cat.id, sub_cat.name FROM sub_categories AS sub_cat RIGHT JOIN product_table ON sub_cat.id = product_table.sub_category
    Wouldn't an INNER JOIN be the ideal way to go?

    Code:
    SELECT 
    sub_cat.id, sub_cat.name 
    FROM 
    sub_categories AS sub_cat 
    INNER JOIN product_table ON sub_cat.id = product_table.sub_category
    GROUP BY
    sub_cat.id, sub_cat.name

  4. #4
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi

    i m new to JOINS

    i tried the below code
    PHP Code:
    <select name="sub_category">
    <?php 
    $qry
    ="SELECT DISTINCT sub_cat.sub_category_id, sub_cat.sub_category_name FROM sub_categories AS sub_cat RIGHT JOIN product_table ON sub_cat.sub_category_id = product_table.sub_category_id";

    $result=mysql_query($result);
    while(
    $row=mysql_fetch_array($result))
    {
    echo 
    "<option value=" .$id
        if(isset(
    $_REQUEST['sub_category']))
        {
        if(
    $_REQUEST['sub_category']==$row['sub_category_id'])
        echo  
    ' selected="selected"'
        } 
        echo 
    ">";
        
        if(
    $row['sub_category_id'] == '')
        {echo 
    '';} 
        else
        {echo 
    $row['sub_category_name'];} "</option>";
    ?>
    </select>
    But still the empty sub_category_name is displayed in the drop down

    vineet

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Try my example. An inner join will display results that exist on both tables. Therefore my example will show only subcategories that have a product of that type in the product table. The group by clause will remove duplicates that would be caused from having more than one product in a certain category.

    See here: http://en.wikipedia.org/wiki/Join_%28SQL

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Wouldn't an INNER JOIN be the ideal way to go?

    Code:
    SELECT 
    sub_cat.id, sub_cat.name 
    FROM 
    sub_categories AS sub_cat 
    INNER JOIN product_table ON sub_cat.id = product_table.sub_category
    GROUP BY
    sub_cat.id, sub_cat.name
    Yes, I didn't get a chance to test it so I definitely could have it wrong I'm much better at JOINs when I can actually test them against my data versus doing it blind.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi wolfe

    The sub category table contains just id and name of the sub category. There is no product in it.

    I m really new to joins.

    i tried your example
    PHP Code:
    <select name="sub_category">
    <?php 
    $qry
    ="SELECT 
    sub_cat.sub_category_id, sub_cat.sub_category_name 
    FROM 
    sub_categories AS sub_cat 
    INNER JOIN product_table ON sub_cat.sub_category_id = product_table.sub_category_id
    GROUP BY
    sub_cat.sub_category_id, sub_cat.sub_category_name"
    ;

    $result=mysql_query($result);
    while(
    $row=mysql_fetch_array($result))
    {
    echo 
    "<option value=" .$id
        if(isset(
    $_REQUEST['sub_category']))
        {
        if(
    $_REQUEST['sub_category']==$row['sub_category_id'])
        echo  
    ' selected="selected"'
        } 
        echo 
    ">";
        
        if(
    $row['sub_category_id'] == '')
        {echo 
    '';} 
        else
        {echo 
    $row['sub_category_name'];} "</option>";
    ?>
    </select>
    but still the empty sub_categories are shown in drop down.

    vineet

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    I'm sorry, sub categories then. Youll want to do an inner join between the main category table and and sub category table

  9. #9
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I'm sorry, sub categories then. Youll want to do an inner join between the main category table and and sub category table
    Hi

    The main category table doesnt have sub_category_id field. How will they join ?

    Sub_category_id is in "product_table" and "sub_category" table

    hope i m making my question clear

    vineet

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    All of these queries work for me (granted I ran them on SQL Server, but there isn't any specific SQL Server syntax that wouldn't work for MySQL too)

    Code:
    SELECT DISTINCT s.SubCategoryId, s.Name FROM #SubCategories AS s 
    	RIGHT JOIN #Products AS p ON s.SubCategoryId = p.SubCategoryId
    Code:
    SELECT s.SubCategoryId, s.Name FROM #SubCategories AS s 
    	RIGHT JOIN #Products AS p ON s.SubCategoryId = p.SubCategoryId
    	GROUP BY s.SubCategoryId, s.Name
    Code:
    SELECT s.SubCategoryId, s.Name FROM #SubCategories AS s 
    	INNER JOIN #Products AS p ON s.SubCategoryId = p.SubCategoryId
    	GROUP BY s.SubCategoryId, s.Name
    My Test Data (this is SQL Server specific)
    Code:
    CREATE TABLE #SubCategories
    (
      SubCategoryId INT,
      Name VARCHAR(25)
    )
    
    INSERT INTO #SubCategories VALUES (1, 'Shoes')
    INSERT INTO #SubCategories VALUES (2, 'Boxes')
    INSERT INTO #SubCategories VALUES (3, 'Shirts')
    INSERT INTO #SubCategories VALUES (4, 'Games')
    
    CREATE TABLE #Products
    (
      ProductId INT,
      SubCategoryId INT
    )
    
    INSERT INTO #Products VALUES (1, 1)
    INSERT INTO #Products VALUES (2, 1) // create a second product with the same sub-category
    // omit sub-category 2
    INSERT INTO #Products VALUES (3, 3)
    INSERT INTO #Products VALUES (4, 4)
    Results
    Code:
    ID	Name
    ----------------
    1	Shoes
    3	Shirts
    4	Games
    Edit:

    @vinpkl ; your PHP code is incorrect, check your mysql_query statement, you have $result being passed into the function and you need to pass $qry
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    Code:
    SELECT DISTINCT s.SubCategoryId, s.Name FROM #SubCategories AS s 
    	RIGHT JOIN #Products AS p ON s.SubCategoryId = p.SubCategoryId
    if there are any products that don't have a category, this query will return a row with NULLs in the subcategory id and name

    those RIGHT JOIN queries should be INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if there are any products that don't have a category, this query will return a row with NULLs in the subcategory id and name

    those RIGHT JOIN queries should be INNER JOIN
    If I reversed the table order, it would have been fine though right? As that would prevent that issue... (granted if a sub_category had a null entry, it would return a null row).
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    a subcategory wouldn't have a null entry, though, would it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a subcategory wouldn't have a null entry, though, would it
    It "shouldn't"
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cpradio View Post
    It "shouldn't"
    so it would have a null id? nope, that would be the PK, and therefore not null

    it might have a null name, but that would be silly, wouldn't it
    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
  •