Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Lottery System
04-29-2011, 10:13 PM #1
- Join Date
- Jun 2010
- Thanked 0 Times in 0 Posts
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?
$drawquery = mysql_query("SELECT * FROM weeklydrawing WHERE `drawing_ticket`>=1 AND status='active' ORDER BY rand() LIMIT 50");
04-30-2011, 12:02 AM #2
- Join Date
- Jun 2002
- Thanked 328 Times in 324 Posts
I don't think you can do all of this in just one query.
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.Code:SELECT SUM (drawing_tickets) as total_tickets FROM weeklydrawing WHERE status='active'
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.
04-30-2011, 12:21 AM #3
Why?I thought of just making 1 row for each drawing ticket entered, but this would be a lot of querying.
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:
In other words you will have one number in that table for each possible number of tickets that a user might buy.Code:CREATE TABLE TicketNumbers( ticket INT ); INSERT INTO TicketNumbes VALUES(1),(2),(3),(4),....(19),(20);
So say they indeed want to buy 8.
Your query becomes:
How it works: You can try it yourself.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 ...
Just doYou will indeed get 8 records. Each with a different number from 1 through 8.Code:SELECT ticket FROM TicketNumbers WHERE ticket <= 8
So the INSERT INTO query there will end up, all in a SINGLE query, doing the equivalent of
I don't see any reason to have any more than those 3 fields in the DrawingTickets table.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' );
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.