Php lottery issues multi winner problems

Question

I have created a lottery script in php. My problem is now selecting more then one winner. Because it is possible for players to have the same number on their tickets. Here I am supplying the two table structures and the source code.

lotto_game {
 id(int)
 jackpot(int)
 status(varchar10)
 pick_1(int)
 pick_2(int)
 pick_3(int)
 pick_4(int)
 pick_5(int)
 tickets_sold(int)
 winner(text)
}

 lotto_picks {
 lotto_id(int)
 user_id(int)
 choice_1(int)
 choice_2(int)
 choice_3(int)
 choice_4(int)
 choice_5(int)
 ticket_status(int)
}

These are my two tables with in my database. For examples sake we will create 2 users with the id's 1, and 2. So what happens is when the script runs it is suppose to change the lotto_game status from 'active' to 'finished' then add the random lottery numbers into each pick_* column.

$one = rand(1,30);
$two = rand(1,30); 
$three = rand(1,30);
$four = rand(1,30);
$five = rand(1,30);

mysql_query("UPDATE `lotto_game` SET 
 pick_1 = '$one',
 pick_2 = '$two',
 pick_3 = '$three',
 pick_4 = '$four',
 pick_5 = '$five',
 status = 'finished'

WHERE status = 'active'");

That wasn't too hard I will admit. But this is just the beginning of the end.

$lotto['tickets'] = mysql_query("SELECT ticket_id FROM `lotto_picks` WHERE ticket_status='valid'");

@$lotto[winners] = mysql_query("SELECT ticket_id,user_id FROM `lotto_picks` WHERE choice_1 = '$one' AND choice_2 = '$two' AND choice_3 = '$three' AND choice_4 = '$four' AND choice_5 = '$five'");

$lotto['num_tickets'] = mysql_num_rows($lotto['tickets']);
@$lotto[winner_id] = mysql_fetch_array(@$lotto[winners]);
$lotto['jackpot'] = mysql_query("SELECT jackpot FROM `lotto_game` WHERE status='active'");

$lotto['winner_jackpot'] = mysql_fetch_array($lotto['jackpot']);
$lotto['num_winners'] = mysql_num_rows($lotto['winners']);
//echo @$lotto['num_tickets'];
//echo @$lotto['num_winners'];
$winner = $lotto['num_winners'];
//echo @$lotto['winner_id']['user_id'];
$jackpot = $lotto['winner_jackpot']['jackpot'];
$id = @$lotto[winner_id][user_id];
if ($winner == 1) {
    mysql_query("UPDATE `character` SET
    decivers = decivers +'$jackpot'
WHERE user_id='$id'");
}

This is what I have come up with and it really seems to work with one winner. But I just cant figure out where to go from here. I have tried using some arrays but nothing works. I know what needs to be done but can't figure out how to do it.

When I search for winners I need to put into an array all their user id's.

so extra decivers is money, if anyone is confused on that. The status on the tickets doesn't really matter here but if you must know it just determines if the ticket_status is 'valid' or 'invalid'

Answer 1

$winners_array = array();
if(mysql_num_rows($lotto['winners'])!=0){
  while($row =mysql_fetch_array($lotto['winners'])){
    if(!in_array($row['user_id'],$winners)) $winners[] = $row['user_id'];
  }
}

$winners will be an array with all the winners user_ids

Answer 2

i think you've chosen the wrong storage formats for your picked numbers. The standard approach is to use binary values which have N-th bit set if the number N is choosen.

Consider this example: user chooses numbers "2 4 5 9 11". Setting corresponding bits to 1 gives '10100011010' which is decimal 1306. Now the lottery picks "4 7 9 12 13" which is '1100101001000' == 6472. Perform a bitwise AND on both values and count the number of bits set in the result:

SELECT BIT_COUNT(1306 & 6472)

this immediately tells us that the user has 2 correct picks. Just as easy you can select "full" winners:

SELECT * FROM tickets WHERE BIT_COUNT(tickets.pick & lotto.pick) = 5

or sort the tickets by the number of correct picks

SELECT * FROM tickets ORDER BY BIT_COUNT(tickets.pick & lotto.pick) DESC