help choosing a random winner from rows with differing values

schwim

Hi there everyone,

I'm writing a lottery script right now and have written the entry system to insert a row with the user's id and the number of "tickets" they purchased. so user id 1 would have a row with a value of 100 and user id 2 would have a value of 1000. How might I come up with a random winner that would take into account that user id 2 should have 10 times the chance to win than user id 1?

Your thoughts on any solutions would be greatly appreciated!

thanks,
json

NogDog

One way would be to create a temporary DB table with one row per user per "ticket". So if Bob bought 3 tickets, he'd get 3 rows, and if Jane bought 10 tickets she'd get 10 rows. Then get the total row count, use PHP to generate a random number between 0 and total-1, then "select * from temp_table offset $the_random_number limit 1".

schwim

Hi there nog and thanks very much for the reply. If I'm creating those rows to handle the winner, should I just create them from the beginning and handle it that way? What I mean is instead of having an entry with a user id and 100, have 100 entries for that user_id?

NogDog

schwim;11011237 wrote:
Hi there nog and thanks very much for the reply. If I'm creating those rows to handle the winner, should I just create them from the beginning and handle it that way? What I mean is instead of having an entry with a user id and 100, have 100 entries for that user_id?

That's probably what I would do, pending someone coming along here with a nifty mathematical solution that would not require that. 🙂

schwim

If I handle it this way, how would I create the loop to insert the correct number of rows? For instance, if isomeone purchases 100 tickets, how would I create the insert loop?

johanafm

Since your table would hold an autoincrement PK and the user_id, loop ticket count times over INSERT INTO tickets(user_id) VALUES ($user_id)

laserlight

If you don't want to use a temporary table, an alternative would be to select the rows along with the sum of the number of tickets purchased (with the rows ordered in some arbitrary but consistent way). Then, generate a random integer in the range [0, sum of the number of tickets purchased). Then, iterate over the rows retrieved, adding the number of tickets purchased for that row to a cumulative sum, until the sum exceeds the random integer. At that point, the current row's corresponding user is the random winner.

bradgrafelman

Looks like I'm late to the party... I had been formulating and idea that pretty much matches laserlight's suggestion above. I was thinking that you could even turn it into a stored procedure, and that the "arbitrary but consistent way" of ordering that laserlight mentioned above could simply be the timestamp of the purchase (which could be automatically added via TIMESTAMP column with 'DEFAULT CURRENT_TIMESTAMP').