如何在用户表的 URL 和个人资料图片显示上显示消息对话用户名

因此，首先，您根本不需要在 pm 表中拥有用户名。因此，请从“pm”表中删除这些列。这是因为您可以使用用户表中的 id 与“from_id”和“to_id”链接。

PM表：

id  from_id     to_id    msg               sent_date
1   2           3        hi how are you?   2019-12-05 04:14:20
2   3           2        fine              2019-12-05 05:15:58
3   2           3        hi                2019-12-05 03:20:34
4   5           2        hi                2019-12-05 08:30:40

用户表：

id    username   profile_pic         
1     john       <link to profile picture>
2     master     <link to profile picture>          
3     john       <link to profile picture>          
4     previn     <link to profile picture>
4     robin      <link to profile picture>

所以我正在考虑在 SQL 中使用 JOIN。但是当必须同时选择to_id和from_id时，并没有真正让它工作。所以想出了一些非常奇怪的狗屎。我认为你必须以其他方式做到这一点，因为这真的很奇怪。但我终于让它起作用了。但这不是一个很好的解决方案，只是一个解决方案。

解决方案的代码：

    if (isset($_SESSION['userid'])) {
        $session_id = $_SESSION['userid'];
      }

        $sql = "SELECT *,
  (SELECT username FROM users WHERE userid=from_id) AS from_username,
  (SELECT username FROM users WHERE userid=to_id) AS to_username,
  (SELECT username FROM users WHERE userid=?) AS my_username,
  (SELECT profile_pic FROM users WHERE userid=from_id) AS from_profile_pic,
  (SELECT profile_pic FROM users WHERE userid=to_id) AS to_profile_pic,
  (SELECT profile_pic FROM users WHERE userid=?) AS my_profile_pic
  FROM pm WHERE from_id = ? OR to_id = ? ORDER BY id DESC";


  if ($stmt = $con->prepare($sql)) {
     $stmt->bind_param('iiii', $session_id, $session_id, $session_id, $session_id);
     $stmt->execute();
   }

   $tempArray = array();

   $result = $stmt->get_result();

   if ($result->num_rows > 0) {
       while ($row = $result->fetch_assoc()) {





         if (!in_array($row['to_id'].$row['from_id'], $tempArray)) {

            $friend_id = str_replace($session_id, null, $row['to_id'].$row['from_id']);

            $title = $row['from_username'].$row['to_username'];
            $num = strlen($title) ;

            $num = $num/2;

            $first_half = substr($title,0, $num);

            $second_half = substr($title, $num);


            if ($first_half == $second_half) {
              $friend_username = $row['from_username'];
            } else {
              $friend_username = str_replace($row['my_username'], null, $row['from_username'].$row['to_username']);
            }

            $friend_profile_pic = str_replace($row['my_profile_pic'], null, $row['from_profile_pic'].$row['to_profile_pic']);

            echo "<br>";
            echo $row['from_username'] . " - " . $row['to_username']." ". $row['msg']. " - " .$row['sent_time'];

         ?>

           <img src="images/<?php echo $friend_profile_pic;?>" height="20px" width="20px"/>
           <a href="cons.php?to_name=<?php echo $friend_username ?>&to_id=<?php echo $friend_id ?>">Reply</a>

         <?php

  }


         array_push($tempArray, $row['from_id'].$row['to_id']);
         array_push($tempArray, $row['to_id'].$row['from_id']);

       }
   } else {
       echo "NO MESSAGES";
   }