Accounting Application Self-Generated SerialNumber is Duplicated in PHP

Question

Good Day! I am generating serialnumber in following format: yearmonth001 which increments after generating each time. I need unique serial entries as they are sent to customers. Serial number is generated on button click and I have done the same to avoid Duplicates. But still, duplicate of serial and formid are coming. Sometimes, one user's record having formid and serialnumber comes twice (not always on same time even if they press with time gaps still the same record is coming twice. Maybe, they are not refreshing browser) .Code I am attaching below works very fine but still shows formid and serialnumber as duplicates often but not always.

Below is the code where serialnumber is generated in format yearmonth0001 and increments - (on button click)

check_status.php

<?php 
        
            
include("connect.php");
        $query =  mysqli_query($link, "SELECT formid, serialnumber FROM accounting Where 1=1 order by id desc ");

        $row = mysqli_fetch_array($query);
        if ($row['formid'] != '') {
            $value = $row['formid'] + 1;
            $serial_number = $row["serialnumber"];
            
            $data["form_id"] = str_pad($value, 3, '0', STR_PAD_LEFT);
            
            $year = date("Y");
            $month = date("m");
            $number = substr($serial_number, 6) + 1;
            $number = str_pad($number,4,"0",STR_PAD_LEFT);
            $data["serial"] = "$year$month$number";
        } else {
            $data["form_id"] = str_pad(1, 3, '0', STR_PAD_LEFT);
            
            $year = date("Y");
            $month = date("m");
            $number = "0001";
            $data["serial"] = "$year$month$number";
        }
        
        print_r (json_encode($data));
        
        ?>

Likewise below is the script for formid to refresh every 2000 ms and select the same from check_status file

<script>
     var nextFormId;
     
    setInterval(function(){
        getFormData();
    }, 2000);
    
    function getFormData(){
        $.ajax('check_status.php').done(function(data) {
            const result = JSON.parse(data);
            
            nextFormId = result.form_id;
                        
            $("#refreshtb").val(nextFormId);
            })
    }
</script> 

Finally, below is the index.php file where PHP code to display formid and required HTML is presented

index.php

$query =  mysqli_query($link, "SELECT formid FROM accounting Where 1=1 order by id desc ");
        $row = mysqli_fetch_array($query);
        if ($row['formid'] != '') {
            $value = $row['formid'] + 1;
            $val = str_pad($value, 3, '0', STR_PAD_LEFT);
        } else {
            $val = str_pad(1, 3, '0', STR_PAD_LEFT);
        }

<div class="form-group">
                    <strong>Form No (رقم الاستمارة): </strong><strong><?php echo date("Y"); ?> / <input align="" type="text" id ="refreshtb" name="formid" value="<?php echo $val; ?>" readonly /></strong>
                </br />

        </div>

Application is running on same network by various employees from their own network PCs

Both formid and serialnumber come in duplicate more often but not always. Any help or support will be approcated. Thanks

Answer 1

That business logic is completely flawed, when having more than one client connected. It is not PHP duplicating ID, but it is you handing out duplicate ID. A common approach is to add the record when requesting the form already - then this ID can be sent along with the rest of the record and then be updated; another approach would be to consider other columns, when generating that synthetic key. Simultaneously generating ID on the client-side is definitely doomed to fail.

I mean, just take a piece of paper and draw the business logic ...step by step. This should make it pretty obvious how flawed the concept is and that it could never work in a reliable manner - if not the server-side would provide (or reserve) the IDs.