Join users and comments tables together?

can i add users with comments table?

users table

https://imgur.com/8FNDvpp

comments table

https://imgur.com/3ZsQQEv

Yes - you need a SQL join query: https://www.w3schools.com/sql/sql_join.asp

I think actually the first table is the comments and the second is the users. Also I do not fully understand the question

It could mean can you add another table containing users and comments or another table just containing users that have made comments or, can you add a user when they add a comment or as I suspect you want to be able to list comments by user.

If you are asking if you can link comments to users, personally, and I am sure some will disagree, I prefer to simply use the id column for this. I would obtain the id of the user and then select all matching comments with the same user id. I prefer to do this myself rather than using a join.

But your tables are a little confusing. For example your comments table contains the comment, an id an email and a post id. I would have an id, the comment and the user id. The comments can then be referenced to the user that posted it and you can look up the email address from the user table.

1 Like

i try that but did not work :frowning:

SELECT comments.user_id, users.username, comments.comment FROM comments INNER JOIN users ON comments.user_id=users.id where comments.user_id= $poat_id

Can you expand on that? What happened, did you get an error message? Can you show the rest of the code around that query? Where does $poat_id come from?

The user-related details should be in the users table (and only in there), and the comments-related details should be in the comments table. The user-id can easily be used to link the comments to the user, we just need a bit more detail on what exactly the problem was.

You also should read up on prepared statements instead of concatenating the user-id into your query like that.

I think you’re correct about that, perhaps the OP can confirm, or maybe just put the table layout in the post without me having to open a screen-shot.

i got no error the poat_id is where the person leaves a comment on the blog i was hoping to intergate
comments so i would not have to use disqus but i guess i will have to

the post is wrong and i cant edit it now but yes users details are in user table and comments in comments table
i know its easy to do it as i have the same with displaying the blog post but the comment part is hard

ok

Maybe if you showed your code, someone could have a look at where things are going wrong.

ok then
removed some comment table since last post its now
id | comment | post_id | user_id | created_at |

users table
id | username |

this is what is showing but the date and time is wrong and theres more data in database but not showing

comments script

<?php 
error_reporting(0);
$query     = "SELECT * FROM comments INNER JOIN users ON comments.user_id=users.id where comments.post_id= $post_id" ;
$result    = mysqli_query( $link, $query );
$num       = mysqli_num_rows( $result );

?>

 <?php

                            if( $num>0 ){
                                 $leaw = mysqli_fetch_assoc($result) ;
                             }
                             else{
                                echo "No Post Found";
                             }
                             ?> 
<?php 
if(empty($leaw['image'])){
   $avatar = 'http://www.gravatar.com/avatar/' . md5( $leaw['email'] ) . '?s=33&d=wavatar&r=g';
    echo '<img src="' . $avatar . '" />';
 }else{
    echo '    <img src="'. $leaw['image'] .' "  style="width:60%" alt="Circle Image" class="img-raised rounded-shape img-fluid"/> </img> </br> ';
    }
       ?>                  <p class="text-black text-center"> 

</br>
  <?php  
    if ($leaw['role'] === "admin") {
echo '<span class="badge badge-danger"> '. $leaw['username'] .'<a  href="' . $row['siteurl'] . 'pages/profile.php?id=' . $leaw['id'] . '/' . $leaw['username'] .'.php"> <i class="fas fa-user-shield"></i></a></span>
<h6 style="color:#f12100;font-weight: 1000;background-color: #160e19;padding: 4  1px;border-radius: 1px;text-transform: uppercase;background-image: none;text-shadow: 1px 1px 1px rgb(65, 25, 41);"> '. $leaw['role'] .' 
</h2>'; 

}elseif ($leaw ['role'] ===  "Mod") {
echo '
<span class="badge badge-dark" style="color:'. $leaw['css'] .');">'. $leaw['username'] .' <a  href="' . $row['siteurl'] . 'pages/profile.php?id=' . $leaw['id'] . '/' . $leaw['username'] .'.php"><i class="fas fa-gavel"></i></a></span><h6 style="color: #0d27de;font-weight: 1000;background-color: #14d0be;padding: 4  1px;border-radius: 1px;text-transform: uppercase;background-image: none;text-shadow: 1px 1px 1px rgb(65, 25, 41);">'. $leaw['role'] .'</h2>
';

}elseif ($leaw ['role'] ===  "user") {
echo '
<span class="badge badge-light" style="color:'. $leaw['css'] .');">'. $leaw['username'] .' </span> <a  href="' . $row['siteurl'] . 'pages/profile.php?id=' . $leaw['id'] . '/' . $leaw['username'] .'.php"><i class="fas fa-user"></a></i>
<h6 style="color:#b1b2bd;font-weight: 1000;background-color: #1e5204a6;padding: 5  1px;border-radius: 1px;text-transform: uppercase;background-image: none;text-shadow: 1px 1px 1px rgb(65, 25, 41);">'. $leaw['role'] .'</h2>';
 } 
?> 
   </p>
              </div>
              <div class="col-md-10">  
                  <p>
                      <span class="float-right"><?php echo $leaw["created_at"]; ?>
 </span>
                 </p>
                 <div class="clearfix"></div>
                  <p><?php echo $leaw["comment"]; ?></p>
              </div>
          </div>
      </div>
  </div>
</div>
   <div class="well">
<form action="viewnews.php?post_id=<?php echo $blog['post_id']; ?>" method="post">
 <input type="hidden" name="post_id" value="<?php echo $post_id; ?>" required>
  <input type="hidden" name="user_id" value="<?php echo $_SESSION['id'];?>" required>
   <input type="hidden" name="created_at" value="<?php echo $todaydate = date('Y-m-d h:i:s'); ?>" required>

 <div class="form-group">
<textarea class="form-control" rows="4" name="comment" required></textarea>
</div>
<button type="submit"  value="Add Comment" name="post_comment" class="btn btn-primary"><i class="fa fa-reply"></i> Submit</button>
 </form>
 </div>

You only retrieve the first comment, there’s only one call to mysqli_fetch_assoc(). If there could be more than one comment, you need to wrap that in a while() loop or similar.

You use $row in that code, where is that coming from? Is that all of the code? You use $post_id in the query, but I can’t see where that’s coming from. Where does $_blog['post_id'] come from in your form?

Sorry for messing around everyone but ive got it now :slight_smile:

thanks everyone for trying to help!