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

ByFull To Tech

Jul 9, 2019 #Auto-Increment, #Invoice Number, #Mysql, #PHP

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

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

By Full To Tech

Interesting Content Must Learn

Ultimate Guide to the Top Websites for Students and Professionals

Jun 27, 2023 Full To Tech

Interesting Content Must Learn

How to Check Battery Health in Windows PC

May 31, 2023 Full To Tech

Must Learn

Top 5 free image background remover tool

Mar 26, 2023 Full To Tech

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

Nirbhay Singh 24th July 2020

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.

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

ByFull To Tech

Like this:

By Full To Tech

Related Post

Ultimate Guide to the Top Websites for Students and Professionals

How to Check Battery Health in Windows PC

Top 5 free image background remover tool

You missed

Ultimate Guide to the Top Websites for Students and Professionals

How to Check Battery Health in Windows PC

Top 5 free image background remover tool

Different coding environment types used for software development

RCK INFOSYSTEM

ByFull To Tech

Share this:

Like this:

By Full To Tech

Related Post

You missed

RCK INFOSYSTEM