mysql - 评级脚本按ip限制投票

Question

我试图限制一个IP可以对数据库中的图像进行投票的次数，但我确实遇到了问题

我的两个表的概述

T1(评级)

id          votes       rating      category     rating
----------- ---------- ----------- ----------- -----------

T2(评级用户)

user_id     user_ip     user_vote  user_source
----------- ---------- ----------- -----------

表 1 -> id有关系到表 2 -> user_source

过去两天我尝试了很多不同的查询，但我就是不知道如何做到这一点:(

现在我使用这个查询:

select *
from rating
left join rating_users on rating_users.user_source = rating.id
where rating_users.user_ip is null
    or rating_users.user_ip != $ip
order by RAND() LIMIT 1

假设 table1 有 5 行数据..

如果 ip1 进入网站并且表 2 为空(截断)，并且他对所有 5 个图像进行投票，那么“第 6 个”图像将什么都不是，这正是我想要的...

但是如果 ip2 进入并且表 2 仍然有来自 ip1 的投票，那么 ip2 可以根据自己的意愿投票，因为没有任何行是“NULL”...

我真的迷失了，自从周六/周日晚上以来我一直在尝试解决这个问题..

有人可以帮帮我吗，我这里有很多白发:(

编辑我的代码 ->

Forside.php (index.php)

<?php
$dbCon = dbCon();

$sql = "SELECT * FROM rating ORDER BY id DESC LIMIT 1";
$result = $dbCon->query($sql);
$result = $result->fetch_object();

$id = $result->id;

//$tal = ip2long($_SERVER['REMOTE_ADDR']);
//$tal = long2ip($_SERVER['REMOTE_ADDR']);
$ip = ip2long($_SERVER['REMOTE_ADDR']);

// CHECKKER OM DEN IP HAR VOTET DETTE BILLEDE
$sql = "SELECT user_ip, user_source, user_vote FROM rating_users WHERE user_ip = $ip AND user_source = $id";
$result = $dbCon->query($sql);
$result = $result->fetch_object();
$rating = $result->user_vote;


if (!empty($result)) {
//    echo 'Du har allerede votet dette billede : ' . $rating . "/5";
    //IMGINFO
    $string = "<div class=\"flex absolute centerAm borc1 boxsb\">";
    $string = $string .= "<div id=\"innerVotingWrapper\" class=\"innerVotingWrapper relative flex \">";

    $string = $string .= "<div id=\"imgInfo\" class=\"imgInfo absolute top0 right0\">";
    $string = $string .= "<div id=\"imgRefresh\">";
    $string = $string .= "</div>";
    $string = $string .= "</div>";

    $string = $string .= "<div class=\"imageWrapper mauto relative fadeInClass\">";
    $string = $string .= "<img id=\"imgSrc\" src=\"assets/img/$id.png\" class=\"babeImg borb2 boxsb\">";
    $string = $string .= "<div id=\"starWrapper\" class=\"flex absolute left0 bot0 mar10\">";

    for ($i = 1; $i <= $rating; $i++) {
        $string = $string .= "<div class='voteFull'></div>";
    }
    for ($i = $rating; $i < 5; $i++) {
        $string = $string .= "<div class='voteEmpty'></div>";
    }
    $string = $string .= "</div>"; //STAR WRAPPER
    $string = $string .= "</div>"; //IMAGE WRAPPER
    $string = $string .= "</div>";//INNER WRAPPER
    $string = $string .= "</div>";//YDRE WRAPPER

//HVIS PERSONEN IKKE HAR STEMT FØR
} else {


    $string = "<div class=\"flex absolute centerAm borc1 boxsb\">";
    $string = $string .= "<div id=\"innerVotingWrapper\" class=\"innerVotingWrapper relative flex \">";
    //IMGINFO
    $string = $string .= "<div id=\"imgInfo\" class=\"imgInfo absolute top0 right0\">";
    $string = $string .= "</div>";

    $string .= "<div class=\"imageWrapper mauto relative fadeInClass\">";

    $string .= "<img id=\"imgSrc\" src=\"assets/img/$id.png\" class=\"babeImg borb2 boxsb\">";

    //BILLEDE VALUEN
    $string .= "<input id=\"imgValue\" class=\"absolute displayn\" type=\"radio\" value=\"$id\">";

    //STAR FORM
    $string .= "<div id=\"starWrapper\" class=\"starWrapper flex center absolute left0 bot0 mar10\">";
    $string .= "<form action=\"\" method=\"post\" class=\"flex flex-drr absolute bot0 left0\">";

    for ($i = 5; $i > 0; $i--) {
        $string .= "<input id=\"vote$i\" class=\"vote displayn\" type=\"radio\" name=\"vote\" value=\"$i\">";
        $string .= "<label for=\"vote$i\"></label>";
    }

    //SUBMIT FORM
    $string .= "<input type=\"submit\" id=\"voteSubmit\" class=\"displayn\">";

    $string .= "</form>";
    $string .= "</div>"; //starWrapper
    $string .= "</div>"; //imageWrapper
    $string .= "</div>"; //innerVotingWrapper
    $string .= "</div>"; //YDRE NAV TIL AT CENTRERE
}
//COL10
$test = "<div id=\"testid\" class=\"bgw\">";
$test .= "</div>";
?>

<div class="flex">
    <div class="col10 bgc2">
        <?php echo $test; ?>
    </div>
    <div class="col90 bgc1 relative vh100">
        <?php echo $string; ?>
    </div>
</div>

newimage.php

<?php

require_once '../includes/db.php';
require_once '../includes/functions.php';
$dbCon = dbCon();

//$voteValue = filter_input(INPUT_POST, 'voteValue', FILTER_SANITIZE_NUMBER_INT);
//$imgValue = filter_input(INPUT_POST, 'imgValue', FILTER_SANITIZE_NUMBER_INT);

$voteValue = $_POST['voteValue'];
$imgValue = $_POST['imgValue'];

$ip = ip2long($_SERVER['REMOTE_ADDR']);

$stmtt = $dbCon->prepare("INSERT INTO rating_users (user_ip, user_vote, user_source) VALUES (?, ?, ?)");
$stmtt->bind_param('iii', $ip, $voteValue, $imgValue);
$stmtt->execute();

//UPDATERE DATABASED
$stmt = $dbCon->prepare("UPDATE rating SET
    votes = votes + 1,
    rating = rating + ? WHERE id = ?");
$stmt->bind_param('ii', $voteValue, $imgValue);
$stmt->execute();


//LAV EN SQL QUERY SOM CHECKKER AT id ikke matcher et id allerede votet af den ip
//SENDER NY QUERY AFSTED

$sql = "SELECT * FROM rating INNER JOIN rating_users ON rating_users.user_source = rating.id WHERE rating_users.user_source= $user_source AND (rating_users.user_id= $user_id OR rating_user.ip= $ip ) ";
$result = $dbCon->query($sql);
$result = $result->fetch_object();
$id = $result->id;
//HVIS DEN IKKE ER TOM SKAL DET BILLEDE + RATING DIVS SENDES SOM JSON
$imgSrc = "assets/img/" . $id . ".png";
$arr = array('imgSrc' => $imgSrc, 'id' => $id);

echo json_encode($arr);

Javascript/Ajax

var vote = document.getElementsByClassName('vote');
var voteL = vote.length;
for (let i = 0; i < voteL; i++) {
    //SKAL VÆRE LET VALUE
    let voteValue = vote[i].value;

    vote[i].addEventListener('click', function () {
        var imgValue = document.getElementById("imgValue").value;
        newImage(voteValue, imgValue);
        console.log(vote[i].value);
    });
}

function newImage(voteValue, imgValue) {
    var http = new XMLHttpRequest();
    var url = "pages/newImage.php";
    var params = "voteValue=" + voteValue + "&imgValue=" + imgValue;
    http.open("POST", url, true);
    http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    http.onreadystatechange = function () { //KALDER FUNKTIONEN NÅR DNE ER KLAR
        if (http.readyState == 4 && http.status == 200) {

            var Data = JSON.parse(this.responseText);
            console.log(this.responseText);

            var imgSrc = document.getElementById('imgSrc');
            imgSrc.src = Data.imgSrc;
            var imgValue = document.getElementById('imgValue');
            imgValue.value = Data.id;

            var ratingValue = Data.rating;
            var starWrapper = document.getElementById('starWrapper');
            //HVIS IPEN HAR STEMT PÅ BILLEDET BLIVER HANS VOTES RETURNERET TIL VISNING OG IKKE MULIGT AT STEMME
            if (Data.rating) {

                // OPRETTER VOTED/EMPTY STARS HVIS DER ER VOTET
                var string = "";
                for (i = 1; i <= ratingValue; i++) {
                    string = string + "<div class=\"voteFull\"></div>";
                }
                for (i = ratingValue; i < 5; i++) {
                    string = string + "<div class=\"voteEmpty\"></div>";
                }
                starWrapper.innerHTML = string;

            } else {
                console.log('Data.rating er ikke sat');
            }

        }
    };
    http.send(params);
}

var testId = document.getElementById('testid');
testId.addEventListener('click', function () {
    console.log('Clicked');
});

Answer 1

这是数据 1 的要求，您应该对列 user_source 上的 t2 有一个唯一的约束，因为用户只能投票一次，另外您可以添加 ip 到唯一约束以限制每个 ip/用户一票

SELECT *
FROM rating
INNER JOIN rating_users 
    ON rating_users.user_source = rating.id
WHERE rating_users.user_source= $user_source
AND (rating_users.user_id= $user_id OR rating_user.ip= $ip ) 

如果用户或 IP 尚未为当前照片投票，则上述查询应返回零

编辑:我更改了查询以限制为 user_id/user_ip 并分段到当前照片 (user_source)。这意味着您必须将以下内容传递给查询 $user_source、$user_id、$ip