Show sub categories only if they have products in them

[dropbop]

Hi,

 

I have been working on an eCommerce platform for the last few months. Its almost complete but I need to do one thing and thats to only display sub categories if products exist in them.

 

This is the site craftgo.me (development site),

 

and this is how the categories are produced for a category page. ie. http://www.craftgo.me/category.php?cat_id=96

 

if($_GET['cat_id']) {
$parents_id = $_GET['cat_id'];
	$cat_num_rows_sql = "SELECT category_id, parent_id FROM mccategories WHERE parent_id = '$parents_id'";
	$cat_num_rows_result = mysql_query($cat_num_rows_sql);
	$cat_row_count = mysql_num_rows($cat_num_rows_result);

		if($cat_row_count == '0'){
			$current_cat_parent_sql = "SELECT parent_id FROM mccategories WHERE category_id = '$cat_id'";
			$current_cat_parent_result = mysql_query($current_cat_parent_sql);
			$cat_parent_row = mysql_fetch_array($current_cat_parent_result);
			$parent_id = $cat_parent_row['parent_id'];
		} else {
			$parent_id = $_GET['cat_id'];
		}
} 



$catsql = "SELECT category_id, category_name FROM mccategories WHERE cat_showing = '0' AND parent_id = '$parent_id' ORDER BY category_name";
$catresult = mysql_query($catsql);
while($catrow = mysql_fetch_array($catresult)) {

$category_id = $catrow['category_id'];
$cat_name = $catrow['category_name'];

	echo '<li><a href="category.php?cat_id=' . $category_id . '">' . $cat_name . '</a></li>';
}

 

In the mcproducts table the category id for each product is under 'product_category'

 

 

Im not even sure where to start to make this work, but if anyone could give me a pointer, that would be super.

 

Many thanks

Eoin

 

 

[scootstah]

You're trying to fetch additional results from a table that you have already fetched results from - this is completely unnecessary, you already have the information to work with.

 

How are you determining what is a subcategory and what isn't? How many levels deep can your subcategories go?

[dropbop]

You're trying to fetch additional results from a table that you have already fetched results from - this is completely unnecessary, you already have the information to work with.

 

I cant remember exactly why I did that, I really should start commenting on my code more... or I will end up lost in git myself lol

 

How are you determining what is a subcategory and what isn't? How many levels deep can your subcategories go?

 

A sub category is any category that has a parent. Categories can be as deep as we want, but we are sticking 1 sub level

 

Parent Cat > Sub Cat

 

Each cat have an ID and a Parent ID.

 

 

category_id

category_name

parent_id

cat_showing

1

Category One

0

0

2

Category Two

0

0

3

Sub Category One

1

0

4

Sub CategoryTwo

2

0

 

 

I suppose what I need to know/learn how to do is, to check the products table for any products that are in the sub categories of the selected (parent) category...  So if a visitor was on category?cid=20 and some of its sub cats had no products, I would rather not show them the categories so I dont waste there time looking through pages that have nothing in them.