How to display all the commentes related to a particular post? (sql query)

853 views

What I want?

I want to display all the comments that are submitted on a particular post. Like if i have post number 2 and it has four comments so I want to retrieve all these 4 comments with the post.

Problem having?

The problem is that,whenever I try to retrieve the comments related the particular post, I get only one comment one post. But not all comments onto that post.

Query I have written :

SELECT zcomment.icomment, zpost.zpostbody, zcomment.myid, zcomment.myname, zpost.zpostid from zcomment LEFT JOIN zpost on zpost.zpostid=zcomment.myid

The above query only print the single comment and single post.

I have used Auto_Increment in zpostid so that whenever any post is submitted the value of it should be inserted into the table.
I have used primary key in zpostid and have made it foreign key in the zcomment table.(with the name myid).

Can anyone help me out with this,i am having this issue for a long time.

closed with the note: Seems that problem is solved so closing the issue.

posted Aug 13, 2014 by anonymous

Share this question

Is it possible to share the table structure. Let me share QueryHome table design a bit which may help you to have table design. We have a single table which has postid, posttype (Question, Answer, Comment) and ParentPostid.

Which we need the comments on a particular post or answer we use the self join to get the info.

Again I can not comment on your query until unless I see the respective table structure.

commented Aug 14, 2014 by Salil Agrawal

Zpost(table)
-----------------------------------------------------------------------------------------------------
zpostid (primary key and auto_increment)
zpostbody(varchar)
zposttitle(varchar)

-----------------------------------------------------------------------------------------------------
Zcomment(table)

myid (primary key as well as auto_increment)
myname(varchar)
imail (email)
icomment(text)
________________________________________________________
*something is wrong in the tables
# For both comment and post, i have used auto_increment. I think i should use it only in zpostid not in myid otherwise if anyone will comment will have unique id and then it would not be possible to retrieve comments related to that post.
#How selfjoin and parentpostid can help?
# After seeing your comment i think i should use only table for post and comments both.
Ultimate Goat => whenever anyone(not logged in user) click on any post, he/she can get all the comments related to that post.

commented Aug 14, 2014 by anonymous

1 Answer

Obviously this will not work in Zcomment table you should have a field called zpostid which is pointing to which post it is related. Auto Increment is fine, also you need to join based on this new field something like -

SELECT zcomment.icomment, zpost.zpostbody, zcomment.myid, zcomment.myname, zpost.zpostid from zcomment LEFT JOIN zpost on zpost.zpostid=zcomment.zpostid

answer Aug 14, 2014 by Salil Agrawal

ok! I executed the query you have mentioned. It is giving the results i need. BUT here is a problem in this case,
As i have said 2 tables are there zpost & zcomment.
Zpostid column of the table zpost is primary key type. Now zpostid is incrementing perfectly whenever any user submit any post.
Comment should not be incrementing automatically. So can i assign the myid(comment id) to each comment. Because i can't use auto_increment in myid(comment id) column.
This mean i have to manually assign the myid(comment id) to each comment user submit.
Sir Can you share how the self join concept help me in this case. Or can you share any query for retrieving all comments and single post. It would be helpful. Thanks

commented Aug 16, 2014 by anonymous

I don't have your code but I don't see any issue in auto increment of comment table.

Self join is the case where table 1 and table 2 are same table but it will not be applicable in your case.

Let me know if have any doubt further, also request you to ask your friends to join so that larger community can get benefit from QueryHome services.

commented Aug 16, 2014 by Salil Agrawal

auto_increment in both will assign unique id to both. Which means means
this =>
postid
1
2
3
commid
1
2
3
In this situation finding comments related to postid1 would be impossible.
I am redesigning the database model for both the tables and then work on it.
Have recommended about queryhome. It is a great social network for programming problems.

commented Aug 16, 2014 by anonymous

Say u have post table which has post id as 1, 2, 3

Now say u have comment table which has comment-id as 1, 2, 3, 4, 5, 6 and post id as 1, 1, 2, 2, 3, 3 respectively. Now in this way comment-id can be auto increment.

commented Aug 16, 2014 by Salil Agrawal

GOT IT!
But here's a thing :
In postid of post table values are 1,2,3 => inserting through auto_increment
What about? 1,1,2,2,3,3 => How these values will be inserted into comment table.
Have already tried foreign key here but it didn't work. So how can i get these values. 1,1,2,2,3,3 into the comment table , postid

commented Aug 16, 2014 by anonymous

Did not get it, can you explain more clearly ??

commented Aug 17, 2014 by Salil Agrawal

Any update???

commented Aug 18, 2014 by Salil Agrawal

Similar Questions

0 votes

How to display the last record of the field in a input box using PHP?

I have php script form which I used to input Members Details of my system into the MySQL database. My requirement is to display the last member's details in a input box.

+1 vote

How can I Optimize the speed of a MySQL Select Query?

–1 vote

How to display previous record value from MySQL database during update?

I want to update my sql database record and want to populate the value of previous record. So anybody can help me to get the previous value of the record to show when the update require.

+1 vote

How to create a SQL table if table does not exists using php?

I am tring to create a new table into the database if the table name given is not present in database using Php .
This is the code that I am using .

  $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(!$conn)
    {
            die('Failed to connect to server: ' . mysql_error());
            exit;
    }
    $db_selected = mysql_select_db($dbname);
    if(!$db_selected)
    {
           $db_selected = " CREATE DATABASE $dbname ";
    }
    $usertb1 = mysql_query("select 1 from $usertb LIMIT 1");
    if( $usertb1 !== FALSE )
    {
           $sql = "CREATE TABLE IF NOT EXISTS $usertb ( uid INT(20) AUTO_INCREMENT UNIQUE KEY NOT NULL,name VARCHAR(40),email varchar(40) PRIMARY KEY NOT NULL)";
    }
    $typetb1 = mysql_query("select 1 from $typetb LIMIT 1");
    if( $typetb1 !== FALSE)
    {
           $sql1 = "CREATE TABLE IF NOT EXISTS $typetb( uid INT(20) NOT NULL, type ENUM('WEB','APP','CART') NOT NULL ,unsubscribe TINYINT(1) NOT NULL,bounce TINYINT(1) NOT NULL,complaint TINYINT(1) NOT NULL, PRIMARY KEY (uid,type), FOREIGN KEY(uid) WHERE promo_user(uid))";
    }

0 votes

I want to insert a value from an array into a column of sql table, but the content is not going into the table?

for($i=0;$i<=feof($getdata);$i++)
{
if (filter_var($data[$i][1], FILTER_VALIDATE_EMAIL)){
echo $data[$i][1];
$email=$data[$i][1];
$conn = mysqli_connect($dbhost,$dbuser,$dbpass, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
$sql ="INSERT INTO promo_user (uid,name,email) VALUES (,'', '$email')";
mysqli_query($sql,$conn);
mysqli_close($conn);

I am using the above code but there is something wrong with it,whenever i run the code the echo is working fine but the content does go into sql table

Please help

How to display all the commentes related to a particular post? (sql query) [CLOSED]

1 Answer

Your comment on this answer: