I'm working on a voting system within my application. Each member joins a group and that group can then select a winner from the available group members. The voting part works fine, but now I need to compare the votes entered by each member to get a percentage or to find out if the vote was unanimous so I can then designate winners from that information. I have a groups table.
|groupID|winner|first|second|third|
|3445553|......|.....|......|.....|
And I have a membership table.
|memberID|vwinner|vfirst|vsecond|vthird|
|12334334|.......|......|.......|......|
In my test group I have 5 members. I've successfully simulated them voting which has populated these fields in the database. The value that goes into the field is the ID of the member the other member voted for.
Now I need to query the database and check each members vote and compare them to the others to either get a count of all the same votes or to find out if the vote was unanimous. The voting process can either be Majority Rules, so the winner needs 51% of the vote, or Unanimous, so the winner needs 100% of the votes. The basic function that is escaping me is how to write a loop whether it be foreach or while to go over the array of data I'm getting from this query
$query="SELECT vwinner, vfirst, vsecond, vthird, vfourth FROM membership WHERE groupID = :groupID";
$stmt=$this->db->prepare($query);
$params = array(':groupID' => $groupID);
$stmt->execute($params);
$votes = $stmt->fetchALL();
And comparing the values. Ultimately I need it to return the ID of the member who got 51% of the vote and another that simply checks that all the votes were the same for a unanimous vote. Below is a sample of what I have started so far.
public function countVotes($groupID,$payout)
{
$query = "SELECT teams FROM groups WHERE groupID = :groupID";
$stmt=$this->db->prepare($query);
$params = array(':groupID' => $groupID);
$stmt->execute($params);
$result = $stmt->fetch();
$teamnumber = $result->teams;
$query = "SELECT voted FROM membership WHERE groupID = :groupID && voted=1";
$stmt=$this->db->prepare($query);
$params = array(':groupID' => $groupID);
$stmt->execute($params);
$results = $stmt->rowCount();
if($teamnumber == $results)
{
if($payout == 'Majority Rule')
{
$query="SELECT vwinner, vfirst, vsecond, vthird, vfourth FROM membership WHERE groupID = :groupID";
$stmt=$this->db->prepare($query);
$params = array(':groupID' => $groupID);
$stmt->execute($params);
$votes = $stmt->fetchALL();
foreach($votes as $vote)
{
$t1wv = $vote->vwinner;
}
}
$query = "UPDATE groups
SET voting='0'
WHERE groupID = :groupID";
$stmt=$this->db->prepare($query);
$params = array(':groupID' => $groupID);
$stmt->execute($params);
return true;
}
else
{
return false;
}
}
It checks the groups table to get the number of teams then compares that to the number of teams that have voted, if they are equal then it runs the query that compares the votes. That foreach loop I know won't work I just started writing it to see if I could get my head around this problem. The Update query below it simply turns off voting once the count has been done. There will be more conditionals before that runs but it was a part of one of my initial tests.
I've done some searching here but I can't seem to find anything similar. This query is going to return a lot of data and I just can't seem to get an idea of how to start parsing it properly.
I could easily write a bunch of individual queries but that would be inefficient of course. Any ideas would be greatly appreciated. Thanks in advance.
As requested DATA EXAMPLE:
The type of date that goes into the tables is and ID that would resemble this
42b39d4c6d3f512c90cd2726da822117
Heres a look at the table with some data in it as votes.
![Database snippet]https://www.dropbox.com/s/7t8wnqc13vsbcwn/Untitled.png?dl=0
Sample result needed:
|Place |memberID|votes|
|vwinner|memberID| 2 |
|vwinner|memberID| 3 |
|vfirst |memberID| 3 |
|vfirst |memberID| 1 |
|vfirst |memberID| 1 |
|vsecond|memberID| 2 |
|vsecond|memberID| 3 |
|vthird |memberID| 4 |
|vthird |memberID| 1 |
|vfourth|memberID| 3 |
|vfourth|memberID| 2 |
So if a member got a vote for a place I need to know how many votes that member got and for what place.