Making a menu only of major categories with a count

[floridaflatlander]

I'm trying to make a menu that is a menu only of major categories with a count or it has sub-categories with a count.

Some major categories have sub-categories and some don't.

This morning this works but last night I was getting a record with a id_parent of  0 and a count of 0(and I don't know why).

Does this sql make since and would it work consistently?

 

$q = "SELECT item, slug FROM category WHERE (count >= 1 AND id_parent = 0) OR id_cat IN (SELECT id_parent FROM category WHERE count >= 1 AND id_parent >= 1)  GROUP BY item"; 

 

The table the menu comes from looks like this ..

id_cat | id_parent | slug | item | count

1 | 0 | boats | Boats | 0

2 | 0 | autos | Cars & Trucks | 0

3 | 1 | inshore-boats | Inshore Boats | 2

4 | 2 | trucks | Trucks | 1

5 | 0 | atvs | ATVs | 2

[lemmin]

That query should return rows 1,2, and 5. Both 1 and 2 have an id_parent of 0 and count of 0.

 

The reason it returns those is because sub categories exist that match your criteria of those fields being greater than 0 (as per your subquery).

 

What is the output you are trying to get from that data set?

[floridaflatlander]

Thanks for the reply

 

That query should return rows 1,2, and 5. Both 1 and 2 have an id_parent of 0 and count of 0.

....

What is the output you are trying to get from that data set?

 

The rows I'm trying to get are 1 2 & 5, the only problem I know of is that last night I was getting a row that had a 0 count a parent 0 with the same sql.

 

Anyway the sql above should only return rows 1 2 & 5? That's what I want.

[floridaflatlander]

I'm changing the IN part to  (SELECT id_parent FROM category WHERE count >= 1 AND id_parent >= 1 GROUP BY id_parent) maybe this would get rid of the little grimlin last night.

[floridaflatlander]

I found the problem, there was a subcategory with a count on the second page of the table in my phpmyadmin, when I checked the first time I only checked the category and it's subcategories in question on the first page. So it worked all along.

 

Thanks for the help lemmin