从您的选择查询中，我收集到这里涉及3个表：
Video Video_Likes Uservideo_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 Video_Likes Uservideo_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=?
找出用户喜欢还是不喜欢视频
步骤2：计划展示注意：video_id和user_id上应该有一个唯一的密钥；每个视频/用户组合的状态不能超过一个
添加几个基本字段：
Video Video_Likes Uservideo_id --> video_idtitle user_id <--- user_id affinity name
简化的脚本，目前省略了许多PHP逻辑
videos.php
步骤3：计划Javascript<?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 data-video-id="<?= $row['video_id'] ?>"> <?= $row['likes'] ?? 'No Votes' ?> </td> <td data-video-id="<?= $row['video_id'] ?>"> <?= $row['dislikes'] ?? 'No Votes' ?> </td> </tr> <?php endforeach; ?>
现在，在
</body>标签之前添加几个侦听器和一个ajax函数：
<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事务到位。
<?php// assuming you have managed user login and saved user_id in sessionsession_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_enpre( ['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_enpre( ['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_enpre( ['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_enpre( ['status'=>'success', 'likes'=>$likes, 'dislikes'=>$dislikes] ); die;}// continue with the page presentation as above...返回javascript函数
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); } } }); }欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）