auto generate number is not working at the start of new year

Question

I am trying to generate my bill no in following sequence in php L5- 0124-0001 (where L5 is company name, 01 representing month and 24 representing year, where as last four digit is for serial number) , I was using this code since October 2023, everything was working fine until end of year 2023, but as today year 2024 start, its stop working, (company name is correctly display, year month as well but last four digit are not incremented when new bill need to be generated. it remain 0001 for all the bills, where as it should L5-0124-0002 and so on ........ below is the code

$CI = "L5"; //Example only
$CIcnt = strlen($CI);
$offset = $CIcnt + 6;

// Get the current month and year as two-digit strings 
$month = date("m"); // e.g. 09 
$year = date("y"); // e.g. 23  

// Get the last bill number from the database 
$query = "SELECT patientno FROM iap2 ORDER BY patientno DESC LIMIT 1"; 
$result = mysqli_query($con,$query); 
// Use mysqli_fetch_assoc() to get an associative array of the fetched row 
$row = mysqli_fetch_assoc($result); 
// Use $row[‘patientno’] to get the last bill number 
$lastid = $row['patientno'];     

// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || (substr($lastid, $CIcnt + 1, 2) != $month) || (substr($lastid, $CIcnt + 3, 2) != $year)) { 
    // Start a new sequence with 0001 
    $number = "$CI-$month$year-0001"; 
} else { 
    // Increment the last four digits by one 
    $idd = substr($lastid, $offset); // e.g. 0001 
    
    $id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002 
    $number = "$CI-$month$year-$id"; 
} 

Answer 1

Your code is severely flawed. The way the number is built, the alphabetical and the chronological orders are not the same. So when you fetch the "last" number, you get a number from December 2023, not from January 2024, because 1223 is greater (as a string) than 0124.

You could change the ORDER BY to fix it. But then the query would be unoptimized (MySQL would need to scan the entire table), and, more importantly, the code would still be flawed because it's sensitive to race condition. If 2 users try to create a bill at the same time, they may end up with the same number.

A correct way to generate numbers is to use a dedicated counter table. When you need to get a new number:

• Lock the table
• Retrieve the last used counter for the current year and month (and the company, in case you can have several in the same database)
• Increment it and update the table
• Unlock the table
• Use the counter to insert the new bill