Save Multiple Rows of Invoice Data In MySQL Database Using PHP, jQuery and Bootstrap 3

Posted 6:00 pm by muni & filed under CSS, HTML5, JAVASCRIPT, JQUERY, MYSQL, PHP, Twitter Bootstrap.

In this tutorial we are going to see how to Save Multiple Rows of Invoice Data In MySQL Database Using PHP, jQuery and Bootstrap 3. In previous tutorial we have seen how to dynamically add table rows with text fields using jQuery and PHP.

Please checkout our new version of Smart Invoice System Version 3….
 

Smart Invoice System Version 3

Dynamically add rows to table with Input Fields using jQuery and PHP

Next we have seen how to integrate jQuery autocomplete to this dynamically added input fields in table.

Add jQuery Autocomplete to Dynamically added input fields using jQuery, PHP and MySQL

Next we are going to see how to save/insert/store dynamically added multiple rows of invoice table data in MySQL database using PHP, jQuery and Bootstrap 3.

There are two way of data preparation to save multiple rows of invoice table data in MySQL database.

1. On the client side itself we prepare proper formatted array of data.
2. On the second method we will format the submitted array of data in order to save in MySQL database in Server side using PHP.

We are going to see both the method of saving multiple rows of invoice table data in MySQL database using PHP, jQuery and Bootstrap 3. Always preparing the formatted array of data on the client side is preferred one, because it reduces unwanted load on the server side.

Step 1: Create MySQL Database to Store Multiple Rows Invoice Table Data:

Please create database in your MySQL server in the name of “invoice”. Next Create two MySQL tables in the following name
1. invoices
2. invoice_details

Here is the PHP invoice database design that I have designed for this tutorial.

Here is SQL queries to create MySQL tables of above mentioned…

CREATE DATABASE IF NOT EXISTS `invoice` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `invoice`;


-- Dumping structure for table invoice.invoices
CREATE TABLE IF NOT EXISTS `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) DEFAULT NULL,
  `invoice_total` decimal(10,2) DEFAULT NULL,
  `invoice_subtotal` decimal(10,2) DEFAULT NULL,
  `tax` decimal(10,2) DEFAULT NULL,
  `amount_paid` decimal(10,2) DEFAULT NULL,
  `amount_due` decimal(10,2) DEFAULT NULL,
  `notes` text,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `uuid` varchar(75) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table invoice.invoices: ~0 rows (approximately)
/*!40000 ALTER TABLE `invoices` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoices` ENABLE KEYS */;


-- Dumping structure for table invoice.invoice_details
CREATE TABLE IF NOT EXISTS `invoice_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `product_id` varchar(25) DEFAULT NULL,
  `product_name` varchar(250) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_invoice_details_invoices_idx` (`invoice_id`),
  CONSTRAINT `fk_invoice_details_invoices` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Where
invoices – will hold invoice information
invoice_details – will hold multiple rows of invoice product information.

Step 2: Create Configuration File to Keep PHP Invoice Application Configurations (ex. Database)

Next I will create config.php file which hold database configurations and other configuration that are related to this Invoice PHP application.

<?php
/*
Site : http:www.smarttutorials.net
Author :muni
*/

define('BASE_PATH', 'http://localhost/tutorials/invoice/');

//Database credentails
define('DB_HOST', 'localhost');
define('DB_NAME', 'invoice');
define('DB_USERNAME','root');
define('DB_PASSWORD','');


$con = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if( mysqli_connect_error()) echo "Failed to connect to MySQL: " . mysqli_connect_error();

Step 3: Create Invoice HTML Table With Input Fields

Next Create your Invoice HTML Form, and add HTML table and input fields in it. Please refer following tutorial in order add dynamically add rows to table.

Dynamically add rows to table with Input Fields using jQuery and PHP

Next follow this tutorial to add jQuery autocomplete functionality to dynamically added table rows of input elements.

Add jQuery Autocomplete to Dynamically added input fields using jQuery, PHP and MySQL

<table class="table table-bordered table-hover">
	<thead>
		<tr>
			<th width="2%"><input id="check_all" class="formcontrol" type="checkbox"/></th>
			<th width="15%">Item No</th>
			<th width="38%">Item Name</th>
			<th width="15%">Price</th>
			<th width="15%">Quantity</th>
			<th width="15%">Total</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td><input class="case" type="checkbox"/></td>
			<td><input type="text" data-type="productCode" name="data[0]['product_id']" id="itemNo_1" class="form-control autocomplete_txt" autocomplete="off"></td>
			<td><input type="text" data-type="productName" name="data[0]['product_name']" id="itemName_1" class="form-control autocomplete_txt" autocomplete="off"></td>
			<td><input type="number" name="data[0]['price']" id="price_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
			<td><input type="number" name="data[0]['quantity']" id="quantity_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
			<td><input type="number" name="data[0]['total']" id="total_1" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
		</tr>
	</tbody>
</table>

Step 4: Dynamically add Table Rows Using jQuery

Next we will add table rows dynamically using jQuery with input fields in it. Here is the jQuery script that adds the rows to the HTML invoice table dynamically with input fields.

var i=$('table#invoiceTable tr').length;
$("#addmore").on('click',function(){
	html = '<tr>';
	html += '<td><input class="case" type="checkbox"/></td>';
	html += '<td><input type="text" data-type="productCode" name="data['+i+'][product_id]" id="itemNo_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
	html += '<td><input type="text" data-type="productName" name="data['+i+'][product_name]" id="itemName_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
	html += '<td><input type="text" name="data['+i+'][price]" id="price_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
	html += '<td><input type="text" name="data['+i+'][quantity]" id="quantity_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
	html += '<td><input type="text" name="data['+i+'][total]" id="total_'+i+'" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
	html += '</tr>';
	$('table#invoiceTable').append(html);
	i++;
});

