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

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.