PHP Mysql IF statements

Question

I'm currently working on a website which has 2 links. Upvote & downvote. Votes are stored in mysql, in a table called "data" with columns "yes" and "no"

I've successfully created a query to update the count of either yes or no, and then echo the value to the page. However, currently the user can spam click the buttons and the count will keep going up.

I've started logging IP addresses with $ip = $_SERVER['REMOTE_ADDR']; & putting them in a table called "ips" with column "ipaddresses".

Now, I want to change my code so that it will query mysql and check the 'ips' table for $ip and if it returns true, then die(); else if... execute upvote query. This will make it so a person can only vote once per IP.

Here is my current code:

<?php
if ($_GET['vote']=="yes") {
    // Connection to database
    $connection=mysqli_connect("hostname-here","username-here","password-here","database-here");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    mysqli_query($connection,"UPDATE data SET yes = (yes + 1) WHERE ID = $_GET[id];");
    mysqli_close($connection);

    echo "Voted.";     
}
?>

Help would be appreciated, I've googled a lot and can't find anything that works. Thanks!

Answer 1

You shouldn't use IP as a deciding factor if someone has already voted. Multiple users can be coming from the same IP.

If you want to do this anyway, you should create a new table. Let's call it "user_action". This table should have a column called IP, and another called VOTE. You will have to log each individual user action, and check the IP before updating your "data" table.

Edit: Some pseudo code to help you more.

Create your table:

CREATE TABLE user_action (IP varchar(39), VOTE tinyint(1));

Simple PHP logic. Just fill in with actual MySQL commands (You apparently already know how to do this from your OP)

    $sSql = "SELECT vote FROM user_action WHERE IP = '" . $_SERVER['REMOTE_ADDR'] ."'";

    If (rowcount > 1) {
        //User already voted, update their answer.
        $sSql = "UPDATE user_action SET vote = " .$_GET['vote']. " WHERE IP = '" . $_SERVER['REMOTE_ADDR'] ."'";
    }
    Else {
        //User hasn't voted, insert their answer
        $sSql = "INSERT INTO user_action (vote, ip) VALUES(" .$_GET['vote']. ", '" . $_SERVER['REMOTE_ADDR'] ."'";
    }

And if you want to tell how many upvotes you have:

$sSql = "SELECT sum(vote) FROM user_action WHERE vote = 1";

Answer 2

Here you go:

<?php

if ($_GET['vote']=="yes") {
    // Connection to database
    $connection=mysqli_connect("fdb13.your-hosting.net","1789869_gow","niggers1","1789869_gow");
    // Check connection
    if (mysqli_connect_errno())
        {
            die("Failed to connect to MySQL: " . mysqli_connect_error());
        }

    // Check if they've already voted

    $result = mysqli_query($connection, "SELECT COUNT(*) AS already_voted FROM ips WHERE ip = '{$_SERVER['REMOTE_ADDR']}'") or die("Failed to query ips: " . mysqli_error());
    $row = mysqli_fetch_assoc($result);
    if ($row['already_voted']) {
        die("You already voted");
    }

    // Increasing the current value with 1
    mysqli_query($connection,"UPDATE girlsdata SET yes = (yes + 1) WHERE ID = $_GET[id];") or die("Failed to add vote: " . mysqli_error());
    mysqli_close($connection);

    echo "PHP successfully executed. Edit this out later.";    
}
?>