Generate Auto Increment Invoice Number With Prefix Using PHP and MySql

Today in this blog we will understand the concept of How we can generate auto increment invoice number with prefix using PHP and MySql.

Let’s start, first of all, we will create a Mysql table using phpMyAdmin let say the table name is an invoice.

To begin with, the table creation command requires the following details −

  • Name of the table
  • Name of the fields
  • Definitions for each field
create table invoice(
   id INT NOT NULL AUTO_INCREMENT,
   invoice_no VARCHAR(20) NOT NULL,
   invoice_date VARCHAR(40) NOT NULL,
   created_on DATE,
   PRIMARY KEY ( id )
);


Now, At first, we will create a database connection say connection.php.

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "db_invoice";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

?>


You May Also Like:

After creating a database connection we will now write a script.

<?php  
    include "connection.php";
    $value2='';
    //Query to fetch last inserted invoice number
    $query = "SELECT invoice_no from tbl_invoice order by invoice_no DESC LIMIT 1";
    $stmt = $conn->query($query);
    if(mysqli_num_rows($stmt) > 0) {
        if ($row = mysqli_fetch_assoc($stmt)) {
            $value2 = $row['invoice_no'];
            $value2 = substr($value2, 10, 13);//separating numeric part
            $value2 = $value2 + 1;//Incrementing numeric part
            $value2 = "ABC/19-20/" . sprintf('%03s', $value2);//concatenating incremented value
            $value = $value2; 
        }
    } 
    else {
        $value2 = "ABC/19-20/001";
        $value = $value2;
    }
    echo $value;//
?>


Explanation of the above code, at first we include the connection file and now we will declare a variable $value2 after that we will write a query for fetching the last inserted invoice number now we execute the query using query() and check if there is a row or not if the row is greater than 0(Zero) then execute the if condition otherwise execute the else condition. Else condition will execute when there is no row in the invoice table.

Output:

If there is no data in the table then ABC/19-20/001.

Suppose If there is one data ABC/19-20/001 in the table then it will give ABC/19-20/002.

That’s it. Now you have successfully created a PHP Script to generate an auto-increment invoice number with a prefix. If you have any doubts or questions comment down below.

