Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Lottery System

  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    163
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Lottery System

    I'm working on a Lottery system.

    The user uses their Drawing Tickets for a chance to win. The way I currently have this setup, is the user can type in 8 Drawing tickets for example, and this will be placed into a row. So it would be like:

    id -- userid -- username -- drawing_ticket -- status -- date

    1 -- 12 -- PapaGeorgio -- 8 -- active -- 2011-04-28


    So this row will have 8 chances to be selected as the winner. I thought of just making 1 row for each drawing ticket entered, but this would be a lot of querying.

    What method may I use to do this?

    PHP Code:
    $drawquery mysql_query("SELECT * FROM weeklydrawing WHERE `drawing_ticket`>=1 AND status='active' ORDER BY rand() LIMIT 50"); 

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    I don't think you can do all of this in just one query.

    Start with:
    Code:
    SELECT SUM (drawing_tickets) as total_tickets FROM weeklydrawing WHERE status='active'
    Then use the total ticket count and get a random number between 1 and total ticket. That will be the winning ticket number. You have to then go through all the active rows and find where that number lands. So if the first row has a weight of 8, it would count as tickets 1-8. The easiest way to do this is probably outside of MySQL and do it in whatever language you are using. It would be pretty easy to figure out which row has the winning ticket by iterating over the recordset.

    The other way you could design it would be to have each row contain the starting ticket number and the ending ticket number. That would make it easier to determine where the winner is.

    Some other remarks:
    Unless status is an enum, I wouldn't use an arbitrary text field for something like status. It will use up a lot more space than a number would. And why are you repeating their username in this table if you are already recording their user id? That is needlessly duplicating data.
    OracleGuy

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    I thought of just making 1 row for each drawing ticket entered, but this would be a lot of querying.
    Why?

    You mean a lot of INSERTs??

    There's an easy way around that, if you add one simple little auxilliary table.

    Figure out what the maximum number of tickets is that you will allow each person to buy.

    Say it is 20.

    So create a simple little table that looks like this:
    Code:
    CREATE TABLE TicketNumbers( ticket INT );
    
    INSERT INTO TicketNumbes VALUES(1),(2),(3),(4),....(19),(20);
    In other words you will have one number in that table for each possible number of tickets that a user might buy.

    So say they indeed want to buy 8.

    Your query becomes:
    Code:
    $userid = ... from session or form ...
    $qty = $_POST["quantity"];
    $sql = "INSERT INTO DrawingTickets (userid,ticket,ticketDate) "
         . " SELECT $userid, ticket, CURDATE() FROM TicketNumbers WHERE ticket <= $qty ";
    ... execute that ...
    How it works: You can try it yourself.

    Just do
    Code:
        SELECT ticket FROM TicketNumbers WHERE ticket <= 8
    You will indeed get 8 records. Each with a different number from 1 through 8.

    So the INSERT INTO query there will end up, all in a SINGLE query, doing the equivalent of
    Code:
    INSERT INTO DrawingTickets VALUES( 371, 1, '2011-4-29' );
    INSERT INTO DrawingTickets VALUES( 371, 2, '2011-4-29' );
    ...
    INSERT INTO DrawingTickets VALUES( 371,81, '2011-4-29' );
    I don't see any reason to have any more than those 3 fields in the DrawingTickets table.

    What's the point of the autonumber field? You'll never use it. And oracleguy already pointed out that the username in there is a mistake. And why an ACTIVE field? If there's a record in the table, it's active. If not, it's not there. Even the ticketDate field may not really be needed. But up to you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •