Mysql中的递归SELECT查询？

Question

I got two following tables

category:(one to many-----question)

id name  parentId
1  test1  0
2  test2  1
3  test3  1
4  test4  3 .....

like a tree

test1
  test2
  test3
    test4

question:

id   title      category_id
1   question1    1
2   ....         1
3   ....         2

my question is : if i search category id = 1 ,there will be print total questions:

count(question.id)
     3

How to build Select query to do that? is that possible?

Thank you for your valuable help.

Answer 1

This will return count of questions for your selected category, here in example category_id = 1:

mysqli_query('SELECT COUNT(id) FROM question WHERE category_id = 1');

Updated: so, if you want to count it also for subcategories, the simpliest way will be to have "path" in your category table, where will be all IDs (self ID and ID-s of all parents), and you can separate it with ~ (its important to have ~ also at the beginning and end of path; path can be VARCHAR(255), but if you want have really deep tree, you can use TEXT.

id name   parentId  path
1  test1  0         ~1~
2  test2  1         ~1~2~
3  test3  1         ~1~3~
4  test4  3         ~1~3~4~

Hope, its clear enough, how you will update your table category to have there also column path.

And the select then will be:

mysqli_query('
  SELECT COUNT(id)
  FROM question
  WHERE category_id IN (
    SELECT id
    FROM category
    WHERE path LIKE "%~'.$category_id.'~%"
  )
');

$category_id will be ID of category, for which you want to count questions (also for subcategories).