Multiple queries

[crashmaster]

Hi there, have 1 question:

 

I have a table "users" with 2 columns (for.ex)

user_name - user_residence

 

Also I have a "comments" table.

 

Example:

 

I have a page, where are 20 comments by different users.

Is it possible to get by 1 query all users information?

 

Now my script works like this

 

$q = mysql_query ("SELECT comment_id, comment_author, comment_text FROM comments LIMIT 20");
while ($z = mysql_fetch_array($q)) {

$user_residence = mysql_result(mysql_query("SELECT user_residence FROM users WHERE user_name = '".$z[comment_author]."'"),0);

....etc....

}

 

To show 20 comments I am using 20 queries - to get residence of each user (comment_author).

 

Is there more simple way, how to get residence of all users, that posted comments by a single query?

 

Thank you

 

[dreamwest]

Does the comments table have the user name/id as well

 

Table "comments" with columns:

 

comments | userid

 

Use a join to ....join the tables

 

// Construct our join query
$query = "SELECT user.*, comments.* ".
"FROM user, comments ".
"WHERE user.userid = comments.userid";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
echo $row['user']. " - ". $row['comment'];
echo "<br />";
}

 

If not you cant associate the comment with the user. The only solution would be to add the comments within users table, or add the user id to the comments table...then join