我的代码可以正常插入或从数据库中删除。刷新页面后,它将正确显示。但是,如何才能在单击时进行切换?我已经在线搜索并尝试了解决方案,但是没有一个有效。
这是我的代码:
// PHP CODE TO SEE IF USER ALREADY LIKES VIDEO <?php $query272 = "SELECT * FROM video_likes WHERE video_id = :video_id272 AND user_id = :user_id272"; $stmt272 = $pdo->prepare($query272); $stmt272->bindValue(':video_id272',$id123); $stmt272->bindValue(':user_id272',$userID); $stmt272->execute(); $count272 = $stmt272->rowCount(); if($count272 > 0) { $you_like_this = 1; } else { $you_dont_like_this = 1; } if($you_dont_like_this == 1) { ?>
Ajax代码
<script> $(function() { $('#insertLike').click(function () { var videoID271 = $('#id').val(); var userID271 = $('#userID').val(); console.log('starting ajax'); $.ajax({ url: "./insert-like.php", type: "post", data: { id: videoID271, userID: userID271 }, success: function (data) { var dataParsed = JSON.parse(data); console.log(dataParsed); } }); }); }); </script>
像按钮形式:
<div style="float:left;margin-right:12px" id="like"> <input type="hidden" name="id" id="id" value="<?php echo $id123; ?>" /> <input type="hidden" name="userID" id="userID" value="<?php echo $userID; ?>" /> <input type="hidden" name="you_like_this" id="you_like_this" value="1" /> <button onclick="insertLike(this)" class="insertLike" id="insertLike" style="background:none;border:none;text-decoration:none; color:#DD4400;font-weight:bold">Like</button> </div> <?php } else { ?>
不喜欢Button Ajax:
<script> $(function () { $('#deleteLike').click(function () { var unlikeVideoID272 = $('#unlikeVideoID').val(); var unlikeUserID272 = $('#unlikeUserID').val(); console.log('starting ajax'); $.ajax({ url: "./delete-like.php", type: "post", data: { unlikeVideoID: unlikeVideoID272, unlikeUserID: unlikeUserID272 }, success: function (data) { var dataParsed = JSON.parse(data); console.log(dataParsed); } }); }); }); </script>
不喜欢表格:
<div style="float:left;margin-right:12px" id="unlike"> <input type="hidden" name="unlikeVideoID" id="unlikeVideoID" value="<?php echo $id123; ?>" /> <input type="hidden" name="unlikeUserID" id="unlikeUserID" value="<?php echo $userID; ?>" /> <button onclick="deleteLike(this)" class="deleteLike" id="deleteLike" style="background:none; border:none; text-decoration:none; color:#DD4400; font-weight:bold">Unlike</button> </div>
PHP关闭:
<?php } ?>
编辑(按要求):
Table users Table likes Table videos user_id <------- user_id -------> user_id likes video_id ------> id
不使用不喜欢。相反,使用“喜欢”和“与众不同”(当您已经喜欢它时,您可以改变主意并与之不同)。不像将简单地从表中删除一样。
再一次,插入数据库工作正常。在Ajax Success 200之后,我只需要将like之类的单词更改为不一样的单词。脚本也是如此。当“喜欢”变成“不一样”时,单击“不喜欢”应再次喜欢该视频。仿佛某人是躁郁症,并且不断改变自己的情绪/观点。想想Facebook的“赞”按钮。您单击它。它喜欢一个帖子。您再次单击它,它与帖子不同。无需刷新页面。
从您的选择查询中,我收集到这里涉及3个表:
Video Video_Likes User video_id --> video_id user_id <--- user_id
select count(user_id) from video_likes where video_id=?
select * from video_likes where user_id=? and video_id=?
但是,如果您确实不喜欢它,那将不起作用。在这种情况下,我们需要向中添加另一个字段video_likes:
video_likes
Video Video_Likes User video_id --> video_id user_id <--- user_id affinity
其中affinity =’赞’| “不喜欢”(我更喜欢使用人类可读的值,而不是“ L” |“ D”)
现在,查询将是
select count(user_id) from video_likes where video_id=? and affinity=?
select affinity from video_likes where user_id=? and video_id=?
注意:video_id和user_id上应该有一个唯一的密钥;每个视频/用户组合的状态不能超过一个
添加几个基本字段:
Video Video_Likes User video_id --> video_id title user_id <--- user_id affinity name
简化的脚本,目前省略了许多PHP逻辑
videos.php
<?php // assuming you already have a PDO object named $pdo... $sql = "select video.video_id, video.title, likes.total_likes, dislikes.total_dislikes from Video left join ( select video_id, count(video_id) total_likes from Video_Likes where affinity = :like -- 'Like' group by video_id ) likes on video.video_id = likes.video_id left join ( select video_id, count(video_id) total_dislikes from Video_Likes where affinity = :dislike -- 'Dislike' group by video_id ) dislikes on video.video_id = likes.video_id order by video.title"; $stmt = $pdo->prepare($sql); $stmt->execute(['like'=>'Like','dislike'=>'Dislike']); // This is the end of all the PHP logic. // Now, we will output the view. No more PHP except iteration, variable substitution and minor conditionals ?> <html> <head><title>Sample</title></head> <body> <h1>Video List</h1> <table> <tr> <th>Video</th> <th>Likes</th> <th>Dislikes</th> </tr> <?php foreach($stmt as $row): ?> <tr> <td><?= htmlentities($row['title']) ?></td> <td><?= htmlentities($row['likes']) ?? 'No Votes' ?></td> <td><?= htmlentities($row['dislikes']) ?? 'No Votes' ?></td> </tr> <?php endforeach; ?> </table> </body> </html>
假设在页面中加载了JQuery(上面未显示),请添加ajax传输
就目前而言,没有钩子也没有任何将信息传输到ajax的方法。通过添加类和数据属性来修复它:
<?php foreach($stmt as $row): ?> <tr> <td><?= $row['title'] ?></td> <td class="video-like" data-video-id="<?= $row['video_id'] ?>"> <?= $row['likes'] ?? 'No Votes' ?> </td> <td class="video-dislike" data-video-id="<?= $row['video_id'] ?>"> <?= $row['dislikes'] ?? 'No Votes' ?> </td> </tr> <?php endforeach; ?>
现在,在</body>标签之前添加几个侦听器和一个ajax函数:
</body>
<script> // assign this from php var user_id = "<?= $user_id ?>"; // the document ready section is not strictly needed, but doesn't hurt... $( document ).ready() { $('.video-like').on('click', function() { var video_id = $(this).data('video-id'); // may be data('videoId') setAffinity(video_id, 'Like'); }); $('.video-dislike').on('click', function() { var video_id = $(this).data('video-id'); // may be data('videoId') setAffinity(video_id, 'Dislike'); }); // close document ready } function setAffinity(video_id, affinity) { $.ajax({ url: "./videos.php", type: "post", data: { user_id: user_id, video_id: video_id, affinity: affinity }, success: function (data) { if(data.status == 'success') { // do something } else { // do something else } } }); } </script>
在获取页面数据的逻辑($sql = "select video.video_id, video.title, likes.total_likes, dislikes.total_dislikes ...")之前,请检查POST提交。这使REST事务到位。
$sql = "select video.video_id, video.title, likes.total_likes, dislikes.total_dislikes ..."
<?php // assuming you have managed user login and saved user_id in session session_start(); $user_id = $_SESSION['user_id'] ?? false; // if there is a POST submission, we know a change to data is being requested. if($user_id && array_key_exists('video_id',$_POST)) { // we are returning JSON; there can be no output before this. That's why this is the first order of business. header("Content-type:application/json"); // early exit on data validation failure if( !is_numeric($_POST['video_id'] ) { print json_encode( ['status'=>'failed', 'message'=>'Invalid video selected'] ); die; } $video_id = $_POST['video_id']; // early exit on data validation failure if( !in_array( ['Like','Dislike'], $_POST['affinity'] ) { print json_encode( ['status'=>'failed', 'message'=>'You must select Like or Dislike'] ); die; } $affinity = $_POST['affinity']; $sql = "insert into video_likes (video_id, user_id, affinity) values(?,?,?)"; $stmt = $pdo->prepare($sql); $success = $stmt->execute( [$video_id, $user_id, $affinity] ); // early exit on failure if(!$success) { print json_encode( ['status'=>'failed','message'=>'Your selection was not recorded'] ); die; } // let's send back the new count of likes and dislikes $sql = "select count(user_id) total from video_likes where video_id=? and affinity=?"; $stmt= $pdo->prepare($sql); $stmt->execute([$video_id, 'Likes']); $likes = $stmt->fetchColumn(); $stmt->execute([$video_id, 'Dislikes']); $dislikes = $stmt->fetchColumn(); print json_encode( ['status'=>'success', 'likes'=>$likes, 'dislikes'=>$dislikes] ); die; } // continue with the page presentation as above...
返回javascript函数setAffinity()…
setAffinity()
function setAffinity(video_id, affinity) { $.ajax({ url: "./videos.php", type: "post", data: { user_id: user_id, video_id: video_id, affinity: affinity }, success: function (data) { if(data.status == 'success') { // you will receive back {data: 'success', likes: $likes, dislikes: $dislikes} // locate the affected row and update the values // you may have to create an id for each like and dislike if this doesn't work... $('.video-like[data-video-id="' +data.likes+ '"]').html(data.likes); $('.video-dislike[data-video-id="' +data.dislikes+ '"]').html(data.likes); } else { alert(data.message); } } }); }