PHP/MYSQL仅允许每个成员一票? [英] PHP/MYSQL only allowing one vote per member?

问题描述

我一直在努力建立奖励投票系统，我对php和mysql不太了解.但是我比这里的其他人和我的老板在度假中了解的更多.但是我一直在重用以前留在我们系统上的代码，并在今年进行改编.

基本上，投票系统运行良好，我在mysql中设置了新表来捕获数据.我发现现有代码中存在一个相当大的缺陷，并且不确定如何修改它.基本上，该代码允许人们此刻进行任意多次投票.我想将其限制为每位成员只有1票，以保持公平.

因此，目前，会员使用会员编号登录，然后投票.投票存储在mysql表中，然后我可以通过查询数据来添加投票.

我希望有人可以帮助我添加一行或两行代码，这将仅检查成员是否已投票.当成员投票时，其成员编号为与它们的投票选择一起存储在sql表中.因此，也许最好的方法是查看表中是否已存在memeberid，如果存在，请告诉用户他们已经投票-或对此表示怀疑的话.

<?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());
?>

谢谢

解决方案

这是一种快速而肮脏的方法:

$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
}

不过，正如Piskvor所指出的那样，该解决方案(至少)有两个局限性:

1. 它仅限于包含它的方法，因此它一般不会阻止多次投票 －仅通过此特定方法. (您可以编写一个包含相同支票的函数，但仍然需要在用户尝试投票的任何地方调用该函数.)
2. 这会对数据库造成额外的压力，这在高流量的情况下可能是不可接受的.

考虑到这些要点，我的建议是首先运行一个脚本来检查您的票表中是否出现重复的member_id值，在每种情况下都删除一个，然后将UNIQUE约束添加到表中.从那里，您可以确保您的表永远不会有多条具有相同member_id的行.

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

解决方案

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.

这篇关于PHP/MYSQL仅允许每个成员一票?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！