php - 跨表查询

Question

我正在运行一个查询来检索用户帖子。表“帖子”有一列包含提交帖子的用户的姓名，另一个名为“用户”的表也有用户名和头像列表。

我需要在表 Users 上的每篇文章中查找“User”，以及该表中是否存在匹配的回显列“Avatar”。

$result = mysql_query("SELECT * FROM Posts WHERE MATCH (City) AGAINST ('$city2') ORDER by `Comments` DESC LIMIT $limit_posts OFFSET $first_post");

while($row = mysql_fetch_array( $result )) { ?>
<div class='item'>
    <a href="?city=<?php echo $row['City']; ?>&post=<?php echo $row['PID']; ?>"><?php echo $row['Text']; ?></a> 
<? } 

Answer 1

$result = mysql_query("
SELECT
  Posts.*,
  Users.Avatar
FROM
  Posts
INNER JOIN
  Users
ON
  Users.ID = Posts.User
WHERE
  MATCH (Posts.City) AGAINST ('$city2') 
ORDER BY
  Posts.`Comments` DESC 
LIMIT 
  $limit_posts 
OFFSET 
  $first_post
");

while($row = mysql_fetch_array( $result )) { ?>
<div class='item'>
    <a href="?city=<?php echo $row['City']; ?>&post=<?php echo $row['PID']; ?>"><?php echo $row['Text']; ?></a> 

<?php echo $row['Avatar']; ?>
<? }