I have tried making a few posts about this problem, but have decided to collect everything in this final one to hopefully somehow solve it.
I am building a site where users can vote on questions from a database. There's no login and so, to make sure everyone can only vote once per question, I am using their IP together with the ID of the question.
First, I get the ID and IP address and store both, making sure they are integers:
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);
I then check to see if the user has already votes, using the two variables. This is where I expect the problem arises. I get a:
Notice: Trying to get property of non-object
from line 116 which is: $row_cnt = $result->num_rows.
Furthermore var_dump ($result) returns bool(false) and var_dump ($row_cnt) returns Null. Adding quotes around the two variables in the query, $ip_long and $id fixes the problem while localhost, but not on my server.
A local var_dump($result) with quotes around the variables returns the following:
object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }
I would like to add 1 to the QuestionVotes for the specific question and then remove the option to vote on that same question for the specific IP Address.
//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();
}
}
And lastly, here is the code I use to build the html boxes containing database question information, add a voting button that displays the current votes and append, what is used as QuestionID, to the 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";
}
My tables are as follows:
Question: QuestionID(int11)(pk), QuestionHeader(varchar(20)), QuestionText(text), QuestionVotes(int(5))
User_Votes: UserID(unsigned, int(39)), QuestionID(int(11))
There are couple of things I would like to point out. First, your error:
I get a 'Notice: Trying to get property of non-object' from line 116 which is: $row_cnt = $result->num_rows;.
When you call mysqli->query() with a select query that finds no results then returned object is not an object but instead false.
Second, instead of COUNT(*), just use *.
So to maintain your logic, you should do something like this:
//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";
}
Edited:
//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')");
}
Re-edited:
You have to call $stmt->store_result() after $stmt->execute(). And your $stmt->get_result() is unnecessary here because you're not using the selected data.
Part of a comment from the 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.
So your code should be like this:
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();
}
}
Sidenote: Please don't mix the procedural and object oriented style of mysqli.