How to select all articles from one category and sub-category In MySQL 5

Question

Three tables save articles information:

categories table:

id, lft, rgt, title

lft: left value

rgt: right value

lft and rgt value is Nested Set, example:

           root 
          (0,15)
          /    \
         /      \
       cat1    cat2
      (1,6)   (7, 14) 
       /|      / | \
      / |     /  |  \
     /  |    /   |   \
 cat3 cat4  cat5 cat6 cat7
(2,3) (4,5) (8,9)(10,11)(12,13)

article table:

id, title

article_category_map table:

article_id, category_id

How to select all articles from one category and sub-category in MySQL?

I expect:

1、When click cat2, display all articles of cat2 and cat5 and cat6 and cat7.

2、When click cat5 , only display all articles of cat5.

3、Wher click root, display all articles of all categories (include cat1, cat2, cat3,cat4, cat5, cat6, cat7...).

Whr is your subcat table, post that also. And, do you want to fetch the data for a known cat ID or you want to fetch the data based on dynamic cat ID? — Aparna

Ruslan Osmanov Ruslan Osmanov · Accepted Answer · 2016-05-09T03:06:17

I guess 'lft' and 'rgt' stand for "left" and "right" properties of the binary tree.

Suppose $category_id is ID of the category, Db::fetch method fetches single row from database, Db::fetchAll method fetches all rows matching an SQL query.

Then you can fetch "left" and "right" of the category, then fetch articles from the category and its subcategories.

if (! $cat = Db::fetch("SELECT lft, rgt
  FROM categories WHERE id = $category_id"))
{
  // handle error
}

$articles = Db::fetchAll("SELECT a.* FROM article a
  JOIN article_category_map m ON m.article_id = a.id
  JOIN categories c ON c.id = m.category_id
  WHERE c.id BETWEEN ${cat['lft']} AND {$cat['rgt']}");

How to select all articles from one category and sub-category In MySQL 5

1 Answers