Generating a random winner and displaying the odds of winning - am I doing this right?

Question

I'm running a contest on a website and I have 3215 entrants who are eligible for 5x Sony PSPs.

I believe the formula to count the odds is totalEntrants - prizes / prizes:

(3215-5)/5 = 642 so that's an odds of 642 to 1 of winning - is that right? ( I suck at math )

And in my table which contains 3215 rows in the database I would just select a random row like so?

SELECT * from entries
WHERE entries.won = 0
ORDER BY RAND()
LIMIT 1

Now I have one row, and I need to set the won column to 1 so the entrant can't win again, then run it again? This is my first time doing it so I just want confirmation on if I'm doing it correctly.

Answer 1

Why not just use LIMIT 5 to select the winners in one step?

Answer 2

I think you can't select and update in the same query. What I would do is to use your query to select a random winner and execute an update on the register whose ID was returned to change the field "won" to have value 1. Then you just repeat the process 4 times. :)

Answer 3

Winning odds are totalEntrants / prizes

There is no need to reduce the number of prizes. e.g., if we have 2 entrants and one prize the odds are 2/1, meaning each entrant has one of two chance to win (if we'd reduce the number of prizes it's be 1/1, meaning one to one chance - sure win...)

The query seems right:

• Same entrant won't get selected twice (assuming you have code that updates the 'won' field)
• User will be selected randomly by RANDOM generating different order each time querying

Answer 4

Use:

CREATE TEMPORARY TABLE won LIKE ENTRIES;

DECLARE numAwards INT DEFAULT 5;

WHILE numAwards > 0 DO

  INSERT INTO won
    (columns...)
    SELECT e.*
      FROM ENTRIES e
     WHERE e.userid NOT IN (SELECT w.userid FROM won)
  ORDER BY RAND()
     LIMIT 1;

  SET numAwards = numAwards - 1;

END WHILE;

You could also use:

   SELECT e.*
     FROM ENTRIES e
LEFT JOIN won w ON w.userid = e.userid
    WHERE w.userid IS NULL
  ORDER BY RAND()
     LIMIT 1;

...but there's no difference in performance - see: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

UPDATE: Using LIMIT 5 is not ideal because it doesn't ensure that a person will win only once, unless you assume that a person only entered once (unlikely).