Show sub category if not empty

Databases
1

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

2

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
3

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
4

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

5

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

6

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.

7

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

8

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

9

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

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

11

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

12

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

13

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

14

It “shouldn’t” :smiley:

15

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