Reputation: 395
I have two table called t_user and t_chat. I am trying to display message from t_chat in PHP table like below.
$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++;
}
?>
I want display sender and receiver name which is located in table called t_user with username column
. I am new in PHP and confused how can I achieve it. Let me know if someone can help me for achieve my task. Thanks a lot!
Note : t_chat table have userid witch column name called sender and receiver, currently I am displaying that userid in above table, instead I want display username. Thanks
Upvotes: 0
Views: 39
Reputation: 1181
The joins might look something like this:
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
In this example I am joining the tables twice (as user1 and user2) so that the t_user
table gets referenced independently for each lookup.
I also gave each column a name using AS
to make them easier to reference later in your code.
Upvotes: 1
Reputation: 4247
Try this sql (The t_user table must have an id
column that matches the userid
from t_chat):
$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";
More details and examples about MySQL JOIN you can find in the tutorial from:
https://coursesweb.net/php-mysql/mysql-inner-left-join-right-join_t
Upvotes: 0