我有两张桌子,分别叫t_user和t_chat。我正在尝试显示来自t_chat的消息在PHP表如下。
$quotes_qry="SELECT * FROM t_chat ORDER BY id DESC LIMIT $start, $limit";
$result=mysqli_query($mysqli,$quotes_qry);
<?php
$i=0;
while($row=mysqli_fetch_array($result))
{
?>
<tr>
<td><?php echo ++$sr+$start;?></td>
<td><?php echo $row['sender'];?></td>
<td><?php echo nl2br($row['receiver']);?></td>
<td><?php echo nl2br($row['message']);?></td>
<td><?php echo time_elapsed_string($row['time']);?></td>
<td><a href="?id=<?php echo $row['id'];?>" class="" onclick="return confirm('Are you sure you want to delete this row?');"><img src="images/delete-icon.png"></a></td>
</tr>
<?php
$i++;
}
?> 我想要显示发送者和接收者的名字,它位于称为t_user with username column的表中。我是PHP新手,对如何实现它感到困惑。如果有人可以帮助我完成我的任务,请告诉我。非常感谢!
注意: t_chat表有名为sender和receiver的用户I,目前我在上面的表中显示该用户I,而不是显示用户名。谢谢
发布于 2019-02-03 02:38:25
joins可能如下所示:
SELECT t_chat.sender AS 'sender', t_chat.receiver AS 'receiver', t_chat.message AS 'message', t_chat.time AS 'time', t_chat.id AS 'id', user1.username AS 'senderusername', user2.username AS 'receiverusername'
FROM t_chat
LEFT JOIN t_user AS user1 ON t_chat.sender = user1.id
LEFT JOIN t_user AS user2 ON t_chat.receiver = user2.id
ORDER BY id DESC在本例中,我连接了两个表(作为user1和user2),以便在每次查找时独立地引用t_user表。
我还使用AS为每个列命名,以便稍后在代码中更容易引用它们。
发布于 2019-02-03 03:24:48
试试这个sql ( t_user表必须有一个与t_chat中的userid匹配的id列):
$quotes_qry="SELECT t1.sender, t1.receiver, t1.message, t1.time, t2.username FROM t_chat AS t1 INNER JOIN t_user AS t2 ON t1.userid=t2.id ORDER BY t1.id DESC LIMIT $start, $limit";有关MySQL JOIN的更多详细信息和示例,您可以在以下教程中找到:
https://coursesweb.net/php-mysql/mysql-inner-left-join-right-join_t
https://stackoverflow.com/questions/54495902
复制相似问题