0

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.

Andrew
  • 399
  • 6
  • 15
  • Can you add a data example (input & expected output)? – Norbert Jul 01 '17 at 23:41
  • @NorbertvanNobelen Thanks for the request, I've uploaded an image of what the data in the db looks like and a sample of what the data represents. They are just ID's of members. – Andrew Jul 02 '17 at 00:01
  • What's the algorithm you want to use for counting the votes to get a winner? What if there is no winner? – Shadow Jul 02 '17 at 00:23
  • @Shadow I'm not sure how to describe the algorithm best. If it is Majority Rule then the winner needs 51% so if there are 5 members in the group the winner is decided by 3 of the votes. If there is no winner then I plan on doing revotes because one of these conditions needs to be met. Then for Unanimous it needs to be 100% or 5 out of 5 and if that isn't met then there is a revote. Does that make since? – Andrew Jul 02 '17 at 00:34
  • If the rule is simple counting, then why do you want a php loop to count the votes? Sql language has a count() aggregate function... – Shadow Jul 02 '17 at 00:41
  • In the duplicate topic department is your group and the category is your vote on winner. – Shadow Jul 02 '17 at 01:03
  • @Shadow because each user that votes votes for multiple places. First through Fourth. It's not just did they vote but also who they voted for. So if each member votes differently I need to see that and compare who they voted for to the other votes in order to get a tally. I think I figured out another way of doing it anyway. I'll have to write multiple queries but it'll work. Thanks anyway. – Andrew Jul 02 '17 at 01:09
  • Oh didn't see your updated link. Thank you I didn't use those search terms so that never came up. Appreciate it. – Andrew Jul 02 '17 at 01:11
  • @Shadow that answer doesn't answer this question. Unless you can explain how it compares the actual data entered as the vote for each particular place, it doesn't seem to fit the needs I have. Keep in mind that vwinner would have an entry for each member by their ID and that entry may be different for each member so I need to know what they all entered for their selection as the winner and then go over those selections to derive the count total for each one. I'm going to try a long convoluted way, but it doesn't fit with your suggestion. – Andrew Jul 02 '17 at 01:40
  • Well, had you properly described what you wanted in your question, you could have a reason to complain. You asked about the winner only. When I explicitely asked you to describe your logic for voting, you only talked about the winner again. If you want to get the output you are looking for, you better change the voting data structure because you are loking at unpivoting transformation with the current design. If you change the design, then the duplicate topic applies completely. If uou ďo not, then you need to add unpivot to the mix. Which is the solution in the duplicate topic applied 5×. – Shadow Jul 02 '17 at 02:09
  • You're right, I came to that realization, and I apologize for the lack of clarity in my question. What I did was simply count the votes a member receives at the time the vote is cast in another column of their membership. That way as the votes are cast each membership collects their own tally, then I just run a query on each column and compare the number of votes to the number of members in the group to find my percentage. The way I was going about it was wrong is the problem. I appreciate your help, you did help by expanding my thinking on the structure itself. – Andrew Jul 02 '17 at 02:22
  • "Majority" does not mean "51%." It means "more than 50%." – phoog Feb 06 '21 at 20:08
  • @phoog Necro with useless additions much? – Andrew Feb 07 '21 at 01:07
  • It's not useless. I've had more than one occasion to fix bugs in business critical software that arose because "majority" was calculated by comparison to 51, in one case because the calculation was specified incorrectly and in another case because the developer incorrectly applied a correct specification. – phoog Feb 07 '21 at 01:47

0 Answers0