Allow users to vote once in PHP

Question

I'm working on a school project where I need to allow users to vote on pictures. Users can a picture up or down. It's the idea that they can change their vote anytime, but they can't undo their vote, so once they voted it's either up or down.

I've been trying some things but I can't seem to get it to work. It works when an user pressed an upvote for the first time, then the user is able to change his vote to a downvote. But when he tries to upvote again, nothing is happening this has been bugging me for a while now, I would appreciated any help.

Here is my code so far:

 if (isset($_SESSION['loggedin'])) {
    $result = mysql_query("SELECT * FROM user2pics WHERE picid = $id AND userid = $user");
    if (mysql_num_rows($result) == 0) {
        $votes_up = $cur_votes[0] + 1;
        $resultaat = mysql_query("UPDATE pics SET likes = $votes_up WHERE picid = $id");
        if ($resultaat) {
            $query = mysql_query("INSERT INTO user2pics (picid, userid, vote) VALUES ($id, $user, 1)");
            if ($query) {
                $effectiveVote = getEffectiveVotes($id);
                echo $effectiveVote . " votes";
            } elseif (!$query) {
                echo "Failed!";
            }
        } elseif (!$resultaat) {
            echo "Failed insert in pics!";
        }
    } else {
        $row = mysql_fetch_array($result);
        if ($row['vote'] == 0) {
            $votes_down = $cur_votes[0] + 1;
            $result = mysql_query("UPDATE pics SET likes = $votes_up WHERE picid = $id");
            if ($result) {
                $resultaat = $mysqli -> prepare("UPDATE user2pics SET vote = 1 WHERE picid = $id AND userid = $user");
                $resultaat -> execute();
                $effectiveVote = getEffectiveVotes($id);
                if ($resultaat -> affected_rows == 1) {
                    echo $effectiveVote . " votes";
                }
            }
        } else {
            $effectiveVote = getEffectiveVotes($id);
            echo $effectiveVote . " votes";
        }
    }
} else {
    echo "Please login first!";
}
} elseif ($action == 'vote_down'){
if (isset($_SESSION['loggedin'])) {
    $result = mysql_query("SELECT * FROM user2pics WHERE picid = $id AND userid = $user");
    if (mysql_num_rows($result) == 0) {
        $votes_down = $cur_votes[1] + 1;
        $resultaat = mysql_query("UPDATE pics SET dislikes = $votes_down WHERE picid = $id");
        if ($resultaat) {
            $query = mysql_query("INSERT INTO user2pics (picid, userid, vote) VALUES ($id, $user, 0)");
            if ($query) {
                $effectiveVote = getEffectiveVotes($id);
                echo $effectiveVote . " votes";
            } elseif (!$query) {
                echo "Failed to dislike!";
            }
        } elseif (!$resultaat) {
            echo "Failed insert in pics!";
        }
    } else {
        $row = mysql_fetch_array($result);
        if ($row['vote'] == 1) {
            $votes_down = $cur_votes[1] + 1;
            $result = mysql_query("UPDATE pics SET dislikes = $votes_down WHERE picid = $id");
            if ($result) {
                $resultaat = $mysqli -> prepare("UPDATE user2pics SET vote = 0 WHERE picid = $id AND userid = $user");
                $resultaat -> execute();
                $effectiveVote = getEffectiveVotes($id);
                if ($resultaat -> affected_rows == 1) {
                    echo $effectiveVote . " votes";
                }
            }
        } else {
            $effectiveVote = getEffectiveVotes($id);
            echo $effectiveVote . " votes";
        }
    }
} else {
    echo "Please login first!";
}
}

$cur_votes is defined as: $cur_votes = getAllVotes($id);

function getAllVotes($id) {
$votes = array();
$q = "SELECT * FROM pics WHERE picid = $id";
$r = mysql_query($q);
if (mysql_num_rows($r) == 1)//id found in the table
{
    $row = mysql_fetch_assoc($r);
    $votes[0] = $row['likes'];
    $votes[1] = $row['dislikes'];
}
return $votes;
}

