Build Invoice System with PHP & MySQL

Invoice or Billing Management Systems are very popular as now most of transactions are done online. Now every sellers and buyers needs invoice system to handle billing online. So if you’re looking for invoice or billing system using PHP and MySQL, then you’re here at right place. In this tutorial you will learn how to develop invoice and billing system using PHP and MySQL.

We will cover this tutorial in easy steps with live demo to develop complete invoice system to create and edit invoices with invoice print to convert into PDF format. We will also allow to download complete source code of live demo.

As we will cover this tutorial with live example to build invoice system with PHP & MySQL, so the major files for this example is following.

• index.php
• invoice_list.php
• create_invoice.php
• edit_invoice.php
• action.php
• invoice.js
• Invoice.php

Step1: Create MySQL Database Tables
First we will create table invoice_user to store user login details to allow logged in user to manage invoices.

CREATE TABLE `invoice_user` (
`id` int(11) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`mobile` bigint(20) NOT NULL,
`address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


We will create table invoice_order to store invoice details.

CREATE TABLE `invoice_order` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`order_receiver_name` varchar(250) NOT NULL,
`order_receiver_address` text NOT NULL,
`order_total_before_tax` decimal(10,2) NOT NULL,
`order_total_tax` decimal(10,2) NOT NULL,
`order_tax_per` varchar(250) NOT NULL,
`order_total_after_tax` double(10,2) NOT NULL,
`order_amount_paid` decimal(10,2) NOT NULL,
`order_total_amount_due` decimal(10,2) NOT NULL,
`note` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


We will also create table invoice_order_item to store invoice items details.

CREATE TABLE `invoice_order_item` (
`order_item_id` int(11) NOT NULL,
`order_id` int(11) NOT NULL,
`item_code` varchar(250) NOT NULL,
`item_name` varchar(250) NOT NULL,
`order_item_quantity` decimal(10,2) NOT NULL,
`order_item_price` decimal(10,2) NOT NULL,
`order_item_final_amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Step2: Implement User Login
First we will create user login functionality to provide invoice manage access to logged in users. We will create login form in index.php.

<div class="row">
<div class="demo-heading pull">
<h2>Build Invoice System with PHP & MySQL</h2>
</div>
<div class="login-form">
<h4>Invoice User Login:</h4>
<form method="post" action="">
<div class="form-group">
<?php if ($loginError ) { ?>
<div class="alert alert-warning"><?php echo $loginError; ?></div>
<?php } ?>
</div>
<div class="form-group">
<input name="email" id="email" type="email" class="form-control" placeholder="Email address" autofocus="" required>
</div>
<div class="form-group">
<input type="password" class="form-control" name="pwd" placeholder="Password" required>
</div>
<div class="form-group">
<button type="submit" name="login" class="btn btn-info">Login</button>
</div>
</form>
</div>
</div>


We will handle login functionality on login form submit using method loginUsers().

<?php
if (!empty($_POST['email']) && !empty($_POST['pwd'])) {
include 'Invoice.php';
$invoice = new Invoice();
$user = $invoice->loginUsers($_POST['email'], $_POST['pwd']);
if(!empty($user)) {
$_SESSION['user'] = $user[0]['first_name']."".$user[0]['last_name'];
$_SESSION['userid'] = $user[0]['id'];
$_SESSION['email'] = $user[0]['email'];
$_SESSION['address'] = $user[0]['address'];
$_SESSION['mobile'] = $user[0]['mobile'];
header("Location:invoice_list.php");
} else {
$loginError = "Invalid email or password!";
}
}
?>


Step3: Display Invoice List
Now we will display user’s invoices list in invoice_list.php file. We will call invoice method getInvoiceList() to get list logged in user’s invoices list.

<div class="container">
<h2 class="title">PHP Invoice System</h2>
<?php include('menu.php');?>
<table id="data-table" class="table table-condensed table-striped">
<thead>
<tr>
<th>Invoice No.</th>
<th>Create Date</th>
<th>Customer Name</th>
<th>Invoice Total</th>
<th>Print</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
<?php
$invoiceList = $invoice->getInvoiceList();
foreach($invoiceList as $invoiceDetails){
$invoiceDate = date("d/M/Y, H:i:s", strtotime($invoiceDetails["order_date"]));
echo '
<tr>
<td>'.$invoiceDetails["order_id"].'</td>
<td>'.$invoiceDate.'</td>
<td>'.$invoiceDetails["order_receiver_name"].'</td>
<td>'.$invoiceDetails["order_total_after_tax"].'</td>
<td><a href="print_invoice.php?invoice_id='.$invoiceDetails["order_id"].'" title="Print Invoice"><span class="glyphicon glyphicon-print"></span></a></td>
<td><a href="edit_invoice.php?update_id='.$invoiceDetails["order_id"].'" title="Edit Invoice"><span class="glyphicon glyphicon-edit"></span></a></td>
<td><a href="#" id="'.$invoiceDetails["order_id"].'" class="deleteInvoice" title="Delete Invoice"><span class="glyphicon glyphicon-remove"></span></a></td>
</tr>
';
}
?>
</table>
</div>


Step4: Implement Invoice Create
Now in create_invoice.php, we will implement functionality to create invoice. We will create invoice form with required fields to save invoice details with items and totals.

<div class="container content-invoice">
<form action="" id="invoice-form" method="post" class="invoice-form" role="form" novalidate="">
<div class="load-animate animated fadeInUp">
<div class="row">
<div class="col-xs-8 col-sm-8 col-md-8 col-lg-8">
<h2 class="title">PHP Invoice System</h2>
<?php include('menu.php');?>
</div>
</div>
<input id="currency" type="hidden" value="$">
<div class="row">
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4">
<h3>From,</h3>
<?php echo $_SESSION['user']; ?><br>
<?php echo $_SESSION['address']; ?><br>
<?php echo $_SESSION['mobile']; ?><br>
<?php echo $_SESSION['email']; ?><br>
</div>
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4 pull-right">
<h3>To,</h3>
<div class="form-group">
<input type="text" class="form-control" name="companyName" id="companyName" placeholder="Company Name" autocomplete="off">
</div>
<div class="form-group">
<textarea class="form-control" rows="3" name="address" id="address" placeholder="Your Address"></textarea>
</div>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
<table class="table table-bordered table-hover" id="invoiceItem">
<tr>
<th width="2%"><input id="checkAll" class="formcontrol" type="checkbox"></th>
<th width="15%">Item No</th>
<th width="38%">Item Name</th>
<th width="15%">Quantity</th>
<th width="15%">Price</th>
<th width="15%">Total</th>
</tr>
<tr>
<td><input class="itemRow" type="checkbox"></td>
<td><input type="text" name="productCode[]" id="productCode_1" class="form-control" autocomplete="off"></td>
<td><input type="text" name="productName[]" id="productName_1" class="form-control" autocomplete="off"></td>
<td><input type="number" name="quantity[]" id="quantity_1" class="form-control quantity" autocomplete="off"></td>
<td><input type="number" name="price[]" id="price_1" class="form-control price" autocomplete="off"></td>
<td><input type="number" name="total[]" id="total_1" class="form-control total" autocomplete="off"></td>
</tr>
</table>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-3 col-md-3 col-lg-3">
<button class="btn btn-danger delete" id="removeRows" type="button">- Delete</button>
<button class="btn btn-success" id="addRows" type="button">+ Add More</button>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-8 col-md-8 col-lg-8">
<h3>Notes: </h3>
<div class="form-group">
<textarea class="form-control txt" rows="5" name="notes" id="notes" placeholder="Your Notes"></textarea>
</div>
<br>
<div class="form-group">
<input type="hidden" value="<?php echo $_SESSION['userid']; ?>" class="form-control" name="userId">
<input data-loading-text="Saving Invoice..." type="submit" name="invoice_btn" value="Save Invoice" class="btn btn-success submit_btn invoice-save-btm">
</div>
</div>
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4">
<span class="form-inline">
<div class="form-group">
<label>Subtotal:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="" type="number" class="form-control" name="subTotal" id="subTotal" placeholder="Subtotal">
</div>
</div>
<div class="form-group">
<label>Tax Rate:  </label>
<div class="input-group">
<input value="" type="number" class="form-control" name="taxRate" id="taxRate" placeholder="Tax Rate">
<div class="input-group-addon">%</div>
</div>
</div>
<div class="form-group">
<label>Tax Amount:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="" type="number" class="form-control" name="taxAmount" id="taxAmount" placeholder="Tax Amount">
</div>
</div>
<div class="form-group">
<label>Total:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="" type="number" class="form-control" name="totalAftertax" id="totalAftertax" placeholder="Total">
</div>
</div>
<div class="form-group">
<label>Amount Paid:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="" type="number" class="form-control" name="amountPaid" id="amountPaid" placeholder="Amount Paid">
</div>
</div>
<div class="form-group">
<label>Amount Due:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="" type="number" class="form-control" name="amountDue" id="amountDue" placeholder="Amount Due">
</div>
</div>
</span>
</div>
</div>
<div class="clearfix"></div>
</div>
</form>
</div>


We will save invoice details using invoice method saveInvoice().

<?php
include 'Invoice.php';
$invoice = new Invoice();
$invoice->saveInvoice($_POST);
?>


Step5: Implement Invoice Update
Now in edit_invoice.php, we will implement functionality to edit invoice. We will create invoice form with required fields to save invoice edit details with items and totals.

<div class="container content-invoice">
<form action="" id="invoice-form" method="post" class="invoice-form" role="form" novalidate="">
<div class="load-animate animated fadeInUp">
<div class="row">
<div class="col-xs-8 col-sm-8 col-md-8 col-lg-8">
<h1 class="title">PHP Invoice System</h1>
<?php include('menu.php');?>
</div>
</div>
<input id="currency" type="hidden" value="$">
<div class="row">
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4">
<h3>From,</h3>
<?php echo $_SESSION['user']; ?><br>
<?php echo $_SESSION['address']; ?><br>
<?php echo $_SESSION['mobile']; ?><br>
<?php echo $_SESSION['email']; ?><br>
</div>
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4 pull-right">
<h3>To,</h3>
<div class="form-group">
<input value="<?php echo $invoiceValues['order_receiver_name']; ?>" type="text" class="form-control" name="companyName" id="companyName" placeholder="Company Name" autocomplete="off">
</div>
<div class="form-group">
<textarea class="form-control" rows="3" name="address" id="address" placeholder="Your Address"><?php echo $invoiceValues['order_receiver_address']; ?></textarea>
</div>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-12 col-md-12 col-lg-12">
<table class="table table-bordered table-hover" id="invoiceItem">
<tr>
<th width="2%"><input id="checkAll" class="formcontrol" type="checkbox"></th>
<th width="15%">Item No</th>
<th width="38%">Item Name</th>
<th width="15%">Quantity</th>
<th width="15%">Price</th>
<th width="15%">Total</th>
</tr>
<?php
$count = 0;
foreach($invoiceItems as $invoiceItem){
$count++;
?>
<tr>
<td><input class="itemRow" type="checkbox"></td>
<td><input type="text" value="<?php echo $invoiceItem["item_code"]; ?>" name="productCode[]" id="productCode_<?php echo $count; ?>" class="form-control" autocomplete="off"></td>
<td><input type="text" value="<?php echo $invoiceItem["item_name"]; ?>" name="productName[]" id="productName_<?php echo $count; ?>" class="form-control" autocomplete="off"></td>
<td><input type="number" value="<?php echo $invoiceItem["order_item_quantity"]; ?>" name="quantity[]" id="quantity_<?php echo $count; ?>" class="form-control quantity" autocomplete="off"></td>
<td><input type="number" value="<?php echo $invoiceItem["order_item_price"]; ?>" name="price[]" id="price_<?php echo $count; ?>" class="form-control price" autocomplete="off"></td>
<td><input type="number" value="<?php echo $invoiceItem["order_item_final_amount"]; ?>" name="total[]" id="total_<?php echo $count; ?>" class="form-control total" autocomplete="off"></td>
<input type="hidden" value="<?php echo $invoiceItem['order_item_id']; ?>" class="form-control" name="itemId[]">
</tr>
<?php } ?>
</table>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-3 col-md-3 col-lg-3">
<button class="btn btn-danger delete" id="removeRows" type="button">- Delete</button>
<button class="btn btn-success" id="addRows" type="button">+ Add More</button>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-8 col-md-8 col-lg-8">
<h3>Notes: </h3>
<div class="form-group">
<textarea class="form-control txt" rows="5" name="notes" id="notes" placeholder="Your Notes"><?php echo $invoiceValues['note']; ?></textarea>
</div>
<br>
<div class="form-group">
<input type="hidden" value="<?php echo $_SESSION['userid']; ?>" class="form-control" name="userId">
<input type="hidden" value="<?php echo $invoiceValues['order_id']; ?>" class="form-control" name="invoiceId" id="invoiceId">
<input data-loading-text="Updating Invoice..." type="submit" name="invoice_btn" value="Save Invoice" class="btn btn-success submit_btn invoice-save-btm">
</div>
</div>
<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4">
<span class="form-inline">
<div class="form-group">
<label>Subtotal:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="<?php echo $invoiceValues['order_total_before_tax']; ?>" type="number" class="form-control" name="subTotal" id="subTotal" placeholder="Subtotal">
</div>
</div>
<div class="form-group">
<label>Tax Rate:  </label>
<div class="input-group">
<input value="<?php echo $invoiceValues['order_tax_per']; ?>" type="number" class="form-control" name="taxRate" id="taxRate" placeholder="Tax Rate">
<div class="input-group-addon">%</div>
</div>
</div>
<div class="form-group">
<label>Tax Amount:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="<?php echo $invoiceValues['order_total_tax']; ?>" type="number" class="form-control" name="taxAmount" id="taxAmount" placeholder="Tax Amount">
</div>
</div>
<div class="form-group">
<label>Total:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="<?php echo $invoiceValues['order_total_after_tax']; ?>" type="number" class="form-control" name="totalAftertax" id="totalAftertax" placeholder="Total">
</div>
</div>
<div class="form-group">
<label>Amount Paid:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="<?php echo $invoiceValues['order_amount_paid']; ?>" type="number" class="form-control" name="amountPaid" id="amountPaid" placeholder="Amount Paid">
</div>
</div>
<div class="form-group">
<label>Amount Due:  </label>
<div class="input-group">
<div class="input-group-addon currency">$</div>
<input value="<?php echo $invoiceValues['order_total_amount_due']; ?>" type="number" class="form-control" name="amountDue" id="amountDue" placeholder="Amount Due">
</div>
</div>
</span>
</div>
</div>
<div class="clearfix"></div>
</div>
</form>
</div>


We will edit save invoice using invoice method updateInvoice()

<?php
include 'Invoice.php';
$invoice = new Invoice();
$invoice->updateInvoice($_POST);
?>


Step6: Implement Invoice Print
Now we will implement functionality to create invoice PDF in print_invoice.php file to allow user to print or download invoice. We will get invoice details from database tables using invoice method getInvoice() and getInvoiceItems(). Then we will use PHP library Dompdf to create PDF from HTML.

<?php
session_start();
include 'Invoice.php';
$invoice = new Invoice();
$invoice->checkLoggedIn();
if(!empty($_GET['invoice_id']) && $_GET['invoice_id']) {
echo $_GET['invoice_id'];
$invoiceValues = $invoice->getInvoice($_GET['invoice_id']);
$invoiceItems = $invoice->getInvoiceItems($_GET['invoice_id']);
}
$invoiceDate = date("d/M/Y, H:i:s", strtotime($invoiceValues['order_date']));
$output = '';
$output .= '<table width="100%" border="1" cellpadding="5" cellspacing="0">
<tr>
<td colspan="2" align="center" style="font-size:18px"><b>Invoice</b></td>
</tr>
<tr>
<td colspan="2">
<table width="100%" cellpadding="5">
<tr>
<td width="65%">
To,<br />
<b>RECEIVER (BILL TO)</b><br />
Name : '.$invoiceValues['order_receiver_name'].'<br />
Billing Address : '.$invoiceValues['order_receiver_address'].'<br />
</td>
<td width="35%">
Invoice No. : '.$invoiceValues['order_id'].'<br />
Invoice Date : '.$invoiceDate.'<br />
</td>
</tr>
</table>
<br />
<table width="100%" border="1" cellpadding="5" cellspacing="0">
<tr>
<th align="left">Sr No.</th>
<th align="left">Item Code</th>
<th align="left">Item Name</th>
<th align="left">Quantity</th>
<th align="left">Price</th>
<th align="left">Actual Amt.</th>
</tr>';
$count = 0;
foreach($invoiceItems as $invoiceItem){
$count++;
$output .= '
<tr>
<td align="left">'.$count.'</td>
<td align="left">'.$invoiceItem["item_code"].'</td>
<td align="left">'.$invoiceItem["item_name"].'</td>
<td align="left">'.$invoiceItem["order_item_quantity"].'</td>
<td align="left">'.$invoiceItem["order_item_price"].'</td>
<td align="left">'.$invoiceItem["order_item_final_amount"].'</td>
</tr>';
}
$output .= '
<tr>
<td align="right" colspan="5"><b>Sub Total</b></td>
<td align="left"><b>'.$invoiceValues['order_total_before_tax'].'</b></td>
</tr>
<tr>
<td align="right" colspan="5"><b>Tax Rate :</b></td>
<td align="left">'.$invoiceValues['order_tax_per'].'</td>
</tr>
<tr>
<td align="right" colspan="5">Tax Amount: </td>
<td align="left">'.$invoiceValues['order_total_tax'].'</td>
</tr>
<tr>
<td align="right" colspan="5">Total: </td>
<td align="left">'.$invoiceValues['order_total_after_tax'].'</td>
</tr>
<tr>
<td align="right" colspan="5">Amount Paid:</td>
<td align="left">'.$invoiceValues['order_amount_paid'].'</td>
</tr>
<tr>
<td align="right" colspan="5"><b>Amount Due:</b></td>
<td align="left">'.$invoiceValues['order_total_amount_due'].'</td>
</tr>';
$output .= '
</table>
</td>
</tr>
</table>';
// create pdf of invoice
$invoiceFileName = 'Invoice-'.$invoiceValues['order_id'].'.pdf';
require_once 'dompdf/src/Autoloader.php';
Dompdf\Autoloader::register();
use Dompdf\Dompdf;
$dompdf = new Dompdf();
$dompdf->loadHtml(html_entity_decode($output));
$dompdf->setPaper('A4', 'landscape');
$dompdf->render();
$dompdf->stream($invoiceFileName, array("Attachment" => false));
?>


Step7: Implement Invoice Delete
We will implement invoice delete functionality in invoice.js. We will handle functionality on deleteInvoice handler and make Ajax request to action.php to delete invoice from database table.

$(document).on('click', '.deleteInvoice', function(){
var id = $(this).attr("id");
if(confirm("Are you sure you want to remove this?")){
$.ajax({
url:"action.php",
method:"POST",
dataType: "json",
data:{id:id, action:'delete_invoice'},
success:function(response) {
if(response.status == 1) {
$('#'+id).closest("tr").remove();
}
}
});
} else {
return false;
}
});


In action.php, we will check for delete invoice action and invoice id to delete invoice using invoice method deleteInvoice() and return JSON response.

<?php
include 'Invoice.php';
$invoice = new Invoice();
if($_POST['action'] == 'delete_invoice' && $_POST['id']) {
$invoice->deleteInvoice($_POST['id']);
$jsonResponse = array(
"status" => 1
);
echo json_encode($jsonResponse);
}
?>


Step8: Implement User Logout
We will also implement user logout functionality by passing action logout to action.php

<?php
if($_GET['action'] == 'logout') {
session_unset();
session_destroy();
header("Location:index.php");
}
?>


Step9: Complete Invoice Module with Method
We will create MySQL database connection and all methods in Invoice.php. Here is complete list of invoice methods.

<?php
public function loginUsers($email, $password){
$sqlQuery = "
SELECT id, email, first_name, last_name, address, mobile
FROM ".$this->invoiceUserTable."
WHERE email='".$email."' AND password='".$password."'";
return $this->getData($sqlQuery);
}
public function checkLoggedIn(){
if(!$_SESSION['userid']) {
header("Location:index.php");
}
}
public function saveInvoice($POST) {
$sqlInsert = "
INSERT INTO ".$this->invoiceOrderTable."(user_id, order_receiver_name, order_receiver_address, order_total_before_tax, order_total_tax, order_tax_per, order_total_after_tax, order_amount_paid, order_total_amount_due, note)
VALUES ('".$POST['userId']."', '".$POST['companyName']."', '".$POST['address']."', '".$POST['subTotal']."', '".$POST['taxAmount']."', '".$POST['taxRate']."', '".$POST['totalAftertax']."', '".$POST['amountPaid']."', '".$POST['amountDue']."', '".$POST['notes']."')";
mysqli_query($this->dbConnect, $sqlInsert);
$lastInsertId = mysqli_insert_id($this->dbConnect);
for ($i = 0; $i < count($POST['productCode']); $i++) { $sqlInsertItem = " INSERT INTO ".$this->invoiceOrderItemTable."(order_id, item_code, item_name, order_item_quantity, order_item_price, order_item_final_amount)
VALUES ('".$lastInsertId."', '".$POST['productCode'][$i]."', '".$POST['productName'][$i]."', '".$POST['quantity'][$i]."', '".$POST['price'][$i]."', '".$POST['total'][$i]."')";
mysqli_query($this->dbConnect, $sqlInsertItem);
}
}
public function updateInvoice($POST) {
if($POST['invoiceId']) {
$sqlInsert = "
UPDATE ".$this->invoiceOrderTable."
SET order_receiver_name = '".$POST['companyName']."', order_receiver_address= '".$POST['address']."', order_total_before_tax = '".$POST['subTotal']."', order_total_tax = '".$POST['taxAmount']."', order_tax_per = '".$POST['taxRate']."', order_total_after_tax = '".$POST['totalAftertax']."', order_amount_paid = '".$POST['amountPaid']."', order_total_amount_due = '".$POST['amountDue']."', note = '".$POST['notes']."'
WHERE user_id = '".$POST['userId']."' AND order_id = '".$POST['invoiceId']."'";
mysqli_query($this->dbConnect, $sqlInsert);
}
$this->deleteInvoiceItems($POST['invoiceId']);
for ($i = 0; $i < count($POST['productCode']); $i++) { $sqlInsertItem = " INSERT INTO ".$this->invoiceOrderItemTable."(order_id, item_code, item_name, order_item_quantity, order_item_price, order_item_final_amount)
VALUES ('".$POST['invoiceId']."', '".$POST['productCode'][$i]."', '".$POST['productName'][$i]."', '".$POST['quantity'][$i]."', '".$POST['price'][$i]."', '".$POST['total'][$i]."')";
mysqli_query($this->dbConnect, $sqlInsertItem);
}
}
public function getInvoiceList(){
$sqlQuery = "
SELECT * FROM ".$this->invoiceOrderTable."
WHERE user_id = '".$_SESSION['userid']."'";
return $this->getData($sqlQuery);
}
public function getInvoice($invoiceId){
$sqlQuery = "
SELECT * FROM ".$this->invoiceOrderTable."
WHERE user_id = '".$_SESSION['userid']."' AND order_id = '$invoiceId'";
$result = mysqli_query($this->dbConnect, $sqlQuery);
$row = mysqli_fetch_array($result, MYSQL_ASSOC);
return $row;
}
public function getInvoiceItems($invoiceId){
$sqlQuery = "
SELECT * FROM ".$this->invoiceOrderItemTable."
WHERE order_id = '$invoiceId'";
return $this->getData($sqlQuery);
}
public function deleteInvoiceItems($invoiceId){
$sqlQuery = "
DELETE FROM ".$this->invoiceOrderItemTable."
WHERE order_id = '".$invoiceId."'";
mysqli_query($this->dbConnect, $sqlQuery);
}
public function deleteInvoice($invoiceId){
$sqlQuery = "
DELETE FROM ".$this->invoiceOrderTable."
WHERE order_id = '".$invoiceId."'";
mysqli_query($this->dbConnect, $sqlQuery);
$this->deleteInvoiceItems($invoiceId);
return 1;
}
?>


You can view the live demo from the Demo link and can download the full script from the Download link below.

Demo


Git Clone Here

Download

Build Inventory System with Ajax, PHP & MySQL

Skip to main content

Inventory Management System is a tools for managing inventory data like orders, purchase, sales etc. Generally, desktop based inventory management systems are used to manage inventory data but its limited to that specific system. So the web based systems are more useful than desktop application as the web application can be accessed anywhere we need.
So if you’re looking for solution to development web based inventory management systems, then you’re here at right place. In this tutorial you will learn in detail to develop completed inventory management system using Ajax, PHP and MySQL. You would also like to check Invoice System with PHP & MySQL in which you learn to develop invoice or billing system.
We will cover this tutorial in details to create live example to handle all cases to develop complete inventory system.

Here are the features of this inventory management system

• Secure encrypted user login to access inventory management system.
• Dashboard to current inventory details.
• Manage customer to add new customer, edit and delete customer record.
• Manage category to add new category, edit and delete category record.
• Manage brand to add new brand details, edit and delete brand record.
• Manage product to add new product, view, edit and delete product record.
• Manage supplier to add new supplier details, edit and delete supplier record.
• Manage purchase to add make new purchase, edit and delete purchase details.
• Manage orders to create new orders, edit and delete order orders.

As we will cover this tutorial with live example to build inventory management system with Ajax, PHP & MySQL, so the major files for this example is following.

• index.php
• customer.php
• category.php
• brand.php
• product.php
• supplier.php
• purchase.php
• orders.php
• actions.php
• Inventory.php

Step1: Create MySQL Database Tables
First we will create MySQL database tables to store inventory data to manage it. We will create ims_product table to store product details.
CREATE TABLE `ims_product` (
`pid` int(11) NOT NULL,
`categoryid` int(11) NOT NULL,
`brandid` int(11) NOT NULL,
`pname` varchar(300) NOT NULL,
`model` varchar(255) NOT NULL,
`description` text NOT NULL,
`quantity` int(11) NOT NULL,
`unit` varchar(150) NOT NULL,
`base_price` double(10,2) NOT NULL,
`tax` decimal(4,2) NOT NULL,
`minimum_order` double(10,2) NOT NULL,
`supplier` int(11) NOT NULL,
`status` enum('active','inactive') NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create ims_supplier table to store supplier details.
CREATE TABLE `ims_supplier` (
`supplier_id` int(11) NOT NULL,
`supplier_name` varchar(200) NOT NULL,
`mobile` varchar(50) NOT NULL,
`address` text NOT NULL,
`status` enum('active','inactive') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create ims_purchase table to store supplier details.
CREATE TABLE `ims_purchase` (
`purchase_id` int(11) NOT NULL,
`supplier_id` varchar(255) NOT NULL,
`product_id` varchar(255) NOT NULL,
`quantity` varchar(255) NOT NULL,
`purchase_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create ims_order table to store order details.
CREATE TABLE `ims_order` (
`order_id` int(11) NOT NULL,
`product_id` varchar(255) NOT NULL,
`total_shipped` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Include Bootstrap, jQuery and jQuery Datatables Fiels
As we will handle design with Bootstrap, so we will include bootstrap and jQuery files. We will also include jQuery Datatables files as we will display inventory data in Datatables.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />

Step3: Display Current Inventory
In index.php file, we will create design to display current inventory details in table on dashboard menu.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Current Inventory</h3>
</div>
</div>
</div>
<div class="panel-body">
<div class="row"><div class="col-sm-12 table-responsive">
<table id="inventoryDetails" class="table table-bordered table-striped">
<thead><tr>
<th>Product</th>
<th>Product Code</th>
<th>Starting Inventory</th>
<th>Inventory Recieved</th>
<th>Inventory Shipped</th>
<th>Inventory on Hand</th>
</tr></thead>
</table>
</div></div>
</div>
</div>
</div>
</div>

We will load current inventory details in jQuery Datatables by making Ajax request to action.php and call $inventory->getInventoryDetails() to return data in JSON format to load Datatables.
$(document).ready(function(){
var inventoryData = $('#inventoryDetails').DataTable({
"processing":true,
"serverSide":true,
"lengthChange": false,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'getInventoryDetails'},
dataType:"json"
},
'rowCallback': function(row, data, index){
if(data[5] < 0){ $(row).find('td:eq(5)').css({'color':'red', 'font-weight':'bold'}); } else { $(row).find('td:eq(5)').css({'color':'green', 'font-weight':'bold'}); } }, "pageLength": 10 }); });
In method getInventoryDetails() from class Inventory.php, we will get inventory details and return data as JSON.
public function getInventoryDetails(){
$sqlQuery = "SELECT p.pid, p.pname, p.model, p.quantity as product_quantity, s.quantity as recieved_quantity, r.total_shipped
FROM ".$this->productTable." as p
LEFT JOIN ".$this->purchaseTable." as s ON s.product_id = p.pid
LEFT JOIN ".$this->orderTable." as r ON r.product_id = p.pid ";
if(isset($_POST['order'])) {
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY p.pid DESC ';
}
if($_POST['length'] != -1) {
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$inventoryData = array();
while( $inventory = mysqli_fetch_assoc($result) ) {
if(!$inventory['recieved_quantity']) {
$inventory['recieved_quantity'] = 0;
}
if(!$inventory['total_shipped']) {
$inventory['total_shipped'] = 0;
}
$inventoryInHand = ($inventory['product_quantity'] + $inventory['recieved_quantity']) - $inventory['total_shipped'];
$inventoryRow = array();
$inventoryRow[] = $inventory['pname'];
$inventoryRow[] = $inventory['model'];
$inventoryRow[] = $inventory['product_quantity'];
$inventoryRow[] = $inventory['recieved_quantity'];
$inventoryRow[] = $inventory['total_shipped'];
$inventoryRow[] = $inventoryInHand;
$inventoryData[] = $inventoryRow;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $inventoryData
);
echo json_encode($output);
}

Step4: Manage Customer
In customer.php, we will create design display customers list. We will create design to add/update customer records with Bootstrap Modal form.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Manage Customer</h3>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align="right">
<button type="button" name="add" id="addCustomer" data-toggle="modal" data-target="#userModal" class="btn btn-success btn-xs">Add</button>
</div>
</div>
<div class="clear:both"></div>
</div>
<div class="panel-body">
<div class="row">
<div class="col-sm-12 table-responsive">
<table id="customerList" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Address</th>
<th>Mobile</th>
<th>Balance</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
</div>
<div id="customerModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="customerForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Customer</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label>Name</label>
<input type="text" name="cname" id="cname" class="form-control" required />
</div>
<div class="form-group">
<label>Mobile</label>
<input type="number" name="mobile" id="mobile" class="form-control" required />
</div>
<div class="form-group">
<label>Balance</label>
<input type="number" name="balance" id="balance" class="form-control" required />
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" id="address" class="form-control" rows="5" required></textarea>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="userid" id="userid" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="customerAdd" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>
</div>

In customer.js file, will handle functionality to display customer records by making Ajax request to action.php and call method $inventory->getCustomerList(); to load customer data.
var userdataTable = $('#customerList').DataTable({
"lengthChange": false,
"processing": true,
"serverSide": true,
"order": [],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'customerList'},
dataType:"json"
},
"columnDefs":[
{
"target":[4,5],
"orderable":false
}
],
"pageLength": 25
});

In method getCustomerList() from class Inventory.php, we will get customer details and return as JSON data.
public function getCustomerList(){
$sqlQuery = "SELECT * FROM ".$this->customerTable." ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= '(id LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= '(name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR address LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR mobile LIKE "%'.$_POST["search"]["value"].'%") ';
$sqlQuery .= 'OR balance LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$customerData = array();
while( $customer = mysqli_fetch_assoc($result) ) {
$customerRows = array();
$customerRows[] = $customer['id'];
$customerRows[] = $customer['name'];
$customerRows[] = $customer['address'];
$customerRows[] = $customer['mobile'];
$customerRows[] = $customer['balance'];
$customerRows[] = '<button type="button" name="update" id="'.$customer["id"].'" class="btn btn-warning btn-xs update">Update</button>';
$customerRows[] = '<button type="button" name="delete" id="'.$customer["id"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
$customerData[] = $customerRows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $customerData
);
echo json_encode($output);
}

Step5: Manage Category
In category.php file, we will handle functionality to display category list, add and update category.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<div class="row">
<h3 class="panel-title">Manage Category</h3>
</div>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6">
<div class="row" align="right">
<button type="button" name="add" id="categoryAdd" data-toggle="modal" data-target="#categoryModal" class="btn btn-success btn-xs">Add</button>
</div>
</div>
<div style="clear:both"></div>
</div>
<div class="panel-body">
<div class="row">
<div class="col-sm-12 table-responsive">
<table id="categoryList" class="table table-bordered table-striped">
<thead><tr>
<th>ID</th>
<th>Category Name</th>
<th>Status</th>
<th>Edit</th>
<th>Delete</th>
</tr></thead>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
<div id="categoryModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="categoryForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Category</h4>
</div>
<div class="modal-body">
<label>Category Name</label>
<input type="text" name="category" id="category" class="form-control" required />
</div>
<div class="modal-footer">
<input type="hidden" name="categoryId" id="categoryId"/>
<input type="hidden" name="btn_action" id="btn_action"/>
<input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>

We will make Ajax request to action.php to call method $inventory->getCategoryList() to display category data.
var categoryData = $('#categoryList').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'categoryList'},
dataType:"json"
},
"columnDefs":[
{
"targets":[3, 4],
"orderable":false,
},
],
"pageLength": 25
});

In method getCategoryList(), we will get category data and return as JSON data to display in Datatables.
public function getCategoryList(){
$sqlQuery = "SELECT * FROM ".$this->categoryTable." ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= 'WHERE (name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR status LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY categoryid DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$categoryData = array();
while( $category = mysqli_fetch_assoc($result) ) {
$categoryRows = array();
$status = '';
if($category['status'] == 'active') {
$status = '<span class="label label-success">Active</span>';
} else {
$status = '<span class="label label-danger">Inactive</span>';
}
$categoryRows[] = $category['categoryid'];
$categoryRows[] = $category['name'];
$categoryRows[] = $status;
$categoryRows[] = '<button type="button" name="update" id="'.$category["categoryid"].'" class="btn btn-warning btn-xs update">Update</button>';
$categoryRows[] = '<button type="button" name="delete" id="'.$category["categoryid"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
$categoryData[] = $categoryRows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $categoryData
);
echo json_encode($output);
}

Step6: Manage Brand
In brand.php file, we will handle functionality to display brand list, add new brand, update existing brand details and delete brand.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-md-10">
<h3 class="panel-title">Manage Brand</h3>
</div>
<div class="col-md-2" align="right">
<button type="button" name="add" id="addBrand" class="btn btn-success btn-xs">Add</button>
</div>
</div>
</div>
<div class="panel-body">
<table id="brandList" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>Category</th>
<th>Brand Name</th>
<th>Status</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
<div id="brandModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="brandForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Brand</h4>
</div>
<div class="modal-body">
<div class="form-group">
<select name="categoryid" id="categoryid" class="form-control" required>
<option value="">Select Category</option>
<?php echo $inventory->categoryDropdownList(); ?>
</select>
</div>
<div class="form-group">
<label>Enter Brand Name</label>
<input type="text" name="bname" id="bname" class="form-control" required />
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>

We will make Ajax request action.php to load brand data in jQuery datatables by calling $inventory->getBrandList() method.
var branddataTable = $('#brandList').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'listBrand'},
dataType:"json"
},
"columnDefs":[
{
"targets":[4, 5],
"orderable":false,
},
],
"pageLength": 10
});

In method getBrandList() from class Inventory.php, we will get brand list and return as JSON data.
public function getBrandList(){
$sqlQuery = "SELECT * FROM ".$this->brandTable." as b
INNER JOIN ".$this->categoryTable." as c ON c.categoryid = b.categoryid ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= 'WHERE b.bname LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR c.name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR b.status LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY b.id DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$brandData = array();
while( $brand = mysqli_fetch_assoc($result) ) {
$status = '';
if($brand['status'] == 'active') {
$status = '<span class="label label-success">Active</span>';
} else {
$status = '<span class="label label-danger">Inactive</span>';
}
$brandRows = array();
$brandRows[] = $brand['id'];
$brandRows[] = $brand['bname'];
$brandRows[] = $brand['name'];
$brandRows[] = $status;
$brandRows[] = '<button type="button" name="update" id="'.$brand["id"].'" class="btn btn-warning btn-xs update">Update</button>';
$brandRows[] = '<button type="button" name="delete" id="'.$brand["id"].'" class="btn btn-danger btn-xs delete" data-status="'.$brand["status"].'">Delete</button>';
$brandData[] = $brandRows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $brandData
);
echo json_encode($output);
}

Step7: Manage Product
In product.php, we will handle functionality to list product, add new product, update product details and delete product.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Manage Product</h3>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align='right'>
<button type="button" name="add" id="addProduct" class="btn btn-success btn-xs">Add</button>
</div>
</div>
</div>
<div class="panel-body">
<div class="row"><div class="col-sm-12 table-responsive">
<table id="productList" class="table table-bordered table-striped">
<thead><tr>
<th>ID</th>
<th>Category</th>
<th>Brand Name</th>
<th>Product Name</th>
<th>Product Model</th>
<th>Quantity</th>
<th>Supplier Name</th>
<th>Status</th>
<th></th>
<th></th>
<th></th>
</tr></thead>
</table>
</div></div>
</div>
</div>
</div>
</div>
<div id="productModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="productForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Product</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label>Select Category</label>
<select name="categoryid" id="categoryid" class="form-control" required>
<option value="">Select Category</option>
<?php echo $inventory->categoryDropdownList();?>
</select>
</div>
<div class="form-group">
<label>Select Brand</label>
<select name="brandid" id="brandid" class="form-control" required>
<option value="">Select Brand</option>
</select>
</div>
<div class="form-group">
<label>Product Name</label>
<input type="text" name="pname" id="pname" class="form-control" required />
</div>
<div class="form-group">
<label>Product Model</label>
<input type="text" name="pmodel" id="pmodel" class="form-control" required />
</div>
<div class="form-group">
<label>Product Description</label>
<textarea name="description" id="description" class="form-control" rows="5" required></textarea>
</div>
<div class="form-group">
<label>Product Quantity</label>
<div class="input-group">
<input type="text" name="quantity" id="quantity" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
<span class="input-group-addon">
<select name="unit" id="unit" required>
<option value="">Select Unit</option>
<option value="Bags">Bags</option>
<option value="Bottles">Bottles</option>
<option value="Box">Box</option>
<option value="Dozens">Dozens</option>
<option value="Feet">Feet</option>
<option value="Gallon">Gallon</option>
<option value="Grams">Grams</option>
<option value="Inch">Inch</option>
<option value="Kg">Kg</option>
<option value="Liters">Liters</option>
<option value="Meter">Meter</option>
<option value="Nos">Nos</option>
<option value="Packet">Packet</option>
<option value="Rolls">Rolls</option>
</select>
</span>
</div>
</div>
<div class="form-group">
<label>Product Base Price</label>
<input type="text" name="base_price" id="base_price" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
</div>
<div class="form-group">
<label>Product Tax (%)</label>
<input type="text" name="tax" id="tax" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
</div>
<div class="form-group">
<label>Supplier</label>
<select name="supplierid" id="supplierid" class="form-control" required>
<option value="">Select Supplier</option>
<?php echo $inventory->supplierDropdownList();?>
</select>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="pid" id="pid" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>

We will make Ajax request to method $inventory->getProductList() to get product list.
var productData = $('#productList').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'listProduct'},
dataType:"json"
},
"columnDefs":[
{
"targets":[7, 8, 9],
"orderable":false,
},
],
"pageLength": 10
});

In method getProductList(), we will get product details and return as JSON data.
public function getProductList(){
$sqlQuery = "SELECT * FROM ".$this->productTable." as p
INNER JOIN ".$this->brandTable." as b ON b.id = p.brandid
INNER JOIN ".$this->categoryTable." as c ON c.categoryid = p.categoryid
INNER JOIN ".$this->supplierTable." as s ON s.supplier_id = p.supplier ";
if(isset($_POST["search"]["value"])) {
$sqlQuery .= 'WHERE b.bname LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR c.name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR p.pname LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR p.quantity LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR s.supplier_name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= 'OR p.pid LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST['order'])) {
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY p.pid DESC ';
}
if($_POST['length'] != -1) {
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$productData = array();
while( $product = mysqli_fetch_assoc($result) ) {
$status = '';
if($product['status'] == 'active') {
$status = '<span class="label label-success">Active</span>';
} else {
$status = '<span class="label label-danger">Inactive</span>';
}
$productRow = array();
$productRow[] = $product['pid'];
$productRow[] = $product['name'];
$productRow[] = $product['bname'];
$productRow[] = $product['pname'];
$productRow[] = $product['model'];
$productRow[] = $product["quantity"];
$productRow[] = $product['supplier_name'];
$productRow[] = $status;
$productRow[] = '<button type="button" name="view" id="'.$product["pid"].'" class="btn btn-info btn-xs view">View</button>';
$productRow[] = '<button type="button" name="update" id="'.$product["pid"].'" class="btn btn-warning btn-xs update">Update</button>';
$productRow[] = '<button type="button" name="delete" id="'.$product["pid"].'" class="btn btn-danger btn-xs delete" data-status="'.$product["status"].'">Delete</button>';
$productData[] = $productRow;
}
$outputData = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $productData
);
echo json_encode($outputData);
}

Step8: Manage Supplier
In supplier.php, we will design page to handle functionality to display supplier list, add, update and delete suppliers.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Manage Supplier</h3>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align="right">
<button type="button" name="add" id="addSupplier" data-toggle="modal" data-target="#userModal" class="btn btn-success btn-xs">Add</button>
</div>
</div>
<div class="clear:both"></div>
</div>
<div class="panel-body">
<div class="row">
<div class="col-sm-12 table-responsive">
<table id="supplierList" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Mobile</th>
<th>Address</th>
<th>Status</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
</div>
<div id="supplierModal" class="modal fade">
&##60;div class="modal-dialog">
<form method="post" id="supplierForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Supplier</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label>Supplier Name</label>
<input type="text" name="supplier_name" id="supplier_name" class="form-control" required />
</div>
<div class="form-group">
<label>Mobile</label>
<input type="text" name="mobile" id="mobile" class="form-control" required />
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" id="address" class="form-control" rows="5" required></textarea>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="supplier_id" id="supplier_id" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="addSupplier" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>
</div>

We will make Ajax request on page load to display suppliers list. We will call inventory method $inventory->getSupplierList() to get suppliers data.
var supplierDataTable = $('#supplierList').DataTable({
"lengthChange": false,
"processing": true,
"serverSide": true,
"order": [],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'supplierList'},
dataType:"json"
},
"columnDefs":[
{
"target":[4,5],
"orderable":false
}
],
"pageLength": 25
});

In method getSupplierList(), we will get suppliers data from MySQL table and return as JSON data.
public function getSupplierList(){
$sqlQuery = "SELECT * FROM ".$this->supplierTable." ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= 'WHERE (supplier_name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= '(address LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY supplier_id DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$supplierData = array();
while( $supplier = mysqli_fetch_assoc($result) ) {
$status = '';
if($supplier['status'] == 'active') {
$status = '<span class="label label-success">Active</span>';
} else {
$status = '<span class="label label-danger">Inactive</span>';
}
$supplierRows = array();
$supplierRows[] = $supplier['supplier_id'];
$supplierRows[] = $supplier['supplier_name'];
$supplierRows[] = $supplier['mobile'];
$supplierRows[] = $supplier['address'];
$supplierRows[] = $status;
$supplierRows[] = '<button type="button" name="update" id="'.$supplier["supplier_id"].'" class="btn btn-warning btn-xs update">Update</button>';
$supplierRows[] = '<button type="button" name="delete" id="'.$supplier["supplier_id"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
$supplierData[] = $supplierRows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $supplierData
);
echo json_encode($output);
}

Step9: Manage Purchase
In purchase.php, we will design page to handle purchase functionality like list purchase, make new purchase, update purchase details and delete purchase.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Manage Purchase</h3>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align='right'>
<button type="button" name="addPurchase" id="addPurchase" class="btn btn-success btn-xs">Add</button>
</div>
</div>
</div>
<div class="panel-body">
<div class="row"><div class="col-sm-12 table-responsive">
<table id="purchaseList" class="table table-bordered table-striped">
<thead><tr>
<th>ID</th>
<th>Product</th>
<th>Quantity</th>
<th>Supplier</th>
<th></th>
<th></th>
</tr></thead>
</table>
</div></div>
</div>
</div>
</div>
</div>
<div id="purchaseModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="purchaseForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Purchase</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label>Product Name</label>
<select name="product" id="product" class="form-control" required>
<option value="">Select Product</option>
<?php echo $inventory->productDropdownList();?>
</select>
</div>
<div class="form-group">
<label>Product Quantity</label>
<div class="input-group">
<input type="text" name="quantity" id="quantity" class="form-control" required pattern="[+-]?([0-9]*[.])?[0-9]+" />
</div>
</div>
<div class="form-group">
<label>Supplier</label>
<select name="supplierid" id="supplierid" class="form-control" required>
<option value="">Select Supplier</option>
<?php echo $inventory->supplierDropdownList();?>
</select>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="purchase_id" id="purchase_id" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>

We will make Ajax request to inventory method $inventory->listPurchase() to load purchase data to datatables.
var purchaseData = $('#purchaseList').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'listPurchase'},
dataType:"json"
},
"pageLength": 10
});

In method listPurchase(), we will get purchase data from MySQL tables and return as JSON data.
public function listPurchase(){
$sqlQuery = "SELECT * FROM ".$this->purchaseTable." as ph
INNER JOIN ".$this->productTable." as p ON p.pid = ph.product_id
INNER JOIN ".$this->supplierTable." as s ON s.supplier_id = ph.supplier_id ";
if(isset($_POST['order'])) {
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY ph.purchase_id DESC ';
}
if($_POST['length'] != -1) {
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$purchaseData = array();
while( $purchase = mysqli_fetch_assoc($result) ) {
$productRow = array();
$productRow[] = $purchase['purchase_id'];
$productRow[] = $purchase['pname'];
$productRow[] = $purchase['quantity'];
$productRow[] = $purchase['supplier_name'];
$productRow[] = '<button type="button" name="update" id="'.$purchase["purchase_id"].'" class="btn btn-warning btn-xs update">Update</button>';
$productRow[] = '<button type="button" name="delete" id="'.$purchase["purchase_id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
$purchaseData[] = $productRow;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $purchaseData
);
echo json_encode($output);
}

Step10: Manage Orders
In order.php, we will design page and handle functionality to list orders, add new order, update order and delete order.
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="row">
<div class="col-lg-10 col-md-10 col-sm-8 col-xs-6">
<h3 class="panel-title">Manage Orders</h3>
</div>
<div class="col-lg-2 col-md-2 col-sm-4 col-xs-6" align='right'>
<button type="button" name="add" id="addOrder" class="btn btn-success btn-xs">Add</button>
</div>
</div>
</div>
<div class="panel-body">
<div class="row"><div class="col-sm-12 table-responsive">
<table id="orderList" class="table table-bordered table-striped">
<thead><tr>
<th>ID</th>
<th>Product</th>
<th>Total Item</th>
<th>Customer</th>
<th></th>
<th></th>
</tr></thead>
</table>
</div></div>
</div>
</div>
</div>
</div>
<div id="orderModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="orderForm">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Add Order</h4>
</div>
<div class="modal-body">
<div class="form-group">
<label>Product Name</label>
<select name="product" id="product" class="form-control" required>
<option value="">Select Product</option>
<?php echo $inventory->productDropdownList();?>
</select>
</div>
<div class="form-group">
<label>Total Item</label>
<div class="input-group">
<input type="text" name="shipped" id="shipped" class="form-control" required />
</div>
</div>
<div class="form-group">
<label>Customer Name</label>
<select name="customer" id="customer" class="form-control" required>
<option value="">Select Customer</option>
<?php echo $inventory->customerDropdownList();?>
</select>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="order_id" id="order_id" />
<input type="hidden" name="btn_action" id="btn_action" />
<input type="submit" name="action" id="action" class="btn btn-info" value="Add" />
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>

We will make Ajax request to load orders datatable by calling inventory method $inventory->listOrders() to load data.
var orderData = $('#orderList').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'listOrder'},
dataType:"json"
},
"pageLength": 10
});

In method listOrders(), we will get orders details from MySQL database tables and return as JSON data.
public function listOrders(){
$sqlQuery = "SELECT * FROM ".$this->orderTable." as o
INNER JOIN ".$this->customerTable." as c ON c.id = o.customer_id
INNER JOIN ".$this->productTable." as p ON p.pid = o.product_id ";
if(isset($_POST['order'])) {
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY o.order_id DESC ';
}
if($_POST['length'] != -1) {
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$result = mysqli_query($this->dbConnect, $sqlQuery);
$numRows = mysqli_num_rows($result);
$orderData = array();
while( $order = mysqli_fetch_assoc($result) ) {
$orderRow = array();
$orderRow[] = $order['order_id'];
$orderRow[] = $order['pname'];
$orderRow[] = $order['total_shipped'];
$orderRow[] = $order['name'];
$orderRow[] = '<button type="button" name="update" id="'.$order["order_id"].'" class="btn btn-warning btn-xs update">Update</button>';
$orderRow[] = '<button type="button" name="delete" id="'.$order["order_id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
$orderData[] = $orderRow;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $numRows,
"recordsFiltered" => $numRows,
"data" => $orderData
);
echo json_encode($output);
}

There are functionality handled to add, edit and delete functionality to manage inventory details. You need to download script to get complete code and database tables.
You can view the live demo from the Demo link and can download the full script from the Download link below.
Demo

Download