javascript - 使用左连接在 while 循环中获取数据

Question

我正在使用 while 循环从 mysql 表中获取用户评论，并希望在评论 div 中给出的按钮上放置一些条件类。

每个评论都有两个按钮:

1. 竖起大拇指按钮
2. 拇指向下按钮

如果用户已经点赞了任何评论，我想给一个类名称active，这样万一将来他点击同一个评论的点赞，就会反转数字类似。

我正在讨论这两个表

评论

+----+--------+---------------+---------+
| id | userid |   usercom     | comname |
+----+--------+---------------+---------+
| 35 |      5 | check comment |      12 |
| 36 |      6 | comment test  |      12 |
| 37 |      6 | third comment |      12 |
| 38 |      5 | number four   |      12 |
| 39 |      7 | fifth         |      13 |
| 40 |      4 | 6th           |      13 |
| 41 |     18 | seven         |      13 |
+----+--------+---------------+---------+

喜欢或不喜欢

+----+-------+------+-------+
| id | vtype | uid  | comid |
+----+-------+------+-------+
|  1 |     0 |    5 |    35 |
|  2 |     1 |    6 |    35 |
|  3 |     1 |    7 |    35 |
|  4 |     0 |    8 |    36 |
|  5 |     1 |    5 |    36 |
|  6 |     1 |    9 |    35 |
|  7 |     1 |   10 |    36 |
|  8 |     1 |   11 |    36 |
|  9 |     1 |   20 |    35 |
| 10 |     0 |    9 |    35 |
| 11 |     1 |   21 |    37 |
+----+-------+------+-------+

我正在从这些表中获取数据并在此处进行回显...

.vbtn {
  display: inline-block;
  margin-bottom: 0;
  font-size: 13px;
  font-weight: 300;
  white-space: nowrap;
  vertical-align: middle;
  -ms-touch-action: manipulation;
  touch-action: manipulation;
  cursor: pointer;
  -webkit-user-select: none;
  -moz-user-select: none;
  -ms-user-select: none;
  user-select: none;
  background: none!important;
  color: inherit;
  color: #06C;
  border: none;
  padding: 0!important;
  font: inherit;
}

.like.active span {
  display: none;
}

.like.active:before {
  font-family: FontAwesome;
  content: "\f164";
  color: #009900;
}

.dislike.active span {
  display: none;
}

.dislike.active:before {
  font-family: FontAwesome;
  content: "\f165";
  color: #FF0000;
}

<?php
// ...
$sid = "18"; //session id
$c_name = "12"; //article-post id
$sel = "SELECT * FROM `likesordislikes` where `uid` = :sid AND `comid` = :comment";
if (isset($_POST['likes'])) {
  $comid = mysqli_real_escape_string($con, $_POST['comid']);
  try {
    $stmt = $DB->prepare($sel);
    $stmt->bindValue(":sid", $sid);
    $stmt->bindValue(":comment", $comid);
    $stmt->execute();
    $fetch = $stmt->fetchAll();
    if (count($fetch) == 0) {
      $insert = "INSERT INTO `likesordislikes` (`vtype` , `uid` , `comid`) VALUES " . "( :like, :likerid , :comment)";
      $stmt = $DB->prepare($insert);
      $stmt->bindValue(":like", "1");
      $stmt->bindValue(":likerid", $sid);
      $stmt->bindValue(":comment", $comid);
      $stmt->execute();
    } elseif (count($fetch) > 0)  {
      if ($fetch[0]["vtype"] == "1") {
        $delete = "DELETE FROM `likesordislikes` where `uid` = :sid AND `comid` = :commnt";
        $stmt = $DB->prepare($delete);
        $stmt->bindValue(":sid", $sid);
        $stmt->bindValue(":commnt", $comid);
        $stmt->execute();
      } elseif ($fetch[0]["vtype"] == "0") {
        $sql = "UPDATE `likesordislikes` SET  `vtype` =  '1' WHERE `uid` = :id AND `comid` = :commn";
        $stmt = $DB->prepare($sql);
        $stmt->bindValue(":id", $sid);
        $stmt->bindValue(":commn", $comid);
        $stmt->execute();
      }
    }
  }
  catch (Exception $ex) {
    echo $ex->getMessage();
  }
}
if (isset($_POST['dislikes'])) {
  $comid = mysqli_real_escape_string($con, $_POST['comid']);
  try {
    $stmt = $DB->prepare($sel);
    $stmt->bindValue(":sid", $sid);
    $stmt->bindValue(":comment", $comid);
    $stmt->execute();
    $fetch = $stmt->fetchAll();
    if (count($fetch) == 0) {
      $insert = "INSERT INTO `likesordislikes` (`vtype` , `uid` , `comid`) VALUES " . "( :unlke, :likerid , :comment);";
      $stmt = $DB->prepare($insert);
      $stmt->bindValue(":unlke", "0");
      $stmt->bindValue(":likerid", $sid);
      $stmt->bindValue(":comment", $comid);
      $stmt->execute();
    } else {
      if ($fetch[0]["vtype"] == "0") {
        $delete = "DELETE FROM `likesordislikes` where uid = :sid AND comid = :commnt";
        $stmt = $DB->prepare($delete);
        $stmt->bindValue(":sid", $sid);
        $stmt->bindValue(":commnt", $comid);
        $stmt->execute();
      } elseif ($fetch[0]["vtype"] == "1") {
        $sql = "UPDATE `likesordislikes` SET `vtype` = '0' WHERE `uid` = :id AND comid = :commn";
        $stmt = $DB->prepare($sql);
        $stmt->bindValue(":id", $sid);
        $stmt->bindValue(":commn", $comid);
        $stmt->execute();
      }
    }
  }
  catch (Exception $ex) {
    echo $ex->getMessage();
  }
}

