Two queries to get information

I have two data tables. One contains all of users identifiable informaiton (username, id, etc) and the rest are content generated by them. I am doing a comments page. So when they submit a comment, their user_id goes in with the comment. Now o开发者_如何转开发n the comments page I am doing a query that will show all of the comments but what i have to do is use the user_id from the comments to pull the name from the user_database. I am using the query below with no success. How can i tweak it?

Code:

   $query="SELECT * FROM comments where post_id = '$postid'";  
   $result=mysql_query($query);

   $num=mysql_numrows($result);

   mysql_close();

   echo "";

   $i=0;
   while ($i < $num) {

   $comment=mysql_result($result,$i,"comment");
   $user_id=mysql_result($result,$i,"user_id");
   $other=mysql_result($result,$i,"other");


    echo "<br>$comment, $user_id, $other";

    echo "";

    $i++;
    }

     if(mysql_num_rows($result) < 1) {
      echo "<div id=noresultfound>No results for $comment</div>";
       }

To get information from two tables in a single query, you can use a join:

SELECT
    c.userid,
    c.comment,
    u.username,
    ...etc...
FROM comments AS c
JOIN user_database AS u
ON c.user_id = u.user_id
WHERE post_id = '42'

To pull data from two tables in the same query you can use a "SQL JOIN".

For example:

select c.*, u.* from comments c join users u on u.id = c.user_id

This query will pull all columns from both tables where each has a user in common.

Read more here.

I would start with changing your code somewhat to be something along the lines of the following

$query="SELECT * FROM comments where post_id = '$postid'";  
$result=mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
    echo "<br>".$row['comment'].", ".$row['user_id'].", ".$row['other'];
}

Then look at modifying your SQL to join with the user table

SELECT * FROM comments LEFT JOIN users ON comments.user_id = users.user_id WHERE post_id = '$postid'

And then you can get the name of the person with

$row['name'];