如果表中不存在客户端 IP，如何正确添加速率'votes'

Question

Hello Stack Overflow Community.

到目前为止，我已经提出了两个关于评级系统的主题，很高兴在您的帮助下，我设法实现了它。它工作正常，只是每个人都可以投票，所以我在每次投票后将客户的 IP 添加到数据库表"投票"中。我想帮我做一个检查，比如：1(如果客户已经投票，不要将其评级存储在数据库中。

另外，我如何避免插入postid和ip，它们是否已经存在于"投票"中？ 例如，我为ID 为 2 的帖子投票一次，它会创建： - 编号：1 - 后置号：2 - IP：客户端的IP

当再次为同一帖子投票时，它会创建相同的信息，因此这是无用的，应该被阻止。

投票表结构：

CREATE TABLE IF NOT EXISTS `votes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`postid` int(11) NOT NULL,
`ip` varchar(15) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

更新：/全价.php/

<?php 
$servername = "localhost";
$username = "root";
$password = "oregon";
$dbname = "project";
try {   
$connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
$postid = $_POST['post_id'];
$ip = $_SERVER['REMOTE_ADDR'];
$sql = $connection->prepare("SELECT postid, ip FROM votes WHERE ip = :ip");
$sql->bindParam(':ip', $ip, PDO::PARAM_STR); 
$sql->execute();
if($sql->rowCount() > 0){
die("You already voted");
}   
$postid = $_POST['post_id'];
$ip = $_SERVER['REMOTE_ADDR'];
$sql = $connection->prepare("INSERT INTO votes (postid, ip) VALUES (:postid, :ip)");
$sql->bindParam(':postid', $postid);
$sql->bindParam(':ip', $ip);    
$sql->execute();    
if (isset( $_POST['slct']) && $_POST['slct'] === "1") {
$rate = "1";
} else if (isset( $_POST['slct']) && $_POST['slct'] === "2") {
$rate = "2";
} else if (isset( $_POST['slct']) && $_POST['slct'] === "3") {
$rate = "3";
} else if(isset( $_POST['slct']) && $_POST['slct'] === "4") {
$rate = "4";
} else if(isset( $_POST['slct']) && $_POST['slct'] === "5") {
$rate = "5";
} else {
$rate = "0";
}
$id = $_POST['post_id'];
$rating = $_POST['rate'];
$clicks = $_POST['clicks'];
$total_clicks = $clicks + '1';
$max_rate = $rating + $rate;
if($_POST['slct'] == "0") {
$clicks = $_POST['clicks'];
$total_clicks = $clicks + '0';
}   
$sql = $connection->prepare("UPDATE posts SET rate = :max_rate, clicks = :total_clicks WHERE id = :id");
$sql->bindParam(':id', $id, PDO::PARAM_INT); 
$sql->bindParam(':max_rate', $max_rate, PDO::PARAM_INT);
$sql->bindParam(':total_clicks', $total_clicks, PDO::PARAM_INT);
if ($sql->execute()) {
header('Location: ../index.php');
} else { 
echo "Error updating record: " . $e->getMessage();
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$connection = null;
?>

问候。

Answer 1

您可以使用SELECT查询检查 IP 地址是否已在数据库中。

$ip = $_SERVER['REMOTE_ADDR'];
$sql = $connection->prepare("SELECT postid, ip FROM votes WHERE ip = :ip");
$sql->bindParam(':ip', $id, PDO::PARAM_INT); 
$sql->execute();
if($sql->rowCount() > 0){
// I prefer to use the rowCount() but also a fetch will be ok for your task
echo 'You already voted.';
} else { // do your saving tasks here }

Answer 2

在我发布整个代码之前，我希望你尝试一下。如果它有效，那么我将发布带有UPDATE查询的第二部分

<?php
$servername = "localhost";
$username = "root";
$password = "oregon";
$dbname = "project";
try {   
$connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
$sql = $connection->prepare("SELECT * FROM votes");
if ($sql->execute()) {
$row_votes = $sql->fetch();
if ($row_votes['postid'] == $_POST['post_id'] && $row_votes['ip'] == $_SERVER['REMOTE_ADDR']) {
echo "You've already voted for this post. You cannot vote again.";
} else {
$sql = $connection->prepare("INSERT INTO votes (postid, ip) VALUES (:postid, :ip)");
$sql->bindParam(':postid', $postid);
$sql->bindParam(':ip', $ip);    
$postid = $_POST['post_id'];
$ip = $_SERVER['REMOTE_ADDR'];
if ($sql->execute()) {
echo "INSERT was successeful";
} else {
echo "INSERT was unseccessful";
}
}
} else {
echo "Could not execute query";
}
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$connection = null;
?>