show sub category if not empty

**vinpkl** · Oct 17, 2012 09:57

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

**cpradio** · Oct 17, 2012 10:23

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

**K. Wolfe** · Oct 17, 2012 11:06

Originally Posted by cpradio

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

**vinpkl** · Oct 17, 2012 12:02

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

**K. Wolfe** · Oct 17, 2012 12:14

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

**cpradio** · Oct 17, 2012 12:39

Originally Posted by K. Wolfe

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.

**vinpkl** · Oct 17, 2012 12:44

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

**K. Wolfe** · Oct 17, 2012 12:53

I'm sorry, sub categories then. Youll want to do an inner join between the main category table and and sub category table

**vinpkl** · Oct 17, 2012 13:00

Originally Posted by K. Wolfe

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

**cpradio** · Oct 17, 2012 13:13

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

**r937** · Oct 17, 2012 13:36

Originally Posted by cpradio

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

**cpradio** · Oct 17, 2012 13:58

Originally Posted by r937

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

**r937** · Oct 17, 2012 14:08

a subcategory wouldn't have a null entry, though, would it

**cpradio** · Oct 17, 2012 14:12

Originally Posted by r937

a subcategory wouldn't have a null entry, though, would it

It "shouldn't"

**r937** · Oct 17, 2012 14:26

Originally Posted by cpradio

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

User Tag List

Thread: show sub category if not empty

Thread Tools

Display

show sub category if not empty

Bookmarks

Bookmarks

Posting Permissions