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

Full To Tech

6 years ago

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.

Share this: