Select query in mysql

[msz900]

I want to select total comments posted on post. Somehow I am able to do it but the problem is that the total comment will show under each post even if there is no comment on the post.

Problem: Not able to get the comment related to post.

What I want :

To select total comment related to each post.

What I try :

   SELECT SUM(total_comment) AS comment 
    FROM   user_comment 
           INNER JOIN post 
                   ON user_comment.image_id = post.id 
    WHERE  status = 0 

  SELECT SUM(total_comment) AS comment 
    FROM   user_comment 
    WHERE  status = 0

Both the queries return all (total) comment on all posts but I want only show comment related to specific post.

My post table

My Comment table

Result look like

OR

But i want like

OR

[r937]

[quote=“msz900, post:1, topic:107114”]
What I want :

To select total comment related to each post.[/quote]

what you need to do is define a foreign key in the comment table, which will contain the post id of the post that each comment is related to

[msz900]

please provide a code example.

[r937]

@msz900

please provide a code example.

sure, no problem

CREATE TABLE users ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , username VARCHAR(37) NOT NULL , profile_picture VARCHAR(99) ); CREATE TABLE images ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , image_name VARCHAR(99) NOT NULL ); CREATE TABLE comments ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT , user_id INTEGER NOT NULL , FOREIGN KEY ( user_id ) REFERENCES users ( id ) , image_id INTEGER , FOREIGN KEY ( image_id ) REFERENCES images ( id ) , COMMENT TEXT NOT NULL , on_time DATETIME NOT NULL , status TINYINT NOT NULL );

[msz900]

this is the descriptive like of my question, please read the question and the update section… i will also show the result and some code…

http://stackoverflow.com/questions/27406086/select-query-in-mysql

[r937]

sorry, that makes it a lot harder for me to understand what’s going on

it seems like the people replying on stackoverflow don’t understand what you’re doing either

[msz900]

yes, that’s the reason i post all of my index.php code where i am doing all the things…
I just want to show number of comments under each post. but the result are not what i want some time it returns comment 3 under each post even there is no comment on the post.

[r937]

@msz900

…some time it returns comment 3 under each post even there is no comment on the post.

did you implement the foreign key as i suggested?

[msz900]

not working

[r937]

@msz900

not working

it would if you did it right

but since you didn’t show anything, we cannot help any further