在主要类别PHP MYSQL中显示子类别的文章

I have a category table

cat_id, cat_name, parent_id, cat_slug

and I have article table as follow

art_id, cat_id, art_title, art_content, author_id .....

and my users table as

user_id, username, pw, .....

so with these data, I want to display all articles which are listed under sub category

for example:

url of main category is: mydomain.com/cat/business url of sub category is: mydomian.com/cat/business/advertising

so when people visit the main category, then it should display the subcategories listed articles on main category even though the cat_id is not mentioned as main category. I want to list out all sub categories of business's subcategories articles on business category page.

what is the query, i should try to achieve this

help will be appreciated. thanks

First, to get your categories...

If your category nesting is only two levels deep (parent and child) or you are only interested in direct sub-categories (i.e. not subcategories of subcategories), then your SQL is pretty simple:

SELECT cat_id FROM category_table WHERE cat_id = $catid OR parent_id = $catid

If your nesting is deeper, then you'll need to recursively query as far down as your category nesting goes. For example, if you limit yourself to 3 levels, you can do this with SQL like this:

SELECT cat_id FROM category_table ct1
WHERE cat_id = $catid OR parent_id = $catid
OR parent_id in (SELECT cat_id FROM category_table WHERE parent_id = ct1.cat_id)

Similarly, for a 4-level deep nesting, you can make the SQL even more complicated:

SELECT cat_id FROM category_table ct1
WHERE cat_id = $catid OR parent_id = $catid
OR parent_id in (SELECT cat_id FROM category_table ct2 WHERE parent_id = ct1.cat_id
                 OR parent_id in
                 (SELECT cat_id FROM category_table ct3 WHERE parent_id = ct2.cat_id))

And so on.

If you do not have a fixed nesting depth, then you'll need to do the looping in the code.

Once you have all categories, then you query articles. So for a simple 2-level dependency, you'll get

SELECT * FROM article_table
WHERE cat_id in (SELECT cat_id FROM category_table WHERE cat_id = $catid OR parent_id = $catid)

Similarly, for a 3-level nesting, you'll get

SELECT * FROM article_table
WHERE cat_id in
    (SELECT cat_id FROM category_table ct1
     WHERE cat_id = $catid OR parent_id = $catid
     OR parent_id in (SELECT cat_id FROM category_table WHERE parent_id = ct1.cat_id))

And so on...

select * from category where parent_id=cat_id

Here for a given cat_id,it will fetch all the sub_categories for this.