时间戳

首页 > 解决方案 > 编辑 MySQL 查询以从同一查询中的不同表中获取用户的喜欢，并显示是否喜欢

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）的实际用户是否喜欢该帖子，如果不喜欢则显示它。

标签： phpmysqlsqldatabase

解决方案

对表使用 LEFT JOINlove并检查该帖子是否真的被喜欢/喜欢的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。

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

推荐阅读