I've got a few queries that are currently working, but not to be extended, and I think the way to do it is with joins.
I need to change this so it only counts channels:PHP Code:
SELECT count(ch.CHID) AS total_count FROM channel ch WHERE ch.active = '1'
- that have videos which belong to sub categories of a certain main category.
The links / joins are:
- a channel can have many videos. (channel table AS ch)
- each video can belong to 1 sub category (video table AS v)
- 1 sub category can belong to numerous main categories. (category table AS c). The categories table stores the main categories of a subcategory in 1 field called "Niche" which holds the data like |0|2|. So I use " from category c where c.niche LIKE '%|0|%' " to select sub categories that belong to main category 0.
Hope that makes some sense. One other query that need to be extended like above i s:
SELECT * from channel ch where ch.active = 1 ORDER BY ch.sort_order ASC, ch.name ASC LIMIT 10