MySQL: Getting Posts from Categories

Question

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 Category 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 :)

Answer 1

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.