$slt = "SELECT" .
" c.*," .
" SUM(CASE WHEN lod.vtype=1 THEN 1 ELSE 0 END) likes," .
" SUM(CASE WHEN lod.vtype=0 THEN 1 ELSE 0 END) dislikes" .
" FROM" .
" comments c LEFT JOIN likesordislikes lod ON lod.comid=c.id" .
" WHERE" .
" c.comname = '$c_name' AND c.media = '$type'" .
" GROUP BY" .
" c.id";
$res = mysqli_query($con, $slt);

while($fetc = mysqli_fetch_array($res)) {
  //$select3 = "SELECT c.* FROM comments c LEFT JOIN likesordislikes lod ON c.id=lod.comid WHERE `uid` = :sid"; (It was my question code)
  $select3 = "SELECT vtype FROM likesordislikes WHERE comid = :commentid AND `uid` = :sid LIMIT 1"; // its the solution i got in answer
  try {
    $stmtt = $DB->prepare($select3);
    $stmtt->bindValue(":commentid", $fetc['id']); //update from the answer
    $stmtt->bindValue(":sid", $sid);
    $stmtt->execute();
    $vfetch = $stmtt->fetchAll();
    if (count($vfetch) == 0) {
      $likeclass = "";
      $dislikeclass = "";
    } 
    elseif (count($vfetch) > 0) {
      if ($vfetch[0]["vtype"] == "1") {
        $likeclass = "active";
        $dislikeclass = "";
      } 
      elseif ($vfetch[0]["vtype"] == "0") {
        $likeclass = "";
        $dislikeclass = "active";
      }
    }
  }
  catch (Exception $ex) {
    echo $ex->getMessage();
  }
  ?>
  <div class="container1">
    <div class="div-right-mid">
      <span class="comment-text">
        <?php echo $fetc['usercom'] ?>  
      </span>
    </div>
    <div id="rating-votes">
      <button class="vbtn like <?php echo $likeclass; ?>" value="<?php echo $comid ?>">
        <span>
          <i class="fa fa-thumbs-o-up" aria-hidden="true">
          </i>
        </span>
      </button>
      <span class="likes">
        <font color="#6d7371">
          <?php echo $fetc['likes']; ?>
        </font>
      </span>&nbsp;
      <button class="vbtn dislike <?php echo $dislikeclass; ?>" value="<?php echo $comid ?>">
        <span>
          <i class="fa fa-thumbs-o-down" aria-hidden="true">
          </i>
        </span>
      </button>
      <span class="dislikes">
        <font color="#6d7371">
          <?php echo $fetc['dislikes']; ?>
        </font>
      </span>
    </div>
  </div>
  <?php
}
?>

更新js

<script type="text/javascript">
$('.rating-votes').likeDislike({
  reverseMode: true,
  click: function (value, l, d, event) {
    var likes = $(this.element).find('.likes');
    var dislikes = $(this.element).find('.dislikes');
    var comid = $(".vbtn").val();
    var data = {comid:comid};
    if(value === 1){
       data.likes = true;
       }else{
       data.dislikes = true;
       }
    $.ajax({
      url: '',
      type: 'POST',   
      data: data,
      success: function (data) {
        likes.text(parseInt(likes.text()) + l);
        dislikes.text(parseInt(dislikes.text()) + d);
      }
    });
  }
});
</script>
<script src="like-dislike.js"></script>

