CODEWITHSUNDEEP
phpmysqlinner-join
DoomageAplentty
DoomageAplentty

Reputation: 2732

MySQL Join Returns too many Rows

I'm relatively new to MySQL and I am stumped with this section.

I am building a simple blog page which will display a blog along with the comments associated with it. I currently have two tables to handle this data:

Blogs

.blog_id (primary), .blog_title, .blog_date, .blog_post

Comments

.comment_id (primary), .blog_id, .comment_name, .comment

What I am trying to do is pull a specific blog which matches an ID and pull the comments tied to that blog. Here is my query:

SELECT * FROM blogs JOIN comments ON blogs.blog_id=comments.blog_id WHERE blogs.blog_id = $active ORDER BY comments.comment_id

This query results in pulling the correct information but if there are two comments on the blog, it will display the blog and everything twice as it's looping through the comments. I want to display all the blog information once and then have it loop through the comments.

I hope I explained this clearly. Any help would be fantastic. Thank you.

Upvotes: 2

Views: 4120

Answers (3)

Nazrul Muhaimin
Nazrul Muhaimin

Reputation: 373

There are two section that you need to look into, one is your query and another is how you display the things.

I've just found some simple tool to build MYSQL query and analyse the you query, you can even connect your database with the website.

You can search in google chrome market or go via below link.

I'm just give a suggestion and direction rather than actually solve it.

MYSQL query builder

Upvotes: 0

Emw
Emw

Reputation: 1665

There are two ways you could approach this:

If you absolutely need to do this in one query, use your joined SQL and iterate over the results using the blog's ID as a unique key:

$blogId = 0;
while ( null != ($row = mysql_fetch_assoc( $result ) ) )
{
  if ( $row['blog_id'] != $blogId )
  {
     // Display blog contents here
     $blogId = $row['blog_id'];
  }
  // Print coment
}

The more efficient way is to run this as two queries. First Select the Blog information, with a count of POSTS:

SELECT blog_id, blog_title, blog_date, blog_post, COUNT(*) as comment_count
FROM blogs INNER JOIN comments ON blogs.blog_id = comments.blog_id
WHERE blog_id = $blogId
GROUP BY blog_id, blog_title, blog_date, blog_post;

or, using a sub-select:

SELECT blog_id, blog_title, blog_date,
    (SELECT COUNT(*) FROM comments WHERE blog_id = blog.blog_id) as comment_count
FROM blogs
WHERE blog_id = $blogId
  • Only if comment_count is greather than zero, select the comments separately and display.

Not to mention there are different kinds of blog views; on an index page, the second query would be all you need to pull the blog summary and a count of comments on each post, without needing to display the contents of each comment.

Upvotes: 0

Kibbee
Kibbee

Reputation: 66122

The behavior you are describing is what a join is supposed to do. If you only want 1 record for each of the blog entries, then you need to use group by. To get a list of all the blogs records with the number of comments you can do a query like:

SELECT blogs.*,Count(comments.comment_ID) 
FROM blogs 
LEFT JOIN comments 
ON blogs.blog_id=comments.blog_id WHERE blogs.blog_id = $active 
ORDER BY comments.comment_id
GROUP By Blog.BlogID

However, you said you wanted to display just the blog, and then the comments associated with that blog. In that case you would most likely just use an initial query to pull all the blogs, and then another query as you loop through all the blog entries to get the comments for each blog. Here is some pseudocode.

select * FROM blogs

For each record from above
    Code for displaying blog goes here

    Select * FROM Comments where Blog_id = $blogID

    For each comment from above query
        code for displaying comment goes here

    Next COmment

Next Blog

You could just do a single select for all the comments, which would be more efficient, but makes the resulting code more complex as you have to find the records using php in the resulting list. It's mush simpler to just use multiple queries, and for most blogs (10 articles on a page, max) it won't make much of a difference. If you really want to use a single query, you can structure your program as in the pseudocode below.

SELECT * FROM blogs 
LEFT JOIN comments ON blogs.blog_id=comments.blog_id 
WHERE blogs.blog_id = $active ORDER BY blogs.blog_id, comments.comment_id

$LastBlogID = -1

FOR EACH Record
    If $LastBlogID != record['blog_id']
        Display Blog Information
        $LastBlogID = record['blog_id']
    End If

    Display Comment Information
Next Record

Upvotes: 3

Related Questions