Skip to content
Advertisement

How obtain a list of posts by category?

I have a MySQL table that associates articles with categories.

Table “posts_categories”:

postId - catId
178 4
177 4
176 4
175 4
174 4
171 4
170 4
169 4
168 4
167 4
166 4
165 4
164 4
163 4
162 4
151 3
150 3
149 3
147 3
1   1

On the page of article 170 “post.php? Id = 170”, I need to list all the posts in the same category. I need only show the post title. How do I make a list with all posts in category 4?

My SQL code is:

SELECT post_title
FROM posts_categories
INNER JOIN articles ON posts_categories.postId = articles.ID
INNER JOIN cursos ON posts_categories.catId = cursos.catId
WHERE posts_categories.postId ='" . $id . "'

Advertisement

Answer

Your query has a couple of issues, firstly

INNER JOIN articles ON posts_categories.postId = post.ID 

should be

INNER JOIN articles ON posts_categories.postId = articles.ID

Secondly, to get all the posts in the same category as the input post, you need to change your WHERE condition to:

WHERE posts_categories.catId = (SELECT catId FROM posts_categories WHERE postId = '" . $id . "')

So your query should be:

SELECT post_title
FROM posts_categories
INNER JOIN articles ON posts_categories.postId = articles.ID
INNER JOIN cursos ON posts_categories.catId = cursos.catId
WHERE posts_categories.catId = (SELECT catId FROM posts_categories WHERE postId = '" . $id . "')

Note that this query will leave you open to SQL injection (see this question). At the very least you should check that $id is a purely numeric value (you can use is_numeric), but ideally you should use prepared statements.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement