php - 根据保存在数据库中的 IP 地址删除特定对象的投票访问权限

Question

我已经尝试发布一些关于这个问题的帖子，但我决定在最后一个帖子中收集所有内容，希望能以某种方式解决它。

我正在构建一个网站，用户可以在该网站上对数据库中的问题进行投票。没有登录，因此，为了确保每个人只能为每个问题投票一次，我将他们的 IP 与问题的 ID 一起使用。

首先，我获取 ID 和 IP 地址并存储两者，确保它们是整数:

if(isset($_GET['id']))
          {

           //Get IP address

           //Test if it is a shared client
           if (!empty($_SERVER['HTTP_CLIENT_IP'])){
            $ip=$_SERVER['HTTP_CLIENT_IP'];

           //Is it a proxy address
           }elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
            $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
           }else{
            $ip=$_SERVER['REMOTE_ADDR'];
           }

           //Save id and IP address as variables
           $id = $_GET['id'];
           $ip_long = ip2long($ip);

然后我使用这两个变量检查​​用户是否已经投票。这是我预计会出现问题的地方。我得到一个:

Notice: Trying to get property of non-object

第 116 行是:$row_cnt = $result->num_rows。

此外 var_dump ($result) 返回 bool(false) 并且 var_dump ($row_cnt) 返回 Null .在查询中的两个变量周围添加引号，$ip_long 和 $id 修复了本地主机上的问题，但不是在我的服务器上。

在变量周围加上引号的本地 var_dump($result) 返回以下内容:

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

我想为特定问题的 QuestionVotes 添加 1，然后删除为特定 IP 地址对同一问题投票的选项。

//Save id and IP address as variables
           $id = $_GET['id'];
           $ip_long = ip2long($ip);

           ///Check to see if user already voted
           $stmt = $conn->prepare("SELECT * FROM User_Votes where UserID = ? and QuestionID = ?");
           mysqli_stmt_bind_param($stmt, 'ss', $ip_long, $id);
           $stmt->execute();
           $result = $stmt->get_result();
            if($result->num_rows){
                //The user has already voted
                echo "Already voted";
            }else{
                //Add IP Address and ID to the User_Votes table
                $stmt = $conn->prepare("INSERT INTO User_Votes (UserID, QuestionID) VALUES (?, ?)");
                mysqli_stmt_bind_param($stmt, 'ss', $ip_long, $id);
                $stmt->execute();
                $stmt = $conn->prepare("UPDATE Question SET QuestionVotes = QuestionVotes + 1 where QuestionID = ?");
                mysqli_stmt_bind_param($stmt, 's', $id);
                $stmt->execute();
            }

       }

最后，这是我用来构建包含数据库问题信息的 html 框的代码，添加一个显示当前投票的投票按钮，并将用作 QuestionID 的内容附加到 url:

// Build 4 question boxes from database Question table, including voting button
      $stmt = $conn->prepare("SELECT * FROM question ORDER BY QuestionVotes DESC LIMIT 4");
      $stmt->execute();

      $result = $stmt->get_result();
      if ($result->num_rows > 0) {
           // output data of each row
           while($row = $result->fetch_assoc()) {
               //$row["QuestionID"] to add id to url
               echo "<div class=\"col-md-3\"><h2>". $row["QuestionHeader"]. "</h2><p>". $row["QuestionText"]. "</p><p><a href=\"index.php?id=". $row["QuestionID"]. "\" class=\"btn btn-success\"> " . $row["QuestionVotes"] . "</a></p></div>";

           }
      }
      else
      {
        echo "0 results";
      }

我的表格如下:

问题:QuestionID(int11)(pk)、QuestionHeader(varchar(20))、QuestionText(text)、QuestionVotes(int(5))
User_Votes: UserID(unsigned, int(39)), QuestionID(int(11))

Answer 1

有几件事我想指出。 首先，您的错误:

I get a 'Notice: Trying to get property of non-object' from line 116 which is: $row_cnt = $result->num_rows;.

当您使用未找到任何结果的选择查询调用 mysqli->query() 时，返回的对象不是对象而是 false。

其次，不用COUNT(*)，只用*。

所以为了保持你的逻辑，你应该做这样的事情:

//Check to see if user already voted
$result = $conn->query("SELECT * FROM User_Votes where UserID = '$ip_long' and QuestionID = '$id'");

if ($result === false) { 
    //Add IP Address and ID to the User_Votes table
    $result = $conn->query("INSERT INTO `User_Votes` (`UserID`, `QuestionID`) VALUES ('$ip_long', '$id')");
}elseif($result && $result->num_rows) { 
    //The user has already voted
    echo "Already voted";
}

已编辑:

//Check to see if user already voted
$result = $conn->query("SELECT * FROM User_Votes where UserID = '$ip_long' and QuestionID = '$id'");

if($result->num_rows){
    //The user has already voted
    echo "Already voted";
}else{
    //Add IP Address and ID to the User_Votes table
    $result = $conn->query("INSERT INTO User_Votes (UserID, QuestionID) VALUES ('$ip_long', '$id')");
}

重新编辑:

您必须在 $stmt->execute() 之后调用 $stmt->store_result()。并且您的 $stmt->get_result() 在这里是不必要的，因为您没有使用选定的数据。

来自 documentation 的部分评论:

If you do not use mysqli_stmt_store_result( ), and immediatley call this function after executing a prepared statement, this function will usually return 0 as it has no way to know how many rows are in the result set as the result set is not saved in memory yet.

所以你的代码应该是这样的:

if(isset($_GET['id']) && !empty($_GET['id'])){
    $id = $_GET['id'];
    $ip_long = ip2long($ip);

    //Check to see if user already voted
    $stmt = $conn->prepare("SELECT * FROM User_Votes where UserID = ? and QuestionID = ?");
    $stmt->bind_param('ss', $ip_long, $id);
    $stmt->execute();
    $stmt->store_result();
    if($stmt->num_rows){
        //The user has already voted
        echo "Already voted";
    }else{
        //Add IP Address and ID to the User_Votes table
        $stmt = $conn->prepare("INSERT INTO User_Votes (UserID, QuestionID) VALUES (?, ?)");
        $stmt->bind_param('ss', $ip_long, $id);
        $stmt->execute();
        $stmt = $conn->prepare("UPDATE Question SET QuestionVotes = QuestionVotes + 1 where QuestionID = ?");
        $stmt->bind_param('s', $id);
        $stmt->execute();
    }
}

旁注:请不要混合 mysqli 的面向过程和面向对象的风格。