如何防止重复输入用户 ID 和评级字段，当两者已经存在于一行中时

Question

当用户登录并在产品中给出评级时，我在 PHP 星级中遇到了问题，数据被输入到表中，在这段时间里我也更新了评级，但问题是当我注销然后再次使用我的会话 ID 输入时，数据行没有更新，相反，它将再次使用用户ID进行产品评级，但数据库中的产品字段同时为空。包含 jQuery 和 PHP 代码：

$(function(){
    $('.rating').barrating({
        theme: 'fontawesome-stars',
        onSelect: function(value, text, event){
            // Get element id by data-id attribute
            var el = this;
            var el_id = el.$elem.data('id');
            // rating was selected by a user
            if(typeof(event)!=='undefined'){
                var split_id  = el_id.split("_");
                var productId = split_id[1];  // productId
                // AJAX Request
                $.ajax({
                     url: 'rating_ajaxData.php',
                    type: 'post',
                    data: {productId:productId,rating:value},
                    dataType: 'json',
                    success: function(data){
                        // Update average
                        var average=data['averageRating'];
                        $('#avgrating_'+productId).text(average);
                    }
                });
            }
        }
    });
});

rating_ajaxData.php

<?php
require 'lib/Session.php';
Session::init();
require 'lib/Database.php';
require 'classes/Customer.php';
$cmr =  new Customer();
$db  =  new Database();
$cmrId =  Session::get("customerId"); 
if(isset($_POST['productId']) && isset($_POST['rating'])){
  $productId = $_POST['productId'];
  $rating    = $_POST['rating'];
  //$ratingProcess = $cmr->processRatingByUser($productid,$rating);  
        $query     = "SELECT COUNT(*) AS cntproduct FROM tbl_rating WHERE cmrId = '$cmrId' AND productId = '$productId'";
        $result    =  $db->select($query)->fetch_assoc();                   
            $count = $result['cntproduct'];       
            if($count==0){
            $insertquery  = "INSERT INTO tbl_rating(cmrId,productId,rating) VALUES('$cmrId','$productId','$rating')";
            $ratingInsert = $db->insert($insertquery);
            //return $ratingInsert;            
            }else{
                $updtquery    = "UPDATE tbl_rating
                                 SET    
                                 rating       = '$rating' 
                                 WHERE cmrId  = '$cmrId' AND productId = '$productId'";
                $ratingupdate = $db->update($updtquery);
                //return $ratingupdate;
            }  
  //get average
  $avgratingquery = "SELECT ROUND(AVG(rating),1) as averageRating FROM tbl_rating WHERE productId = '$productId'";
  $avgresult = $db->select($avgratingquery)->fetch_assoc();
  $averageRating = $avgresult['averageRating'];
  $return_arr = array("averageRating"=>$averageRating);
  echo json_encode($return_arr);  
}                    
?>

Answer 1

始终检查$productId是否大于 0。它将阻止代码执行并解决问题。在这种情况下，只需更改isset($_POST['productId']) !empty($_POST['productId']) .

...
require 'lib/Session.php';
Session::init();
require 'lib/Database.php';
require 'classes/Customer.php';
$cmr =  new Customer();
$db  =  new Database();
$cmrId =  Session::get("customerId"); 
if(!empty($_POST['productId']) && isset($_POST['rating'])){
  $productId = $_POST['productId'];
  $rating    = $_POST['rating'];
  //$ratingProcess = $cmr->processRatingByUser($productid,$rating
...

这是您正在处理的问题吗？

更好的解决方案，多做一点工作

更好的方法是将表中cmrId和productId设置为唯一tbl_rating，而不是在重复键更新时使用 mysql 插入 (http://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/(前任。

INSERT INTO tbl_rating (cmrId,productId,rating) VALUES('$cmrId','$productId','$rating')
ON DUPLICATE KEY UPDATE rating='$rating'