Untitled

Sunday January 12th 2003 05:30 PM PST

i have a little job to do this weekend. It's a HUGE sweepstakes project where i have to build a script that will properly pick the winners from a HUGE database. SO the question i've been asking is HOW did i agree to doing this project for close to nothing? I'm not really pissed about the money, i'm just glad that i've ended up learning A WHOLE SHITLOAD about mysql, sql, shel scripting and php. I've spent way too much time on this project, but i just made a breakthrough. Originally my approach was to build a "lookup" table that would contain all the users and their number of entries. Then from that i could get the total number of entries, and pick a random number. Then with the lookup table i could see which user belonged to that "entry". Well let's just say that this is the pure brut force method of doing this. There ends up being about 24,000 registered users and about 33,000 records of entries. These two tables end up making a total of 28,382,750 entries. So the brut force method starts to fall apart. So i've been thinking about the math solution for the problem all weekend. Last night as a few guests arrived, i stumbled across a simple little SQL option called SUM. Well i have learned mySQL from just using is and by the guidance of Jason, but i had never seen this before. I have to say that i feel stupid admiting that, but it's true. So this little command would allow me to total the number of entires a user has just by saying

SELECT SUM(Entires) FROM 'EntryDatabase' WHERE UserName = 'riggs'

OMFG! I was on cloud9. So now i could take a needless repeat loop out of a HUGE script that i was building to make the lookup table. (just a note: i think i just lost all the non-geeks) SO now my brut force method would be a little faster. Now i can make the lookup table in just under 2 hours and then i'll be ready to pick the winners. The table has been building for just over an hour now. And i've just come to the conclusion that i was STUPID. Not only do i not have to build the lookup table, i can find everthing i need to know with a very very very simple set of functions. Like above i can do a SUM on the entries, but this time use a slight variation.

SELECT SUM(Entires) FROM 'EntryDatabase' WHERE UserNumber < 12000

This would end up telling me how many entries were submitted by users 1 through 11999. So if you've read this far you might as well stick around for the ending. So there are 28 million entries in the sweepstakes. I need to pick a winner. So pick a number between 1 and 28 million. then start calling the statement above with different numbers of users. Lets say your random number pick was 12million. You'd then call:

SELECT SUM(Entires) FROM 'EntryDatabase' WHERE UserNumber < 12000

if it was HIGHER than 12million you'd pick a number lower than 12000, lets say cut it in half. so SELECT SUM(Entires) FROM 'EntryDatabase' WHERE UserNumber < 6000

and so on and so on.

What you find in the end is that you can determine the winner in under 25 calls EVERY time. That's because 2 to the 24 is just over 16million and 2 to 25 is over 33Million. You can divide 28 million in a half only 24 times before you get below a whole number. Ok, enough geek for today. I just had to write it cause Jen was getting sick of hearing it.

Comments:

If you just want a random entry why not just call: SELECT * FROM ENTRIES ORDER BY RAND LIMIT 1

Posted by: Jason D-