PHP/MYSQL only allowing one vote per member?

Question

ive been giving the task at work of setting up an awards voting system, I dont know too much about php and mysql. But i know more about this than anyone else here, and my boss in on holiday. But I've been reusing the code, that had previously been left on our system and adapting it for this year.

Basically the voting system works fine, and I've set up new tables in mysql to capture the data. I've found one fairly large flaw in the existing code though and am not sure how to modify it. Basically the code allows people to vote as many times as they want at the moment. I want to restrict it to only 1 vote per member, to keep things fair.

So at the moment, members log in with a membership number, then vote. The votes are stored in the mysql tables, and i can then add up the votes by querying the data.

I was hoping someone can help me in adding a line or two of code, that will simply check to see if a member has already voted. When a member votes, their member no. is stored in the sql tables along with their votes selections. So maybe the best way is to see if a memeberid already exists in the table, and if it does, tell the user that they have already voted - or words to that effect.

<?php
//Insert into volunteer awards
$coach=mysql_real_escape_string($_SESSION['coach']);
$official=mysql_real_escape_string($_SESSION['official']);
$young_volunteer=mysql_real_escape_string($_SESSION['young_volunteer']);
$volunteer=mysql_real_escape_string($_SESSION['volunteer']);

$memberid=$_SESSION['MM_Username'];
$association=$_SESSION['MM_Association'];
$region=$_SESSION['Region'];


$sql_query = mysql_query("INSERT INTO awards_2009_votes (`id`, `member_id`, `region`, `coach`, `official`, `volunteer`, `young_volunteer`) VALUES ('', '$memberid', '$region', '$coach', '$official', '$volunteer', '$young_volunteer')") or die (mysql_error());
?>

Thanks

Answer 1

I'm not sure from your code what your table structure is, but the following table structure would limit user voting to one vote per topic:

Users table

user_id int unsigned not null auto_increment,
username varchar
// and other user info fields

Topics table

topic_id int unsigned not null auto_increment,
topic_title varchar
// and other topic info fields

Voting table

user_id,
topic_id,
vote_value,
primary key (user_id,topic_id) //this is the constraint that will allow only one vote

Answer 2

You can add a UNIQUE constraint to your table. This is a one-time operation - you don't need to do this every time your script runs, it is a change to table structure. Run this in your MySQL administration tool (e.g. phpMyAdmin, Navicat, HeidiSQL, what-have-you):

ALTER TABLE awards_2009_votes ADD UNIQUE (member_id);

After this change, it will not be possible to add a second vote with the same member ID - the INSERT (or UPDATE) will fail.

The advantage here is that the check is done automatically in the database, so you don't have to worry about 1) checking for duplicates with your code or 2) people adding multiple votes manually.

---

As @middaparka says, you should use INSERT IGNORE to avoid a "duplicate key" error:

$sql_query = mysql_query("INSERT IGNORE INTO awards_2009_votes (`member_id`, `region`, `coach`, `official`, `volunteer`, `young_volunteer`) VALUES ('$memberid', '$region', '$coach', '$official', '$volunteer', '$young_volunteer')") or die (mysql_error());
if (mysql_insert_id()) {
    // row was inserted - vote added
} else {
    // row was not inserted - already voted
}

Answer 3

You can write few lines as follows , althouth I did not checked it but those should work...

$result=mysql_query("select count(*) count from awards_2009_votes where member_id='$memberid'");

$has_voted_array=mysql_fetch_array($result);
if($has_voted['count']!=0)
echo "You have already registered youur vote";
else
{
//do normal operation
$sql_query = mysql_query("INSERT INTO awards_2009_votes (id, member_id, region, coach, official, volunteer, young_volunteer) VALUES ('', '$memberid', '$region', '$coach', '$official', '$volunteer', '$young_volunteer')") or die (mysql_error());
}

Answer 4

Here's a quick and dirty approach:

$sql_query = "SELECT FROM awards_2009_votes WHERE member_id = '$memberid'";
$sql_result = mysql_query($sql_query);
$num_rows = mysql_num_rows($sql_result);

if ($num_rows > 0) {
    // this member has already voted
} else {
    // carry on
}

As Piskvor pointed out, though, this solution has (at least) two limitations:

1. It is confined to the method containing it, so it is not preventing multiple votes in general -- only through this particular method. (You could write a function to contain this same check, but you'd still have to call that function everywhere a user tries to vote.)
2. It results in additional strain on the database, which may be unacceptable in a high-traffic scenario.

With these points in mind, my recommendation would be to first run a script to check for any occurrence of duplicate member_id values in your votes table, remove all but one in each case, and THEN add the UNIQUE constraint to your table. From there you can be sure your table will never have more than one row with the same member_id.