Generate Auto Increment Invoice Number With Prefix Using PHP and MySql.

9th July 201910th July 2019 Full To TechMust Learn, PHP17 Comments 15216

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 table name be 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 )
);

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

?>

<?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;//
?>

<?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 auto-increment invoice number with a prefix. If you have any doubts or question comment down below.

17 Comments

MURINZI Blaise 24th May 2022

Helpfully, thank you so much

Nirbhay Singh 7th June 2022

Anytime @MURINZI Blaise

felix 26th March 2021

sir i want to create 7 digit auto increment invoice with codeigniter kindly help

Nirbhay Singh 1st April 2021

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;

}

Gwyn 16th February 2021

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();

Nirbhay Singh 6th March 2021

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();

PRADEEP KUMAR B S 18th November 2020

In place of mysqli how write in mysql

Aditya B 30th August 2020

Beeessssssttt ,I wroks.... thanks for this 😄

karan 28th July 2020

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.?

Nirbhay Singh 28th July 2020

<?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.

tejas 21st June 2020

how to change the invoice no to 001 on change of fiscal year

Full To Tech 21st June 2020

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;


    }

vishal waghmare 14th May 2021

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

Candan dubey 10th October 2019

Nice, very helpful in creating invoices and quotations. Thank you

Full To Tech 5th November 2019

You're Welcome Chandan Dubey

Mwakimount 23rd July 2020

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