Display articles based on category

MySQL database
#1

Hi there,

I’m trying to retrieve articles that have the same category.
I have 3 tables;

  • Articles (the article themselves)
  • Categories_per_articles (contains the id of articles and the categories associated with it)
  • Categories ( currently has 3 categories)

My SQL is trying say this; Can you please show me all the articles that are placed in the same category.

This is is what I’ve wrote;

SELECT * FROM blog, categories_per_article INNER JOIN category WHERE category.cat_id AND categories_per_article.cat_id = 1

And it brings me backeverything…

Any help will be ace!

Thanks

#2

Without knowing what your table definitions/column names are, it is not directly possible to help. However, since your query doesn’t contain any conditions relating the data between the tables - FROM blog, categories_per_article INNER JOIN category is joining every row in blog (which I’m guessing is the Articles table) with every row in categories_per_article with every row in category and - WHERE category.cat_id AND categories_per_article.cat_id = 1 is including every row where category.cat_id is true and every row where categories_per_article.cat_id = 1.

I recommend that you -

  1. List out the columns you are selecting from each table.
  2. Use an alias for each table, such as b (a) for blog (articles), ca for categories per article, and c for category and always include the alias with each column reference (even in those cases where it is not needed.) This makes your query easier to read and avoid mistakes.
  3. Always use explicit joins. The - blog, categories_per_article is equivalent of an INNER JOIN, but if you hand listed it as an INNER JOIN, with its join condition, it will make it easier to write queries that do what you want.
#3

You’re bang on in…

I’m currently writing it out now.

BLOG

blog_id (I’m using this id to place in the categories_per_article)
blog_title
blog_content

Categories_per_article

id
article_id (this will match the blog_id)
cat_id (this assigns the category they are in)

Category

cat_id (this will match the cat_id used in categories
cat_name

I’m going to have a crack on with this, and thank you for your help on this, very , much appreciate.

#4

UPDATE.

So i have managed to successfully retrieve the artices based on their matching id and grab them from the right category based on a parameter that will pass through a url variable

So this is what I have got.

SELECT blog.blog_title AS article
FROM blog
RIGHT JOIN categories_per_article
ON categories_per_article.article_id = blog.blog_id AND categories_per_article.cat_id = 1

So now, my only question is that is displays NULL on the non-matches, how would i simply omit them out?

Thanks

#5

If you are getting NULL articles, that would mean, with a RIGTH JOIN, that there are rows in the categories_per_article table with cat_id = 1 without a matching row in the blog table. Since the whole purpose of the categories_per_article table is that it only has rows for blog/articles that exist, it would seem you have incorrect data in the categories_per_article table. Could you post a sample of the data.

Once your data is correct, you would use just a JOIN query, because there will always be a row in the categories_per_article table with an article_id that matches a row in the blog table with the same blog_id.

You would use a LEFT (which is easier to visualize how it works) or a RIGHT join if you want all the rows in one table, such as if you were listing all the categories, regardless of if there are any matching rows using those categories.

#6

Hi there,

You are absolutely correct.

What i have done, and im guessing it’s not best practice but omitted the NULL rows from my query

SELECT blog.blog_title AS article
FROM blog
RIGHT JOIN categories_per_article
ON categories_per_article.article_id = blog.blog_id AND categories_per_article.cat_id = 1 IS NOT NULL

Thank you for your help…

Sponsor our Newsletter | Privacy Policy | Terms of Service