sql - How to create custom function for create reference number as primary in this 2021-0001 format in php

Question

I try to create the primary key value customizable increment format. I write PHP function to generate the primary key by using the following function.

    function create_referance_key($connect){

      /**********************create referance key********************************/
      $year = date('Y');

      $query = "
        SELECT MAX(strRefNo) AS refnum FROM claims
        WHERE strRefNo LIKE ''".$year."'%'";
     $statement = $connect->prepare($query);
     if($statement->execute())
     {   
        $result = $statement->fetchAll();
        //$RefNo = '';
        foreach($result as $row)
        {
              $RefNo = $row["refnum"];

              if($RefNo == ''){

              $RefNo = $year.'-'. str_pad(1, 4, '0', STR_PAD_LEFT);

              }else{
                 
              $array = explode("-",$RefNo);
              $RefNo = $year.'-'.str_pad($array[1] + 1, 4, 0, STR_PAD_LEFT);

              }

        }
        return $RefNo;
     }

 }

I want to create a key like 2021-0001,2021-0002,2021-0003. I try the above function.first time of execution of select query no data in the claim table .in case query return NULL. Other case query show max ref number.(both are check enter dummy data) But it does not return the any value . My SQL query part is alright. What is an error that I made inside foreach loop??

Claims table structure:

question from:https://stackoverflow.com/questions/65925058/how-to-create-custom-function-for-create-reference-number-as-primary-in-this-202

Answer 1

nothing wrong with your function .but

SELECT MAX(strRefNo) AS refnum FROM claims
        WHERE strRefNo LIKE ''".$year."'%';

parameter binding doing in the wrong way ''".$year."'%' should be changed as '".$year."%' .

Change function

function create_referance_key($connect, $empno){

      /**********************create referance key********************************/
      $year = date('Y');;

      $query = "
        SELECT MAX(strRefNo) AS refnum FROM claims
        WHERE strRefNo LIKE '".$year."%'";
     $statement = $connect->prepare($query);
     if($statement->execute())
     {   
        $result = $statement->fetchAll();
        //$RefNo = '';
        foreach($result as $row)
        {
              $RefNo = $row["refnum"];

              if(empty($RefNo)){

               $RefNo = $year.'-'. str_pad(1, 4, '0', STR_PAD_LEFT);

              }else{
                 
                 $array = explode("-",$RefNo);
                 $RefNo = $year.'-'.str_pad($array[1] + 1, 4, 0, STR_PAD_LEFT);

              }

        }
        return $RefNo;
     }

}