Dividing numbers between rows in MySQL + PHP
- by André Figueira
Hi I am working on a kind of raffle system which divides 1 million random numbers into an x amount of tickets, e.g. 1 million random numbers to 10,000 tickets.
Each ticket is a row in a database, we then have another table ticket numbers in which i need to give 100 numbers to each ticket they are related by the ticket id.
So at the moment this is my code:
//Amount of the 1 million tickets divided to the tickets
$numbersPerTickets = $_POST['numbersPerTicket'];
//The total cost of the property
$propertyPrice = $_POST['propertyPrice'];
//The total amount of tickets
$totalTickets = NUMBER_CIELING / $numbersPerTickets;
//The ticket price
$ticketPrice = $propertyPrice / $totalTickets;
//Generate array with random numbers up to 999,999
$randomTicketNumbers = createTicketNumbers();
//Creation loop counter
$ticketCreationCount = 1;
//Loop and create each ticket
while($ticketCreationCount <= $totalTickets)
{
//Create a padded ticket number
$ticketNumber = str_pad($ticketCreationCount, 6, 0, STR_PAD_LEFT);
$query = '
INSERT INTO tickets(
propertyID,
ticketNumber,
price
)
VALUES(
"'.$propertyID.'",
"'.$ticketNumber.'",
"'.$ticketPrice.'"
)
';
$db->query($query);
//Get the ID of the inserted ticket to use to insert the ticket numbers
$ticketID = $db->insert_id;
$loopBreak = $numbersPerTickets;
$addedNumberCount = 1;
foreach($randomTicketNumbers as $key => $value)
{
$query = '
INSERT INTO ticketNumbers(
ticketID,
number
)
VALUES(
"'.$ticketID.'",
"'.$value.'"
)
';
$db->query($query);
unset($randomTicketNumbers[$key]);
if($addedNumberCount == $loopBreak){
break;
}else{
$addedNumberCount++;
}
}
$ticketCreationCount++;
}
But this isn't working it adds the right amount of tickets, which in the case for testing is 10,000 but then adds far too many ticket numbers, it ends up exceeding the million numbers in the random tickets array, The random tickets array is just a simple 1 tier array with 1 million numbers sorted randomly.
