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, 09: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-29-2011, 11:02 PM #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.
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.OracleGuy
04-29-2011, 11:21 PM #3I 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);
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 ...
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
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.