26 thoughts on “Generate Auto Increment Invoice Number With Prefix Using PHP and MySql.”
      1. Thank you for the good tutorial, but i wanna ask the invoice number must be inserted first into db table or , because i’ve inserted one value but only return else script but in db table have values

        1. do not insert any custom value to the DB just run the script it will automatically generate a serial invoice number.

          please share the screenshot of the script and DB, so that I can help you more.

    1. Change the code like this

      $query = "SELECT invoice_no from tbl_invoice invoice_no LIKE '%20-21' order by invoice_no DESC LIMIT 1";

      $stmt = $conn->query($query);

      if(mysqli_num_rows($stmt) > 0) {

      if ($row = mysqli_fetch_assoc($stmt)) {

      $value2 = $row['invoice_no'];

      $value2 = substr($value2, 10, 13);//separating numeric part

      $value2 = $value2 + 1;//Incrementing numeric part

      $value2 = "ABC/20-21/" . sprintf('%03s', $value2);//concatenating incremented value

      $value = $value2;

      }

      }

      else {

      $value2 = "ABC/20-21/001";

      $value = $value2;

      }

      1. i am new to php/codeigniter….i dont know where to write this code..and where to load this code
         
         

  1. I want to generate a Inventory no. Like INV/ABC/CPU/0001 and this value gets stored in mysql database. INV/ABC/CPU/ remains constant and only serial no. changes. can you help me out on this.?

    1. <?php  

          include “connection.php”;

          $value2=”;

          //Query to fetch last inserted invoice number

          $query = “SELECT invoice_no from tbl_invoice order by invoice_no DESC LIMIT 1”;

          $stmt = $conn->query($query);

          if(mysqli_num_rows($stmt) > 0) {

              if ($row = mysqli_fetch_assoc($stmt)) {

                  $value2 = $row[‘invoice_no’];

                  $value2 = substr($value2, 12);//separating numeric part

                  $value2 = $value2 + 1;//Incrementing numeric part

                  $value2 = “INV/ABC/CPU/” . sprintf(‘%u’, $value2);//concatenating incremented value

                  $value = $value2; 

              }

          } 

          else {

              $value2 = “INV/ABC/CPU/0001”;

              $value = $value2;

          }

          echo $value;//

      ?>

      Use this script to generate inventory no. 

       

  2. Hi! May I know how to incorporate your script into the script below? Agentcode is the field that I wanted to have auto increment number with 3 letter prefix. TQ.// Now we check if the data was submitted, isset() function will check if the data exists.                        if (!isset($_POST[’email’], $_POST[‘phone’])) {                            // Could not get the data that should have been sent.                            exit(‘Email or phone already exist!’);                        }                        // Make sure the submitted registration values are not empty.                        if (empty($_POST[‘agentname’]) || empty($_POST[’email’]) || empty($_POST[‘phone’])) {                            // One or more values are empty.                            exit(‘All fields are required!’);                        }                        // Validatng form data                        if (!filter_var($_POST[’email’], FILTER_VALIDATE_EMAIL)) {                            exit(‘Email is not valid!’);                        }                        if (preg_match(‘/[A-Za-z0-9]+/’, $_POST[‘agentname’]) == 0) {                            exit(‘Agent name is not valid!’);                        }                        //if (preg_match(‘/[A-Za-z0-9]+/’, $_POST[‘agentcode’]) == 0) {                        //    exit(‘Agent code is not valid!’);                        //}                        //Check if the account with that email or phone exists.                        if ($stmt = $con->prepare(‘SELECT agentid FROM agentlist WHERE email= ? or phone= ? ‘)) {                            // Bind parameters (s = string, i = int, b = blob, etc), hash the password using the PHP password_hash function.                            $stmt->bind_param(‘ss’,$_POST[’email’],$_POST[‘phone’]);                            $stmt->execute();                            $stmt->store_result();                        // Store the result so we can check if the account exists in the database.                        if ($stmt->num_rows > 0) {                            // agentname already exists                            echo ‘Agent code, email or phone number already exists, please use another!BACK’;                        } else {                            if ($stmt = $con->prepare(“INSERT INTO agentlist (agentcode, agentname, email, phone, password, activation_code, createtime, updatetime, status) VALUES (?, ?, ?, ?, ?, ?, now(),now(),?)”)) {                                $agentcode = $_POST[‘agentcode’];                                $password = password_hash(“cseagent123”, PASSWORD_DEFAULT);                                $uniqid = uniqid();                                $stmt->bind_param(‘sssssss’, $_POST[‘agentcode’], $_POST[‘agentname’], $_POST[’email’], $_POST[‘phone’], $password, $uniqid, $_POST[‘status’]);                                $stmt->execute();

    1. first of all create a function and then call that function to your variable function getAgentCode($con){   $value2=”;     //Query to fetch last inserted agent code     $query = “SELECT agentcode from agentlist order by agentcode DESC LIMIT 1”;     $stmt = $con->prepare($query);
          $stmt->execute();    if($stmt->rowCount() > 0) {         if ($row = $stmt->fetch()) {             $value2 = $row[‘agentcode’];             $value2 = substr($value2, 3, 5);//separating numeric part             $value2 = $value2 + 1;//Incrementing numeric part             $value2 = “ABC” . sprintf(‘%03s’, $value2);//concatenating incremented value             $value = $value2;          }     }      else {         $value2 = “ABC001”;         $value = $value2;     }     echo $value; }   Implementation in Your Code if ($stmt = $con->prepare(“INSERT INTO agentlist (agentcode, agentname, email, phone, password, activation_code, createtime, updatetime, status) VALUES (?, ?, ?, ?, ?, ?, now(),now(),?)”)) {                                 $agentcode = getAgentCode($con);  //Function Call getAgentCode                     $password = password_hash(“cseagent123”, PASSWORD_DEFAULT);                  $uniqid = uniqid();                                 $stmt->bind_param(‘sssssss’, $_POST[‘agentcode’], $_POST[‘agentname’], $_POST[’email’], $_POST[‘phone’], $password, $uniqid, $_POST[‘status’]);                         $stmt->execute();

    1. you can use this function to generate an auto-increment invoice number.

      function GenerateId() {

          $query = $this->db->select(‘invoice_id’)

                            ->from(‘table_name’)

                            ->get();

          $row = $query->last_row();

          if($row){

              $idPostfix = (int)substr($row->ID,1)+1;

              $nextId = ‘A’.STR_PAD((string)$idPostfix,5,”0″,STR_PAD_LEFT);

          }

          else{

              // For the first time

              $nextId = ‘A00001’;

          }

          return $nextId;

      }

  3. Nice blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple adjustements would really make my blog jump out. Please let me know where you got your design. Bless you

  4. You are not the average blog writer, man. You certainly have something powerful to contribute to the World Wide Web. Such a outstanding blog. Ill come back again for more. blisters on feet

  5. That is many inspirational stuff. Never knew that opinions could be this varied. Thanks for all of the enthusiasm to offer such helpful information here.

  6. There a few fascinating points in time in this post but I don’t know if I see these center to heart. There may be some validity but I’ll take hold opinion until I explore it further. Excellent article , thanks and then we want a lot more! Put into FeedBurner too

  7. Hi! I could have sworn I’ve been to this blog before but after browsing through some of the post I realized it’s new to me. Anyhow, I’m definitely delighted I found it and I’ll be book-marking and checking back often!

  8. You got great truthful factors right here. I done a search on the problem as well as learned most peoples will concur with your weblog. Your bibs will look good considerably lengthier if you do.

  9. Hi there colleagues, its fantastic piece of writing on the topic
    of cultureand completely defined, keep it
    up all the time.

Leave a Reply

Your email address will not be published. Required fields are marked *