function getEffectiveVotes($id) {
/**
 Returns an integer
 **/
$votes = getAllVotes($id);
$effectiveVote = $votes[0] - $votes[1];
return $effectiveVote;
}

You should not be mixing `mysql_*` and `mysqli_*` functions. Just use `mysqli_*` and bound variables in the prepared statements. — jeroen, May 16 '13 at 13:28
@andrewsi `$cur_votes` is defined as `$cur_votes = getAllVotes($id); function getAllVotes($id) { $votes = array(); $q = "SELECT * FROM pics WHERE picid = $id"; $r = mysql_query($q); if (mysql_num_rows($r) == 1)//id found in the table { $row = mysql_fetch_assoc($r); $votes[0] = $row['likes']; $votes[1] = $row['dislikes']; } return $votes; }` — Jef, May 16 '13 at 13:38
On a side note, you should really comment your code, it makes it much easier to maintain. — Glitch Desire, May 16 '13 at 13:41

Glitch Desire · Accepted Answer · 2013-05-16T13:40:12.750

3

You're duplicating functionality by storing 'likes' in two places.

I didn't look at your weak entity (table for users and votes) so let's assume it will have three fields: user_id, item_id and vote TINYINT. Primary key on user_id and item_id so the same user can only have one vote per item.

Set vote to 1 or -1 depending on up or down, instead of storing likes in the item table, calculate total vote for an item dynamically like this:

SELECT SUM(vote) FROM user_votes WHERE item_id = ?;

If you only want positive votes, do this:

SELECT SUM(vote) FROM user_votes WHERE item_id = ? AND vote = 1;

When the user wants to record or change his vote, you can use REPLACE INTO syntax (thanks to Anigel for the suggestion -- I totally missed it) in order to store the user's new vote:

REPLACE INTO user_votes (user_id, item_id, vote) VALUES (?, ?, ?);

edited May 16 '13 at 13:40

answered May 16 '13 at 13:28

Glitch Desire

14,632
7
43
55

2

+1 for a more robust solution. You could also just use `REPLACE INTO` instead of a select then insert/update if you had a primary key on the two columns (`user_id`,`item_id`) – Anigel May 16 '13 at 13:34
Thanks for your input, I already have this kind of table called `user2pics`, I'm storing likes as 1 and dislikes as 0. I will be looking into the sum function as it seems much easier than what I am doing now. Thank you! – Jef May 16 '13 at 13:35
1

Anigel, totally didn't think of `REPLACE INTO`, will modify my suggestion. – Glitch Desire May 16 '13 at 13:38
@Oshawott I've been trying to implement the sum function: `$q = "SELECT SUM(vote) AS numvotes FROM user2pics WHERE picid = $id"; $rij = mysql_fetch_assoc($q); $sum = $rij['numvotes']; return $sum;` But I keep getting the following error(on this line: `$rij = mysql_fetch_assoc($q); `) : `mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource` what am I doing wrong? – Jef May 16 '13 at 14:20
1

Check if there's an issue with the SQL syntax (output MySQL error) and if so let me know so I can revise the answer -- I didn't get to check it. Also, you probably want to use `mysqli` instead of the deprecated `mysql` functions. – Glitch Desire May 16 '13 at 14:22
@Oshawott finally got it to work! Thank you so much, I was almost at the point of rage quitting and then it worked :)! – Jef May 16 '13 at 14:59
@Jef - What was the issue so I can revise? – Glitch Desire May 16 '13 at 15:00
1

@Oshawott The problem was me trying to store the likes in different locations. I followed up your tip on the sum function and replaced all the deprecated mysql functions. I am not familiar with mysqli, so I gave only the query as a parameter and forgot to add the mysqli link, which was described [here](http://uk3.php.net/mysqli_query) – Jef May 16 '13 at 15:11
@Anigel Thanks a lot for the `REPLACE INTO` suggestion, it replaced a lot of code with just 1 simple line. – Jef May 16 '13 at 15:22
1

@Jef You are welcome, Glad it was useful. Just make sure your primary key is over the two columns of user_id and item_id otherwise it will only let one user have one vote in total on all items which is not what you want. – Anigel May 16 '13 at 15:28

Allow users to vote once in PHP

1 Answers1