3

I've created a script where users can get points by completing tasks and then the more points they get the higher chance they have of winning the prize. It works like a raffle ticket competition.

My problem is generating winners.

I can choose a random winner with a code like this:

    function selectWinners($count){
    $select = mysql_query("SELECT first_name from `users` ORDER BY RAND() LIMIT {$count}");
    $results = mysql_fetch_assoc($select);
    echo "Winner is {$results[first_name]}";
    }

But this is just completely random and doesn't matter at all as to how many points the users have.

I believe I've got to create a temporary table that shows the users ID for every point they have, then use a similar script to select a random ID?

I'm not entire sure, just a guess, I also don't have a clue how that would be coded.

Thanks

Daryl
  • 45
  • 1
  • 6

3 Answers3

9

What you're asking is really more a statistics question than a programming one.

You want each user to have a chance to win proportional to the number of entries they made ("points"). In the real world, this is the equivalent of putting each user's name into a hat once for each point they get, then drawing one name at random. The single drawing names the winner.

Programmatically, you do this by choosing a random number between zero and the total number of points awarded across all users minus one (you could, if you wish, start at one. But computers use zero and that's generally easier). Then you iterate through the users, adding each one's point total to a running sum. When the running count exceeds the random number you've chosen, the user you're currently on has won.

Imagine you have three entrants, named Joe, Bob, and Alice:

Name  Points
------------
Joe   3
Bob   8
Alice 2

It doesn't matter what order the names are in, the probabilities will work anyhow.

You select a random number between zero and 12 inclusive (12=3+8+2-1). On a 0-2, Joe wins (2=3-1). On a 3-10, Bob wins (10=8+3-1). On an 11-12, Alice wins (12=3+8+2-1). Note that the number of values included in each range is equal to the number of points - thus, the odds of a given individual being selected are (individual's points / total points), the logic you desire.

The way you check the winner is as I said above - start a sum at zero, then add 3. If 3 > randomvalue, Joe wins. Then add 8 to that. If the new 11 is greater than the random value, Bob wins. Then add 2. Now the number is greater than your random value (since 13 is higher than the max you could generate).

This could, in theory, be done in a database - but it's not really an appropriate task there. This is application logic, and I'd recommend doing it in PHP (the SQL would just be a basic SELECT of all the users, their points, and perhaps the point total).

Borealid
  • 95,191
  • 9
  • 106
  • 122
  • Is there a name for the mathematical method or algorithm you're using here? I'd like to read more about it. – mister martin May 15 '17 at 04:36
  • I'm not sure there's a mathematical name for a raffle. Each user has a number of tickets, and we choose a certain ticket to be the winner. – Borealid May 15 '17 at 20:20
  • Okay, but it appears as though you're using a specific formula here, no? Or perhaps the math is just over my head. – mister martin May 15 '17 at 20:59
  • I rigorously described choosing a random number between 0 and N-1, and then the winner being the user who held the ticket with that number. – Borealid May 16 '17 at 04:15
  • Yes you did, and I apologize for being slow. It just confused (and fascinated) me a bit how the entrants can be listed in any order and the odds of winning remain the same. Felt like a riddle trying to wrap my head around it, but I see now that I've just been over-thinking it. It makes perfect sense when I sit back and let it sink in :P Thanks for your patience, and the great answer. – mister martin May 16 '17 at 05:10
2

Create a temporary table with one row for each member for each point. Then select a random row.

Here is a stored procedure that I just created for a raffle on my site. The code is culled from different posts on the web, and mysql reference:

CREATE PROCEDURE  `getWinner` ()
  BEGIN


 -- Declare '_val' variables to read in each record from the cursor
DECLARE memberID_val INT DEFAULT 0;
DECLARE points_val INT DEFAULT 0;

-- Declare variables used just for cursor and loop control
DECLARE done INT DEFAULT 0;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;

-- Declare the cursor
DECLARE member_cur CURSOR FOR
      SELECT  id, points FROM member;

-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TEMPORARY TABLE IF EXISTS `raffle_table`;
CREATE TEMPORARY TABLE  `raffle_table` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `memberID` int(11) DEFAULT '0'  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

OPEN member_cur;

REPEAT

  FETCH  member_cur  INTO   memberID_val,  points_val;

  IF NOT done THEN

   WHILE points_val > 0 DO

      INSERT INTO raffle_table (memberID) values (memberID_val);
      SET points_val = points_val - 1;

   END WHILE;

  END IF;

UNTIL done END REPEAT;
CLOSE member_cur;


SELECT memberID FROM `raffle_table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `raffle_table` ) ORDER BY id LIMIT 1;

END $$
hgolov
  • 650
  • 1
  • 11
  • 28
1

Users and their points can be shown in a 2D array, like this (sorted by user points):

    P %.......
    o %.......
    i %%......
    n %%%.....
    t %%%%%...
    s %%%%%%%%
Users 0123456789

You try to select a random winner by creating a random 2d coordinate: (random user id, random user point between 0 and maximum user point), and checking if you've 'hit' a point. Very pseudo code:

$maxpoint = SELECT MAX(points) FROM users;

do{
  $random_user = see for example http://www.greggdev.com/web/articles.php?id=6
  $random_point = rand( 0, $maxpoints );
}while( $random_user->point >= $random_point ); // beware users with 0 point
biziclop
  • 14,466
  • 3
  • 49
  • 65