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

17 Comments
MURINZI Blaise 24th May 2022
|
Helpfully, thank you so much
felix 26th March 2021
|
sir i want to create 7 digit auto increment invoice with codeigniter kindly help
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();
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.?
tejas 21st June 2020
|
how to change the invoice no to 001 on change of fiscal year 
Candan dubey 10th October 2019
|
Nice, very helpful in creating invoices and quotations. Thank you