Avoid Duplicate Votes

Question

I am new to PHP/MySQL and the whole website designing. I am building a website where pre-defined users can vote. I have a database with a list of users. I am trying to avoid duplicate votes. I read that you could block IP address or use cookies, but I am trying to use another method.

In my database called 'users' I have three columns - Username, Password and flag. Flag has a default value of 0. Once, the user votes, I set the flag for that particular user to 1. Now, if the user tries to vote again, I want to check the value of flag in my database. If it's 0 I'd send him to "Thank You for voting" page and update another database I created called results which keeps track of the number of votes each candidate has received. If not, I take him to another page which says, "You have already voted." Everything is working fine so far, except I don't know how to read the value of flag in my database and use an if condition of it.

Here's what I have so far:

<?php

$host="localhost"; // Host name 
$username="dbxxxxx"; // Mysql username 
$password="password"; // Mysql password 
$db_name="dbxxxxx_users"; // Database name 
$tbl_name="users"; // Table name 


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$user = $_COOKIE["details"];  //cookie details has the username the user used to log in

$SQL = "SELECT flag FROM users WHERE Username='$user'";
$flag = mysql_query( $SQL );   //no clue what's happening here. Just trying random stuff
$db_field = mysql_fetch_assoc($flag);  


if($db_field==0)     //checking the value of flag in the database
{       
    mysql_query("UPDATE result SET Votes=Votes+1 //if flag in database = 0 
    WHERE Name='Candidate1'");  //updates result for candidate1 if the user voted for 1

    $user = $_COOKIE["details"];  //reading the cookie again. can be omitted.

    mysql_query("UPDATE users SET flag=1   //changing flag to 1 so user cannot vote again
    WHERE Username='$user'");

    header("location: http://www.lithuaniavote.com/thankyou.html");
 }

else    //flag != 1 or user has already voted
{
    header("location: http://www.lithuaniavote.com/alreadyvoted.html");
}

?>

PS: This code changes the flag from 0 to 1 in the database. However, there's something wrong with the if condition. I am able to vote even if the flag is 1, which is an indication that I have already voted or in other words, It never takes me to the Already Voted page.

Answer 1

Original code:

$SQL = "SELECT flag FROM users WHERE Username=$user";
$flag = mysql_query( $SQL );   //no clue what's happening here. Just trying random stuff
$db_field = mysql_fetch_assoc($flag);  
if($db_field==0)     //checking the value of flag in the database

Try this:

$SQL = "SELECT flag FROM users WHERE Username = '$user'"; // $user should be in 'quotes'
$flag = mysql_query( $SQL );  // This is the actual query to the database
$db_field = mysql_result($flag, 0);  // This is the result of the query.
if($db_field===0)  // Use 3 equals signs instead of 2 in this case (which means "exactly equal to")

Answer 2

I think you should try a much cleaner (and future-proof) approach. Let me re-construct the solution to your problem with PDO:

namespace Voting {
    $pdo = new \PDO("mysql:host={$host};dbname={$db_name};charset=utf8", $username, $password);

    if ($query1 = $pdo->prepare("SELECT `flag` FROM `users` WHERE `Username` = ?;", [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY])) {
        if ($query1->execute([$_COOKIE["details"]])) {
            $result = $query1->fetch(\PDO::FETCH_ASSOC);

            if (intval($result["flag"]) === 0) {
                if ($query2 = $pdo->prepare("UPDATE `users` SET `flag` = '1' WHERE `Username` = ?")) {
                    $query2->execute([$_COOKIE["details"]]);
                    $pdo = null;
                    header("Location: http://www.lithuaniavote.com/thankyou.html");
                }
            } else {
                $pdo = null;
                header("Location: http://www.lithuaniavote.com/alreadyvoted.html");
            }
        }
    }
}

WARNING: Take into account I'm not checking for $_COOKIE safety. You must do some form of sanitization to prevent injections and other vulnerabilities.