Print Posts from all categories and their categories names

Question

I try to achieve the following:

Some great post title (posted in Sport)
Another title of a post here (posted in Space)
Cool title of some post (posted in Technologies)
And so on...

I display all posts from all categories and put the category name next to each post. I do not understand where and why fail. All posts are multiplied by the number of categories. Here is what I have till now:

posts table:

post_id
post_title
post_content
category_id



categories table:

category_id
category_name
category_description

And my queries and PHP code:

// I select all categories with their id and name
$stmt = $db->prepare("SELECT * FROM categories");
$stmt->execute();
$row_categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

// I select all posts too
$stmt = $db->prepare("SELECT * FROM posts");
$stmt->execute();
$row_posts_all = $stmt->fetchAll(PDO::FETCH_ASSOC);

And then:

foreach ($row_posts_all as $array_index_number => $name_of_index) {

    foreach ($row_categories as $array_index_number_categories => $name_of_index_category) {

        print $name_of_index['post_title'] . " posted in: " . $name_of_index_category['category_name'] . "<br><br>";
    }
}

I think this two foreaches part do things wrong. I can do one foreach to display all the posts but I do not know how to get their category names and put them next.

Do you have the reference of category in your post table ? What is that column name ? — Rikesh, Feb 07 '14 at 12:40
Yes! I am sorry! I forgot one field. I updated my question. I have "category_id" in the `posts` table — Liam James, Feb 07 '14 at 12:42

score 1 · Answer 1 · answered Feb 07 '14 at 12:47

1

That's the reason we have JOINS,

SELECT * from posts p
LEFT JOIN categories c on c.id = p.category_id

And then you can simply need a single loop,

foreach ($row_posts_all as $array_index_number => $name_of_index) {
    echo $name_of_index['post_title'] . " posted in: " . $name_of_index['category_name'] . "<br><br>";
}

answered Feb 07 '14 at 12:47

Rikesh

26,156
14
79
87

Thank you! I will read about it and practice it now as I do not understand what "p" and "c" are. – Liam James Feb 07 '14 at 12:48
They are nothing but an alias for the tables. – Rikesh Feb 07 '14 at 12:49
Thank you! I will try and practice it now. Can you suggest me some reference or idea how can I upgrade the example when posts may have categories and subcategories either. When I search in google about categories, posts etc.. all results are WordPress related. :-/ – Liam James Feb 07 '14 at 12:55
In that case refer this question http://stackoverflow.com/questions/8542201/how-to-join-category-table-for-parents-in-sql-query – Rikesh Feb 07 '14 at 12:57

score 0 · Answer 2 · answered Feb 07 '14 at 12:45

0

select P.*, C.* from posts P
inner join categories C on C.category_id = P.category_id

You don't need 2 sql requests. So you don't need 2 loops

answered Feb 07 '14 at 12:45

Pierre Granger

1,993
2
15
21

Can I achieve the idea without joins because I have not practiced them yet. Do I have to practice joins now to accomplish what I try? – Liam James Feb 07 '14 at 12:47

Print Posts from all categories and their categories names

2 Answers2