CODEWITHSUNDEEP
phpmysqlnavigation
Kevlar
Kevlar

Reputation: 334

Dynamic navigation from database

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

Upvotes: 0

Views: 172

Answers (2)

cmorrissey
cmorrissey

Reputation: 8583

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

Upvotes: 2

Styphon
Styphon

Reputation: 10447

The following query should select all the primary categories that only have sub categories:

SELECT *, (SELECT count(*) FROM subCategories WHERE parentCatID = Categories.catID) as num FROM Categories WHERE num > 0

As you haven't given us table or column names I've made them. Replace with your actual table and column names.

EDIT Added in table / column names now a schema is available.

Upvotes: 2

Related Questions