如何根据提交的赞成票/反对票增加INT的上/下？

How to increment INT up/down based on upvote/downvote form submission?

提问人：DaTZcodie 提问时间：9/14/2023 最后编辑：halferDaTZcodie 更新时间：9/25/2023 访问量：75

问：

我正在尝试创建一个带有赞成/反对票的简单论坛，但我无法获得以“1”或“-1”形式提交的值，每次我提交投票时，它都会在“投票”列中作为“0”提交，并且我将投票列设置为整数（INT）并在PHP期间也使用INT， 所以我不认为这是问题所在。

我试着问 ChatGPT，但我很确定这只会让事情变得更糟/过于复杂。

这是我的投票表列：

| id | username | userID | title | description | uniqid | url | vote | type | date

这是我的 HTML/前端页面：

<?php
 // check user session //
 if (isset($_SESSION['usrname'])) {
 $field = "username";
 $value = $_SESSION['usrname'];
 $username = $_SESSION['usrname'];
} else {
 $field = "userID";
 $value = $userID;
 $username = "";
}

// check if the current user has voted the question //
$stmtVoted = $dbh->prepare("SELECT `vote` FROM `votes` WHERE `$field` = :value AND `title` = :title");
$stmtVoted->bindParam(':value', $value, PDO::PARAM_STR);
$stmtVoted->bindParam(':title', $fetch['title'], PDO::PARAM_STR);
$stmtVoted->execute();
$vote = $stmtVoted->fetchColumn();

// count the total user votes for the question //
$stmtVotesTotal = $dbh->prepare("SELECT SUM(`vote`) FROM `votes` WHERE `title` = :title");
$stmtVotesTotal->bindParam(':title', $fetch['title'], PDO::PARAM_STR);
$stmtVotesTotal->execute();
$totalVotes = $stmtVotesTotal->fetchColumn();
?>

// vote form with some information about the question like title, description, URL etc //
<div id="votePostFormContainer-<?php echo $fetch['id'];?>" style="float:right;">
  <form class="vote-post-form" action="" method="POST">
    <input type="hidden" value="<?php echo $fetch['uniqid'];?>" name="postUniqid">
    <input type="hidden" value="Question" name="type">
    <input type="hidden" value="<?php echo $username;?>" name="username">
    <input type="hidden" value="<?php echo $fetch['title'];?>" name="title">
    <input type="hidden" value="<?php echo $fetch['question'];?>" name="description">
    <input type="hidden" value="<?php echo $fetch['url'];?>" name="url">
    <input type="hidden" value="<?php echo $fetch['username'];?>" name="author">
    <input type="hidden" value="<?php echo $userID;?>" name="userID">
    <input type="hidden" value="<?php echo date('Y/m/d H:i:s');?>" name="date">

    <div style="display:flex;">
      <button type="button" class="vote-up-button" data-vote="up">
        <iconify-icon width="18" height="18" icon="<?php echo $vote == 1 ? 'bxs:upvote' : 'bx:upvote'; ?>"></iconify-icon>
      </button>

      <div class="vote-count" id="voteCount-<?php echo $fetch['uniqid'];?>" style="margin-top:-2px;color:#666;"><small><?php if (empty($totalVotes)) {echo '0';} else {echo $totalVotes;}?></small></div>

      <button type="button" class="vote-down-button" data-vote="down">
          <iconify-icon width="18" height="18" icon="<?php echo $vote == -1 ? 'bxs:downvote' : 'bx:downvote'; ?>"></iconify-icon>
      </button>
    </div>
  </form>
</div>

<script type="text/javascript" src="/Blog/resources/voteQuestion.js"></script>

