0

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:

enter image description here

Code Kris
  • 447
  • 3
  • 19
  • 1
    No point or benefit in preparing a query that you have already concatenated data into – RiggsFolly Jan 27 '21 at 18:22
  • Can you give an example of the wrong values you're getting? Simply saying "it does not return the required value" doesn't help much to understand the problem. – El_Vanja Jan 28 '21 at 12:31
  • it does not return any value bro – Code Kris Jan 28 '21 at 12:39
  • Please share more details, and your debugging attempts – Nico Haase Jan 28 '21 at 12:39
  • If it doesn't return anything, it could mean your statement didn't execute. Did you check for execution errors? – El_Vanja Jan 28 '21 at 12:40
  • no error in the excuction .but does not return value also – Code Kris Jan 28 '21 at 12:43
  • 1
    1) Your query string is all messed up. You have a bunch of extra quotes that are making it syntactically incorrect. 2) You're using prepared statements, but directly injecting the parameter into the query, completely defeating the purpose of preparing. Please consider parametrizing your query to avoid SQL injection and also avoid quoting issues. – El_Vanja Jan 28 '21 at 12:44
  • 1
    There is no way this query is executing. The final result will be something like `SELECT MAX(strRefNo) AS refnum FROM claims WHERE strRefNo LIKE ''2021'%'`. Does that look correct to you? – El_Vanja Jan 28 '21 at 12:46
  • 1
    It seems like you're using PDO. See this guide on [finding out the actual PDO error](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work/32648423#32648423). – El_Vanja Jan 28 '21 at 12:48
  • @ El_Vanja you are right .thank you very much – Code Kris Jan 28 '21 at 13:03

2 Answers2

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;
     }

}

Nipun Sachinda
  • 430
  • 2
  • 14
-2

Simple ~


$row = query_fetch_object(query(SELECT strRefNo FROM claims ORDER BY dtBillDate DESC LIMIT 0,1));

// SPLIT THE STRING INTO ARRAY using explode()
// print_r($getSplit) to know more what is inside the array
$getSplit = explode("/",$row->strRefNo);


$newRef = date("Y")."/".($getSplit[1] + 1);
De.Shaw
  • 23
  • 3