MySQL: Getting Posts from Categories

I'm trying to learn MySQL so I have created a little blog system.

I have 3 tables in MySQL:

posts :

id    |  title      
----------------
1     |  Post Title 1         
2     |  Post Title 2  

categories :

id    |  title          | parent
--------------------------------
10     |  category10    | 0 
11     |  category11    | 0
12     |  category12    | 10 

post_category_relations :

id    |  post_id   |   category_id
----------------------------------
1     |  1         |   10
2     |  2         |   12
3     |  3         |   11

Each post can have multiple categories, their relation is stored in post_category_relations:

So when I visit index.php?category=10 , I would like to get each post what is related to category10 including the posts from its child folder category12 as well.

My Unfinished Snippet in PHP

$folder_id = $_GET["category"]; // Get Cat开发者_如何转开发egory ID from the URL
$sql = "SELECT * FROM posts 
          JOIN categories
          JOIN post_category_relations
        // And I don't really know what should I do here
        // because I need the child categories first, then the relations
        // then I can get the post too from the post_id of the relations
       ";
mysql_query($sql);

I know that this will require advanced MySQL skills, but any help is appreciated! I already made this in PHP but I need to use 4 loops which is not the best way to do it when it's possible in MySQL, I just don't know yet how :)

You'll probably find these articles by Phillip Keller interesting:

• Tags: Database schemas
• Tagsystems: performance tests

They cover tags, but your queries (i.e. category1 and category2 vs category1 or category2, and the one you're trying to write) will be nearly identical.

See also this discussion on indexing hierarchical data: Managing Hierarchical Data in MySQL.

As well as the multitudes of threads on SO that related to nested sets, tags, categories, etc.

I'm not in a position to test my query , but I believe something like

select * from posts,post_category_relations where post.id=post_category_relations.post_id and
post_category_relations.category_id in (select id from categories where id=? or parent=?)

is what you are looking for .

This is one SQL:

 # Take post from castegory $cat_id
    (SELECT P.* 
        FROM 
          posts P, post_category_relations PR 
    WHERE 
       PR.category_id = {$cat_id} AND PR.post_id = P.id
     )
    UNION
    # Take all post from $cat_id child categories
    (SELECT P.* 
        FROM 
          posts P, post_category_relations PR, categories C
    WHERE 
       PR.category_id = C.parent AND PR.post_id = P.id 
       AND C.id = {$cat_id}
     )