使用左联接在while循环中获取数据 [英] Fetching data in while loop by using left join
问题描述
我正在使用while循环从mysql表中获取用户注释，并希望在注释div中给定的按钮上放置一些条件类.
I am fetching user comments from mysql table using while loop and want to put some condition class on buttons given in comment div.
每个评论都有两个按钮:
Each comment has two buttons:
- 缩略图按钮
- 缩略图"按钮
我想给一个班级名称 active ，如果用户已经竖起了任何评论，以防万一将来如果他单击同一评论的竖起大拇指，它将颠倒该数字之类的.
I want to give a class name active if a user has already thumbed-up any comment so in case if in future, he clicks on the same comment's thumb-up, it will reverse the number of like.
我正在讨论这两个表
评论
+----+--------+---------------+---------+
| 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 |
+----+-------+------+-------+
我正在从这些表中获取数据并在此回显...
I am getting data from these tables and echoing that here...
.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>
<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代码没有给我所需的结果.
This php code is not giving me the required result.
我正面临三个问题...
I am facing Three issues...
-
当用户竖起评论时，该竖起按钮未显示活动班级的评论(已解决)
只有第一个评论的拇指向上/向下按下可用于单击，第二个第三个评论和所有其余评论的按钮均不可单击(已解决)
Only the 1st comment's thumb-up/thumb-down is working on click, 2nd 3rd and all remaining comment's buttons are not working on click (Solved)
Ajax调用未将数据发送到php (已编辑)
Ajax call is not sending data to php (Edited)
CSS通过登录用户显示活动类的拇指向上/向下按钮对任何特定注释的作用
The CSS shows what happens to active class of thumbed up / thumbed down button for any specific comment by logged in user
推荐答案
第一个问题
有两个问题:
-
$select3查询不会从likesordislikes表中获取列(根据您的代码，您需要vtype列)，它仅会带来c.*(这是来自comments表).这可能就是为什么必须使用try...catch的原因，因为它无法找到vtype索引.因此该行代码应更改为:
The
$select3query is not bringing columns from thelikesordislikestable (according to your code, you need thevtypecolumn), it's only bringingc.*(which is all the fields from thecommentstable). Which is probably why you had to go with thetry...catch, because it won't be able to find thevtypeindex. So that line of code should change to:
$select3 = "SELECT c.*,lod.vtype FROM comments c LEFT JOIN likesordislikes lod ON c.id=lod.comid WHERE `uid` = :sid";
$select3查询始终会获取所有comments记录，即使用户未与这些注释进行交互(因为您使用的是LEFT JOIN)，然后选择第一个(索引0)每次.由于您似乎并没有真正使用表comments中的信息(不需要JOIN)，并且从理论上讲，每个用户和注释都应该有一条记录，因此我将该行更改为:
The $select3 query is always getting all the comments records, even if the user didn't interact with those comments (because you're using a LEFT JOIN), and then picking the first one (index 0) every time. Since you don't really seem to use information from the table comments (no need for the JOIN), and theoretically there should be a single record per user and comment, I'd change that line to:
$select3 = "SELECT vtype FROM likesordislikes WHERE comid = :commentid AND `uid` = :sid LIMIT 1";
然后，当然，绑定评论ID:
And then, of course, bind the comment id:
$stmtt->bindValue(":commentid", $fetc['id']);
第二个问题
您要多次打印<div id="rating-votes">，这是无效的HTML方式. id属性在整个页面中应该是唯一的，如果有两个，则$('#id')之类的调用将始终为您提供一个元素，第一个元素.因此，应将while中的HTML代码更改为使用class而不是id，例如:
You're printing <div id="rating-votes"> multiple times, which is invalid HTML-wise. The id attribute should be unique in the whole page, if you have two, calls like $('#id') will always give you a single element, the first one. So you should change the HTML code inside the while to use class instead of id, like this:
<!-- -->
<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查找，就像这样:
Then, you should change your Javascript calls so they find by class instead of id, like this:
$('.rating-votes').likeDislike({ //.likeDislike function defined in like-dislike.js
// ...
click: function(value, l, d, event) {
// ...
var comid = $(this.element).find('.vote').val();
// ...
}
});
第三个问题
数据未如预期那样到达，可能是因为您缺少与号(&)和所具有的代码:
The data is not arriving as you expect, probably because you're missing an ampersand (&), the code you have:
data: 'likes=' + likes + '&dislikes=' + dislikes + 'comid=' + comid,
将会翻译为:
data: 'likes=1&dislikes=2comid=3,
所以您应该将其更改为:
So you should change it to:
data: 'likes=' + likes + '&dislikes=' + dislikes + '&comid=' + comid,
尽管我建议您使用一个对象而不是自己串联这些值，但是jQuery会为您处理所有怪异的东西.为此，请将该行更改为:
Although I would recommend you use an object instead of concatenating the values yourself, that way jQuery will handle all the weird stuff for you. In order to do so, change that line to:
data:{
likes:likes,
dislikes:dislikes,
comid:comid
}
- 更新
- Update
您遇到的另一个问题是，您正在发送对象，而不是对象的值，因此，发送的不是:
data:{
likes:likes,
dislikes:dislikes,
comid:comid
}
您应该具有:
data:{
likes:likes.val(),
dislikes:dislikes.val(),
comid:comid
}
- 更新
- Update
上一个不是一个真正的问题，我很糟糕.您甚至不应该发送值，因为您只是在PHP中将它们用作标志，所以应该使用类似以下内容的方法:
The previous one wasn't really a problem, my bad. You shouldn't even send the values, since you're only using them as flags in PHP, so instead you should use something like:
data:{
likes:true,
comid:comid
}
另一个问题是您的PHP代码同时接收了likes和dislikes(就像用户同时点击了两者).由于这不可能，所以我将PHP从if然后if更改为if然后else if，如下所示:
Another problem is your PHP code is receiving both likes and dislikes (like the user hit both). Since that shouldn't be possible, I'd change the PHP from if then if, to if then else if, like this:
if (isset($_POST['likes'])) {
// ...
}else if (isset($_POST['dislikes'])) {
// ...
}
请记住，您仍在发送两个信号，因此，您将始终执行第一个if.因此，您应该更改Javascript，使其仅发送两个信号之一.我真的不知道likeDislike是如何工作的，但是您的代码应类似于:
Keep in mind, you're still sending both signals, so this way you'll always execute the first if. So you should change your Javascript so it only sends one of the two signals. I don't really know how likeDislike works, but your code should look something like:
// ...
var data = {
comid:comid
};
if(theUserLikesIt){
data.likes = true;
}else{
data.dislikes = true;
}
$.ajax({
// ...
data: data,
// ...
});
这篇关于使用左联接在while循环中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
