CODEWITHSUNDEEP
phpmysqlsql
Clinton
Clinton

Reputation: 33

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

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...).

Upvotes: 0

Views: 1084

Answers (1)

Ruslan Osmanov
Ruslan Osmanov

Reputation: 21492

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']}");

Upvotes: 0

Related Questions