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:
- How to decode JSON data and accessing the results in PHP
- Limit number of login attempt using PHP & MySQL
- Take Image Snapshot from a webcam with Jquery and HTML
- Is PHP in 2019 still a relevant language
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.
Nice, very helpful in creating invoices and quotations. Thank you
You’re Welcome Chandan Dubey
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
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.
how to change the invoice no to 001 on change of fiscal year
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;
}
i am new to php/codeigniter….i dont know where to write this code..and where to load this code
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.?
<?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.
Beeessssssttt ,I wroks…. thanks for this 😄
In place of mysqli how write in mysql
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();
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();
sir i want to create 7 digit auto increment invoice with codeigniter kindly help
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;
}
Helpfully, thank you so much
Anytime @MURINZI Blaise
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
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
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.
Your article has proven useful to me. It’s very informative and you are obviously very knowledgeable in this area.
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
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!
I love reading your blog because it has very interesting topics.:.:~’
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.
Hi there colleagues, its fantastic piece of writing on the topic
of cultureand completely defined, keep it
up all the time.
Pretty insightful post. I never thought it was this straightforward in the end.
I love your wp web template, exactly where would you obtain it through?
Youre so cool! I dont suppose Ive read anything like this prior to. So nice to seek out somebody by original applying for grants this subject. realy i
Thank you for the good writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! By the way, how could we communicate?
Awesome blog! Do you have any hints for aspiring writers? I’m hoping to start my own website soon but I’m a little lost on everything. Would you advise starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely confused .. Any ideas? Thanks a lot!
Outstanding post, you have pointed out some wonderful details , I also conceive this s a very good website.
Can be blogengine better than wp for some reason? Ought to be which is starting to be popluar as of late.
Wow, incredible blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is wonderful, as well as the content!
I am not sure where you’re getting your info, but good topic.
Greetings! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche. Your blog provided us beneficial information to work on. You have done a wonderful job!
Useful information. Fortunate me I found your web site unintentionally, and I’m stunned why this accident didn’t came about earlier! I bookmarked it.
Excellent goods from you, man. I have understand your stuff previous to and you are just extremely great. I really like what you have acquired here, certainly like what you’re saying and the way in which you say it. You make it enjoyable and you still care for to keep it sensible. I can’t wait to read much more from you. This is really a tremendous web site.