Show sub category if not empty

hi all

i have 5 categories in “category table”. with 2 fields
“category_id” and “category_name”.


1. Adidas
2. Reebok
etc

i have 10 sub categories in “sub categories” table with 2 fields
“sub_category_id” and “sub_category_name”.


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


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


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

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.

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?


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

hi

i m new to JOINS

i tried the below 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

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_(SQL

Yes, I didn’t get a chance to test it so I definitely could have it wrong :slight_smile: I’m much better at JOINs when I can actually test them against my data versus doing it blind.

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


<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

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

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)

SELECT DISTINCT s.SubCategoryId, s.Name FROM #SubCategories AS s 
	RIGHT JOIN #Products AS p ON s.SubCategoryId = p.SubCategoryId
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
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)

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

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

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

a subcategory wouldn’t have a null entry, though, would it :wink:

It “shouldn’t” :smiley:

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