php - 编辑MySQL查询以在同一查询中从不同表中的用户获得喜欢，并显示是否喜欢

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

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>
              </a>
              <div class="post-extra">
                <!--<div class="add-favorite" id="heart-container"><span class="lovecount">112</span><input data-post="123" data-user="Jessica Jones" type="checkbox" id="toggle" onclick="myFunction(this)" checked><div id="twitter-heart" class=""></div></input></div>-->

                <div class="add-favorite" id="heart-container"><a href="#" title="Alle Loves anzeigen"><span class="lovecount"><?php echo $row['loves']; ?></span></a>

                <!--<input data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>" type="checkbox" id="toggle" class="toggle love" onclick="myFunction(this)">-->
                <input data-post="<?php echo $row['id']; ?>" data-userid="<?php echo $_SESSION['user_id'];?>" type="checkbox" id="toggle" class="toggle">

                  <div id="twitter-heart" class=""></div></input></div>
                <div class="post-share"><i class="fa fa-share-alt"></i>
                  <div class="social-links">
                    <a href="#" class="share-facebook social-links-a" data-network="Facebook" data-post="<?php echo $row['id'] ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-facebook"></i></a>
                    <a href="#" class="share-twitter social-links-a" data-network="Twitter" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-twitter"></i></a>
                    <a href="#" class="share-google-plus social-links-a" data-network="Pinterest" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-pinterest"></i></a>
                    <a href="#" class="share-whatsapp social-links-a" data-network="WhatsApp" data-post="<?php echo $row['id']; ?>" data-url="https://onepost.eu/neu/post.php"><i class="fa fa-whatsapp"></i></a>
                  </div>
                </div>
              </div>
            </div>
          </div>
          <!-- POST ITEM END -->

          <div class="form-mini-divider"></div>

          <?php } ?>






          <div class="form-divider"></div> -->
        <div class="show_more_main" id="show_more_main<?php echo $postID; ?>">

          <button id="<?php echo $postID; ?>" class="show_more button circle block green">Mehr OnePosts</button>
          <button class="postloading button circle block green" style="display: none;">Lade...</button>


        </div>

        <?php } ?>

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

最佳答案

使用LEFT JOIN到love表，并检查该帖子是否真的被IS NOT NULL条件所爱/喜欢：

<?php

// Get records from the database
    $stmt = $db->prepare("
        SELECT p.*, (l.postid IS NOT NULL) as is_liked
        FROM posts p
        LEFT JOIN love l
          ON  l.postid = p.id
          AND l.userid = ?
        ORDER BY p.id DESC
        LIMIT 10
    ");
    $stmt->bind_param('i', $currentUserId);
    $stmt->execute();
    $result = $stmt->get_result();

    if($result->num_rows > 0){
        while($row = $result->fetch_assoc()){
            $postID = $row['id'];
    ?>

    [...]

您可以使用$row['is_liked']访问将包含1或0的值。

您需要用保存当前使用ID的变量替换$currentUserId。可能是$_SESSION['userid']。