I have a table code with the following table structure. id is auto incrementing id, the code is a random unique 15 digit code and customer_id is the customer's id.
id code customer_id
1 123...4 1
2 124...5 1
3 128...3 1
4 234...1 2
5 678...3 2
6 567...8 1
The code is actually a 15 digit number which I am generating randomly as per below code. Based on the user input for the number of codes I generate the codes at once, insert ignore into the table and count post addition to the table if few lines were ignored due to duplicates and add the additional ones needed to make the total count needed by the user. I use this approach because the request for the number of codes is usually around 50k to 100k and this approach works well.
do{
$codes = array();
$question_marks = array();
$sql = "SELECT count(*) FROM code";
$stmt = $this->db->prepare($sql);
$stmt->execute();
$initialCount = $stmt->fetchColumn();
for ($i=0;$i<$codesToGenerate;$i++){
$code = getToken(15);
array_push($codesArray, $code, $customerId);
$question_marks[] = '(' . placeholders('?', 2) . ')';
}
$datafields = "code, customer_id";
$this->db->beginTransaction(); // also helps speed up your inserts.
$sql = "INSERT IGNORE INTO code (" . $datafields . ") VALUES " . implode(',', $question_marks) ;
$stmt = $this->db->prepare($sql);
try {
$stmt->execute($codesArray);
}catch (\PDOException $e){
echo $e->getMessage();
$result = 0;
}
$this->db->commit();
$sql = "SELECT count(*) FROM code";
$stmt = $this->db->prepare($sql);
$stmt->execute();
$finalCount = $stmt->fetchColumn();
$rowsInserted = $finalCount - $initialCount;
$codesGenerated += $rowsInserted;
$codesToGenerate = $codesToGenerate - $rowsInserted;
}while($codesToGenerate>0);
I have added a column serial_number in table and I want to add a serial_number which will increment for each value of respective customer like below based on the customer_id. How do I change PHP code to add serial_number as well. Please note that I want to avoid holes in serial_number.
id code customer_id serial_number
1 123...4 1 1
2 124...5 1 2
3 128...3 1 3
4 234...1 2 1
5 678...3 2 2
6 567...8 1 4
How do I do this?
Below are some of my thoughts:
1)Should it be done seperately post this transaction?
2)Should I maintain a last count of serial_number for each customer in a separate table and then increment in PHP before adding to table.
3) How do I take care of concurrencies if I use the approach mentioned in 2 above
