php - 尝试显示两个表中的数据

Question

我正在尝试根据另一个表中找到的 ID 从一个表中获取$_GET 数据。我的第一个表名为 user_thoughts，该表保存用户在我的“社交媒体网站”上发布的所有公开帖子的数据。我有另一个名为 users 的表，它存储该网站注册用户的所有详细信息。

我试图在网站主页上显示所有user_thoughts，但我很难为“想法”的作者显示正确的数据。

这个想法是获取user_thought的added_by(作者)，然后使用保存added_by值的变量并将其与在表 users 中找到的用户名。

这是我的表格及其字段:

用户想法:

id
message
date_of_msg_post
time_of_msg_post
attachment
added_by

用户(仅显示相关字段)

id
first_name
last_name
username
profile_pic

目标:从user_thoughts中获取帖子的added_by，然后使用added_by从表users中的用户名字段获取added_by的详细信息。注意:added_by 和 username 都将保留相同的值。

这是我尝试过的:

/* How it works: The id of each user_thought will be used to determine which user posted it. 
 * Then display their details accourdingly.
 */
$get_thoughts_from_db = mysqli_query($connect, "SELECT * FROM user_thoughts ORDER BY id DESC"); // newest posts first

while ($row = mysqli_fetch_assoc($get_thoughts_from_db)) {
    $thought_id      = $row['id'];
    $msg_content     = $row['message'];
    $date_of_msg     = $row['date_of_msg_post'];
    $thoughts_by     = $row['added_by'];
    $time_of_msg     = $row['time_of_msg_post'];
    $attachent       = $row['attachment'];
    $get_user        = $_GET['id'];
} // while closed   

    // Get the details of the user based on the ID of the user thought.
    $get_data = mysqli_query($connect, "SELECT * FROM users WHERE id = '$get_user'");                                   
    $get_user_data = mysqli_fetch_assoc($get_data);
        $author_fname = $get_user_data['first_name'];
        $user_profile_dp = $get_user_data['profile_pic'];

// displaying all the posts in the database on the main page.
// Will limit 15 posts per page, and will order them by the date and time posted (latest posts first)
$get_all_posts_q = mysqli_query ($connect, "SELECT * FROM user_thoughts ORDER BY id DESC ");
    $check_rows = mysqli_num_rows($get_all_posts_q);
    while ($get_row = mysqli_fetch_array($get_all_posts_q)){
        $message         = $get_row['message']; 

        /**** Between the while loop is where I echo the div(s)
                     which display the above details. *******/

}

当前行为:目前，通过上面的代码，它显示了所有user_thoughts，即表中有三行，并且每一行都在显示，但是，其中的详细信息并非基于作者这些帖子。例如，撰写作者的用户的个人资料图片未显示。

Answer 1

这链接了用户和 user_thoughts。请记住清理您的输入。

$get_thoughts_from_db = mysqli_query($connect, "SELECT * FROM user_thoughts INNER JOIN users ON user_thoughts.added_by = users.username ON ORDER BY id DESC"); // newest posts first