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: 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 auto-increment invoice number with a prefix. If you have any doubts or question comment down below. Share this:Click to share on Twitter (Opens in new window)Click to share on Facebook (Opens in new window)Click to print (Opens in new window)Click to share on LinkedIn (Opens in new window)Click to share on Telegram (Opens in new window)Click to share on WhatsApp (Opens in new window)Like this:Like Loading... Post navigation Limit the number of words in a div using HTML and PHP Sorting an Array in PHP