编辑 MySQL 查询以从同一查询中的不同表中获取用户的喜欢并显示是否喜欢

Question

大家好，我有两张桌子，第一个桌子叫做“帖子”，看起来像这样

id    picture    title    description    poster    ip    posterid    .....

123   img-2.jpg  Title 1  Desc 1         Poster    xx    1

第二个表叫“爱”，长这个样子

id    ip    userid    postid    created

1     xx    1         123       date   

这是我的 MySQL 查询的实际外观：

<?php

// Get records from the database

    $query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT 10");

    if($query->num_rows > 0){ 

        while($row = $query->fetch_assoc()){ 

            $postID = $row['id'];

    ?>

          <!-- POST ITEM START -->

          <div class="post-item">

            <div class="post-asset image">

              <img src="uploads/<?php echo $row['picture']; ?>">

            </div>

            <div class="post-header">

              <h3 class="post-title"><a href="#" data-loader="show"><?php echo $row['title']; ?></a></h3>

              <span class="post-category">

                <a class="favorite-button" href="#" data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>"><span class="favorite-button-icon fa fa-star-o"></span></a>

              </span>

              <span class="post-date font17"><i class="fa fa-clock-o"></i> <?php $timeago=get_timeago(strtotime($row['created'])); echo $timeago;?></span>

              <span class="post-comments font17"><i class="fa fa-comments-o"></i> 1,3k Reaktionen</span>

            </div>

            <div class="post-footer">

              <a href="#" class="post-author">

                <span class="author-img"><img src="img/avatar.png"></span>

                <span class="author-name">OnePost von<b><?php echo $row['poster']; ?></b></span>

我现在想要做的是了解具有其会话 ID（也是用户 ID）的实际用户是否喜欢该帖子，并在是否喜欢该帖子时显示它。

Answer 1

使用 LEFT JOIN 到love表格并检查帖子是否真的被喜欢/喜欢的IS NOT NULL条件：<?php// Get records from the database&nbsp; &nbsp; $stmt = $db->prepare("&nbsp; &nbsp; &nbsp; &nbsp; SELECT p.*, (l.postid IS NOT NULL) as is_liked&nbsp; &nbsp; &nbsp; &nbsp; FROM posts p&nbsp; &nbsp; &nbsp; &nbsp; LEFT JOIN love l&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON&nbsp; l.postid = p.id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND l.userid = ?&nbsp; &nbsp; &nbsp; &nbsp; ORDER BY p.id DESC&nbsp; &nbsp; &nbsp; &nbsp; LIMIT 10&nbsp; &nbsp; ");&nbsp; &nbsp; $stmt->bind_param('i', $currentUserId);&nbsp; &nbsp; $stmt->execute();&nbsp; &nbsp; $result = $stmt->get_result();&nbsp; &nbsp; if($result->num_rows > 0){&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; while($row = $result->fetch_assoc()){&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $postID = $row['id'];&nbsp; &nbsp; ?>&nbsp; &nbsp; [...]您可以访问$row['is_liked']将包含1或的值0。您需要替换$currentUserId为包含当前使用 ID 的变量。那可能是$_SESSION['userid']。