Dynamic navigation from database

Question

I have 2 tables in my database. One for product categories and one for sub categories.

The sub category table has a field holding their parent category IDs

Now im trying to create navigation using this information but am struggling to fetch only the categories out of the database that have a sub category.

Table "Categories":

catID | catName | active | image

Table "subCategories":

catID | catName | parentCatID | active | image

so far i have tried this:

$sql = mysqli_query($con,'SELECT DISTINCT parentCatID FROM *****');
while($row = $sql->fetch_row()) {
  $rows[]=$row;
}                   
$res = join(',',$rows);
$sql2 = mysqli_query($con,"SELECT * FROM ***** WHERE categoryID IN '" . $res . "'");
while($results2 = mysqli_fetch_array($sql2)){
    echo $results2['categoryName'];
}

This doesnt work and I cant see a solution. It's easy enough to just select all the categories. But I dont want the ones that dont have a sub category

Edit your question to include the schemas of the tables you're discussing, so that potential answerers will be able to see what we're dealing with. — Aaron Miller

cmorrissey cmorrissey · Accepted Answer · 2013-07-23T15:48:56

I would use an INNER JOIN

SELECT *
FROM Categories
    INNER JOIN subCategories ON subCategories.parentCatID = Categories.catID AND subCategories.active = 1
WHERE Categories.active = 1
GROUP BY Categories.catID

Edited to reflect your structure, you will need to update the "active = 1" based on how you have that set up

Dynamic navigation from database

2 Answers