这是我通过ajax处理提交的voteQuestion.js，它也意味着处理前端的投票增量，但这不起作用，因为表单提交不断提交“0”到“投票”列而不是“1”或“-1”。

  (function() {
$(document).ready(function() {
    $(".vote-up-button, .vote-down-button").on("click", function(e) {
        e.preventDefault();
        var postUniqid = $(this).siblings('input[name="postUniqid"]').val();
        var voteType = $(this).data('vote'); // "up" or "down"
        var buttonElement = $(this); // Store the button element for later use

        // Map voteType to an integer value (+1 for "up" and -1 for "down")
        var voteValue = (voteType === 'up') ? 1 : -1;

        // Find the vote count element and update it
        var voteCountElement = $("#voteCount-" + postUniqid);
        var currentCount = parseInt(voteCountElement.text()) || 0;
        currentCount += voteValue;
        currentCount = Math.max(currentCount, 0);
        voteCountElement.text(currentCount.toString());

        // Update the icons based on the vote action
        var upvoteIcon = '<iconify-icon width="18" height="18" icon="bxs:upvote"></iconify-icon>';
        var downvoteIcon = '<iconify-icon width="18" height="18" icon="bxs:downvote"></iconify-icon';
        
        if (voteType === 'up') {
            // User upvoted, change the icon to the solid upvote icon
            buttonElement.html(upvoteIcon);
            buttonElement.data('vote', 'remove');
        } else if (voteType === 'down') {
            // User downvoted, change the icon to the solid downvote icon
            buttonElement.html(downvoteIcon);
            buttonElement.data('vote', 'remove');
        } else if (voteType === 'remove') {
            // User wants to remove their vote, change the icon back to outline
            buttonElement.html(voteValue === 1 ? upvoteIcon : downvoteIcon);
            buttonElement.data('vote', voteValue === 1 ? 'up' : 'down');
        }

        // Send the AJAX request to update the vote on the server
        var formData4 = {
            'vote': voteValue, // Send the integer value
            'postUniqid': $("input[name='postUniqid']").val(),
            'title': $("input[name='title']").val(),
            'description': $("input[name='description']").val(),
            'type': $("input[name='type']").val(),
            'url': $("input[name='url']").val(),
            'username': $("input[name='username']").val(),
            'userID': $("input[name='userID']").val(),
            'date': $("input[name='date']").val(),
        };

        $.ajax({
            type: "POST",
            url: "/Blog/resources/PHP/voteQuestion.php",
            data: formData4,
            dataType: 'json',
            success: function(response) {
                if (response.hasOwnProperty('voteCount')) {
            // Update the vote count on frontend
            voteCountElement.text(response.voteCount);
            } else {
                console.log("Unexpected response format:", response);
            }

            },
            error: function(xhr, status, error) {
                console.log("AJAX error:", error);
            }
        });
    });
});
})();

这是我的服务器端 PHP，它处理实际的插入、更新、删除行/投票......

<?php
if (isset($_POST['title']) && isset($_POST['username']) && isset($_POST['vote'])) {
$title = filter_var($_POST['title'], FILTER_SANITIZE_STRING);
$type = filter_var($_POST['type'], FILTER_SANITIZE_STRING);
$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
$description = filter_var($_POST['description'], FILTER_SANITIZE_STRING);
$postUniqid = filter_var($_POST['postUniqid'], FILTER_SANITIZE_STRING);
$url = filter_var($_POST['url'], FILTER_SANITIZE_STRING);
$date = filter_var($_POST['date'], FILTER_SANITIZE_STRING);
$userID = filter_var($_POST['userID'], FILTER_SANITIZE_STRING);
$vote = $_POST['vote'];

// Check if the vote already exists for the given user and post
$stmt = $dbh->prepare("SELECT COUNT(*) FROM `votes` WHERE `username` = :username AND `title` = :title AND `uniqid` = :uniqid");
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':title', $title, PDO::PARAM_STR);
$stmt->bindParam(':uniqid', $postUniqid, PDO::PARAM_STR);
$stmt->execute();
$count = $stmt->fetchColumn();

