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.