04-29-2011, 10:13 PM
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
I don't think you can do all of this in just one query.
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.
04-30-2011, 12:21 AM
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:
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:
$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.
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
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.