//to check all checkboxes
$(document).on('change','#check_all',function(){
	$('input[class=case]:checkbox').prop("checked", $(this).is(':checked'));
});

//deletes the selected table rows
$("#delete").on('click', function() {
	$('.case:checkbox:checked').parents("tr").remove();
	$('#check_all').prop("checked", false); 
	calculateTotal();
});

Step 5: Add Required Asset Files To Dynamically Add Input Text Fields In Table Rows

Next add required asset files in your index.php file ( this insert HTML table data into database using PHP tutorial).

CSS Asset Files

<link href="css/jquery-ui.min.css" rel="stylesheet">
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/datepicker.css" rel="stylesheet">
<link href="css/font-awesome.min.css" rel="stylesheet">
<link href="css/style.css" rel="stylesheet">

JavaScript Asset Files

<script src="js/jquery.min.js"></script>
<script src="js/jquery-ui.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/bootstrap-datepicker.js"></script>
<script src="js/auto.js"></script>

Step 6: Insert Dynamically added Table Rows Invoice Data In MySQL Database Using PHP and jQuery

Next create two PHP function which insert multiple rows invoice data in MySQL database.
1. saveInvoice()
2. saveInvoiceDetail()

Where
saveInvoice() – will save invoice information in ‘invoices’ table in single row .
saveInvoiceDetail() – will save invoice product code, price and name etc. in ‘invoice_details’ table in multiple rows.

Here is the PHP script which store/insert/save multiple row invoice data in MySQL database.

<?php

function saveInvoice( array $data){
		if( !empty( $data ) ){ 
			global $con;
			
			$count = 0;
			if( isset($data['data'] )){
				foreach ($data['data'] as $value) {
					if(!empty($value['product_id'] ))$count++;
				}
			}
			
			if($count == 0)throw new Exception( "Please add atleast one product to invoice." );
			
			return [
					'success' => true,
					'uuid' => $uuid,
					'message' => 'Demo Purpose I have disabled invoice save, Please download script and check in your local machine..'
				];
			
			// escape variables for security
			if( !empty( $data)){
				$client_id = mysqli_real_escape_string( $con, trim( $data['client_id'] ) );
				$invoice_total = mysqli_real_escape_string( $con, trim( $data['invoice_total'] ) );
				$invoice_subtotal = mysqli_real_escape_string( $con, trim( $data['invoice_subtotal'] ) );
				$tax = mysqli_real_escape_string( $con, trim( $data['tax'] ) );
				$amount_paid = mysqli_real_escape_string( $con, trim( $data['amount_paid'] ) );
				$amount_due = mysqli_real_escape_string( $con, trim( $data['amount_due'] ) );
				$notes = mysqli_real_escape_string( $con, trim( $data['notes'] ) );

				

				$id = mysqli_real_escape_string( $con, trim( $data['id'] ) );

				if(empty($id)){
					$uuid = uniqid();
					$query = "INSERT INTO invoices (`id`, `client_id`,  `invoice_total`, `invoice_subtotal`, `tax`,
							`amount_paid`, `amount_due`, `notes`, `created`, `uuid`)
							VALUES (NULL, '$client_id',  '$invoice_total', '$invoice_subtotal', '$tax', '$amount_paid', '$amount_due', '$notes',
							CURRENT_TIMESTAMP, '$uuid')";

				}else{
					$uuid = $data['uuid'];
					$query = "UPDATE `invoices` SET `client_id` = '$client_id', `invoice_total` ='$invoice_total',`invoice_subtotal` = '$invoice_subtotal',
							`tax` = '$tax', `amount_paid` = '$amount_paid', `amount_due` = '$amount_due', `notes` = '$notes', `updated` = CURRENT_TIMESTAMP
							WHERE `id` = $id";
				}
				if(!mysqli_query($con, $query)){
					throw new Exception(  mysqli_error($con) );
				}else{
					if(empty($id))$id = mysqli_insert_id($con);
				}

				if( isset( $data['data']) && !empty( $data['data'] )){
					saveInvoiceDetail( $data['data'], $id );
				}
				return [
					'success' => true,
					'uuid' => $uuid,
					'message' => 'Invoice Saved Successfully.'
				];
			}else{
				throw new Exception( "Please check, some of the required fileds missing" );
			}
		} else{
			throw new Exception( "Please check, some of the required fileds missing" );
		}
	}


function saveInvoiceDetail(array $invoice_details, $invoice_id = ''){ 
	global $con;
    $deleteQuery = "DELETE FROM invoice_details WHERE invoice_id = $invoice_id";
    mysqli_query($con, $deleteQuery);

    foreach ($invoice_details as $invoice_detail){
        $product_id = mysqli_real_escape_string( $con, trim( $invoice_detail['product_id'] ) );
        $productName = mysqli_real_escape_string( $con, trim( $invoice_detail['product_name'] ) );
        $quantity = mysqli_real_escape_string( $con, trim( $invoice_detail['quantity'] ) );
        $price = mysqli_real_escape_string( $con, trim( $invoice_detail['total'] ) );
        

        $query = "INSERT INTO invoice_details (`id`, `invoice_id`, `product_id`, product_name, `quantity`, `price`)
                VALUES (NULL, '$invoice_id', '$product_id', '$productName', '$quantity', '$price')";
        mysqli_query($con, $query);
    }

}