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

first of all if you should escape your get input before you do a query (to prevent SQL injections) this: $folder_id = $_GET["category"]; should be: $folder_id = mysql_real_escape_string(stripslashes($_GET["category"])); — Tim, May 20 '11 at 06:11
I also suggest you to read the articles @Denis provided. You'll learn a lot :) I myself read those several times. — Rifat, May 20 '11 at 06:49
Use MySQL session variables: http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/. Also see this question for other options: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — orangepips, May 23 '11 at 19:07

score 4 · Answer 1 · edited Aug 05 '11 at 23:08

4

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

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.

edited Aug 05 '11 at 23:08

Paŭlo Ebermann

73,284
20
146
210

answered May 20 '11 at 06:28

Denis de Bernardy

75,850
13
131
154

tags can't be used in this case, only tree (with paths, I assume). So +1 for hierarchical link, but you could write answer more detailed. – OZ_ May 20 '11 at 06:44
1

Yeah, I figured. The multi-category queries will be very quite similar, though, whether they're tags or categories: his post2cat table will break down to the post2tag table discussed in the article. – Denis de Bernardy May 20 '11 at 06:48

score 0 · Answer 2 · answered May 20 '11 at 06:20

0

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 .

answered May 20 '11 at 06:20

amal

1,369
8
15

@Rifat and where in the question is it mentioned that the categories are not in a single-depth tree ? Quoting , '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.' – amal May 20 '11 at 06:55
the structure of the table itself is for multilevel isn't it? – Rifat May 20 '11 at 06:57
@Rifat the structure of the table support multilevel category hierarchy .... as well as single level , as well as a 2-level hierarchy . Based on the info the OP provided , it can be assumed he meant the single-level ( the simplest case ) . If not , he should have clarified. – amal May 20 '11 at 07:04

score 0 · Answer 3 · answered May 20 '11 at 06:29

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

MySQL: Getting Posts from Categories

3 Answers3