if ($count > 0) {
    // User has already voted for this post
    // Check the current vote for the user
    $stmt = $dbh->prepare("SELECT `vote` FROM `votes` WHERE `username` = :username AND `title` = :title AND `uniqid` = :uniqid");
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':title', $title, PDO::PARAM_STR);
    $stmt->bindParam(':uniqid', $postUniqid, PDO::PARAM_STR);
    $stmt->execute();
    $currentVote = $stmt->fetchColumn();

    if ($currentVote != $vote) {
        // User wants to switch their vote
        $stmt = $dbh->prepare("UPDATE `votes` SET `vote` = :vote WHERE `username` = :username AND `title` = :title AND `uniqid` = :uniqid");
        $stmt->bindParam(':vote', $vote, PDO::PARAM_INT);
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':title', $title, PDO::PARAM_STR);
        $stmt->bindParam(':uniqid', $postUniqid, PDO::PARAM_STR);
        $stmt->execute();
    } else {
        // User wants to remove their vote
        $stmt = $dbh->prepare("DELETE FROM `votes` WHERE `username` = :username AND `title` = :title AND `uniqid` = :uniqid");
        $stmt->bindParam(':username', $username, PDO::PARAM_STR);
        $stmt->bindParam(':title', $title, PDO::PARAM_STR);
        $stmt->bindParam(':uniqid', $postUniqid, PDO::PARAM_STR);
        $stmt->execute();
    }
} else {
    // Insert a new vote
    $stmt = $dbh->prepare("INSERT INTO `votes` (`title`, `type`, `username`, `userID`, `description`, `uniqid`, `url`, `date`, `vote`) VALUES (:title, :type, :username, :userID, :description, :uniqid, :url, :date, :vote)");
    $stmt->bindParam(':title', $title, PDO::PARAM_STR);
    $stmt->bindParam(':type', $type, PDO::PARAM_STR);
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->bindParam(':userID', $userID, PDO::PARAM_STR);
    $stmt->bindParam(':description', $description, PDO::PARAM_STR);
    $stmt->bindParam(':uniqid', $postUniqid, PDO::PARAM_STR);
    $stmt->bindParam(':url', $url, PDO::PARAM_STR);
    $stmt->bindParam(':date', $date, PDO::PARAM_STR);
    $stmt->bindParam(':vote', $vote, PDO::PARAM_INT);
    $stmt->execute();
}

$stmtCount = $dbh->prepare("SELECT COALESCE(SUM(`vote`), 0) FROM `votes` WHERE `uniqid` = :postUniqid");
$stmtCount->bindParam(':postUniqid', $postUniqid, PDO::PARAM_STR);
$stmtCount->execute();
$voteCount = (int)$stmtCount->fetchColumn();

$response = array('voteCount' => $voteCount);
} else {
$response = array('error' => 'Invalid request');
}

// Output the response as JSON
header('Content-Type: application/json');
echo json_encode($response);
exit;
?>
javascript php sql ajax 表单

评论

1赞 ADyson 9/14/2023
I tried asking chatGPT but I'm pretty sure that was just making things worse...为什么这不让我感到惊讶。这是一个对话引擎，而不是程序员：-）
0赞 DaTZcodie 9/14/2023
@ADyson哈哈，我知道没错，但它有时很有用：'）
1赞 ADyson 9/14/2023
无论如何，在你提交给服务器的JS代码中......据我所知，可以填充“向上”或“向下”。显然，这两个值都不是整数。但是 PHP 尝试在 SQL 查询中将提交的值直接传递到数据库表的列中。'vote': voteTypevoteTypevote
1赞 Barmar 9/14/2023
@DaTZcodie ChatGPT 可能对有经验的程序员有用，但它可以为常见操作提供样板。如果你一开始就不知道自己在做什么，那就没有用了。
0赞 DaTZcodie 9/14/2023
@ADyson啊，是的，你的错，我的坏，我不久前把它设置为+1或-1，但看起来在某个时候发生了一些变化，我现在是这样设置的：但由于某种原因，总票数仍然没有增加var voteValue = (voteType === 'up') ? 1 : -1;

答：

-1赞 Filip Albert 9/14/2023 #1

your 似乎只包含值和 。如果你想让它有一个整数值，我建议你像这样更新你的jQuery formData4对象。$voteupdown

var formData4 = {
   'vote': currentCount,
   'postUniqid': $("input[name='postUniqid']").val(),
   'title': $("input[name='title']").val(),
   'description': $("input[name='description']").val(),
   'type': $("input[name='type']").val(),
   'url': $("input[name='url']").val(),
   'username': $("input[name='username']").val(),
   'userID': $("input[name='userID']").val(),
   'date': $("input[name='date']").val(),
};

上一个：为什么在已经插入模板时获得“CSRF 令牌丢失”？

下一个：.htaccess重写规则未提交发布表单数据？