这段 php 代码没有给我所需的结果。

我面临三个问题...

1. 当用户点赞评论时，点赞按钮未显示事件类(已解决)

2. 只有第一条评论的拇指向上/拇指向下在点击时起作用，第二条评论的按钮在点击时不起作用(已解决)

   <

3. Ajax 调用未将数据发送到 php (已编辑)

CSS 显示对于登录用户的任何特定评论，拇指向上/拇指向下按钮的事件类会发生什么情况

Answer 1

关于你的第一个问题

有几个问题:

1. $select3查询没有从 likesordislikes 中引入列表(根据您的代码，您需要 vtype 列)，它只带来 c.* (这是 comments 表中的所有字段)。这可能就是为什么你必须选择 try...catch ，因为它将无法找到 vtype指数。因此该行代码应更改为:

   $select3 = "SELECT c.*,lod.vtype FROM comments c LEFT JOIN likesordislikes lod ON c.id=lod.comid WHERE `uid` = :sid";
   

2. $select3查询总是获取所有 comments记录，即使用户没有与这些评论交互(因为您使用的是 LEFT JOIN )，然后每次都选择第一个(索引 0 )。因为您似乎并没有真正使用表 comments 中的信息(不需要 JOIN )，理论上每个用户和评论应该有一条记录，我会将该行更改为:

   $select3 = "SELECT vtype FROM likesordislikes WHERE comid = :commentid AND `uid` = :sid LIMIT 1";
   

   然后当然是绑定(bind)评论id:

   $stmtt->bindValue(":commentid", $fetc['id']);
   

关于你的第二个问题

您正在打印 <div id="rating-votes">多次，这在 HTML 方面是无效的。 id属性在整个页面中应该是唯一的，如果有两个，则调用类似 $('#id')总会给你一个元素，第一个。所以你应该更改 while 内的 HTML 代码使用class而不是id ，像这样:

<!-- -->
<div class="rating-votes">
  <button class="vbtn like <?php echo $likeclass; ?>" value="<?php echo $comid ?>" class="vote">
    <!-- -->
  </button>
  <!-- -->
  <button class="vbtn dislike <?php echo $dislikeclass; ?>" value="<?php echo $comid ?>" class="vote">
    <!-- -->
  </button>
  <!-- -->
</div>
<!-- -->

然后，您应该更改您的 Javascript 调用，以便它们通过 class 找到而不是id ，像这样:

$('.rating-votes').likeDislike({    //.likeDislike function defined in like-dislike.js
  // ...
  click: function(value, l, d, event) {
    // ...
    var comid = $(this.element).find('.vote').val();
    // ...
  }
});

关于你的第三个问题

数据未按您的预期到达，可能是因为您缺少 & 符号 ( & )，您拥有的代码:

data: 'likes=' + likes + '&dislikes=' + dislikes + 'comid=' + comid,

会翻译成这样:

data: 'likes=1&dislikes=2comid=3,

所以你应该将其更改为:

data: 'likes=' + likes + '&dislikes=' + dislikes + '&comid=' + comid,

尽管我建议您使用对象而不是自己连接值，这样 jQuery 就会为您处理所有奇怪的事情。为此，请将该行更改为:

data:{
  likes:likes,
  dislikes:dislikes,
  comid:comid
}

• 更新

您遇到的另一个问题是您发送的是对象，而不是对象的值，因此，而不是:

data:{
  likes:likes,
  dislikes:dislikes,
  comid:comid
}

你应该有:

data:{
  likes:likes.val(),
  dislikes:dislikes.val(),
  comid:comid
}

• 更新

前一个并不是真正的问题，我的错。您甚至不应该发送这些值，因为您仅将它们用作 PHP 中的标志，因此您应该使用类似以下内容的内容:

data:{
  likes:true,
  comid:comid
}

另一个问题是您的 PHP 代码同时接收 likes和dislikes (就像用户同时点击了两者一样)。因为这是不可能的，所以我将 PHP 从 if 更改为然后if , 至if然后else if ，像这样:

if (isset($_POST['likes'])) {
  // ...
}else if (isset($_POST['dislikes'])) {
  // ...
}

请记住，您仍然发送两个信号，因此这样您将始终执行第一个 if 。因此，您应该更改 Javascript，使其仅发送两个信号之一。我真的不知道怎么办likeDislike有效，但您的代码应该类似于:

// ...
var data = {
  comid:comid
};
if(theUserLikesIt){
  data.likes = true;
}else{
  data.dislikes = true;
}
$.ajax({
  // ...
  data: data,
  // ...
});