Create Master Item / Product Form – Tutorial Build Point Of Sale With PHP, PDO, MySQL And Jquery Part 4.

How to create master item form at the point of sale PHP. Finally, we have come to part 4, This article will discuss how to create a master item page where the system can add, edit and delete items from the database. Let’s start the following tutorial.

To start this section, you should read the 3 previous articles in order to understand it. In Part 1 I discuss how to create a login form. Then in part 2, I discuss how to create a dynamic menu on adminLTE. In part 3, I created a master user form to give users access to each menu. Here’s the previous part URL address

Let’s begin the tutorial.

How To Create Master Item Page in Point Of Sale PHP

In this part 4 we will create four new files in the “sales” folder (pos/application/sales/): c_search_item.php, v_item.php, c_item.php and. j_item.js. Additionally, we will add a list of MYSQL queries to the master items (m_item table) in the pos.php file (pos/application/model/pos.php). Copy the following code in each PHP and javascript file.

c_search_item.php

<?php
session_start();
require_ once ("../model/dbconn.php");
require_ once ("../model/pos.php");
function cmp($a, $b) {
	return strcmp($a["id_item"], $b["id_item"]);
}
$term = $_POST['term'];
$pos = new pos();
$html ='';

$data = $pos->autoCompleteItem($term);
$hasil = array();
$i=1;
foreach ($data[1] as $row) 
{
	$hasil[$i]['item_name'] = $row['item_name'];
	$hasil[$i]['id_item'] = $row['id_item'];
	$hasil[$i]['unit'] = $row['unit'];
	$hasil[$i]['price'] = $row['price'];
	$hasil[$i]['stock'] = $row['stock'];
	$i++;
}
usort($hasil, "cmp");
$no = 1;
foreach($hasil as $key)
{
	$html .= '<tr>';
	$html .= '<td>'.$no.'</td>';
	$html .= '<td>'.$key['id_item'].'</td>';
	$html .= '<td>'.$key['item_name'].'</td>';
	$html .= '<td style="text-align:right">'.number_format($key['price']).'</td>';
	$html .= '<td style="text-align:right">'.$key['stock'].$key['unit'].'</td>';	

	$html .= '</tr>';
	$no++;
}

$array = array();
$array['data'] = $html;
echo json_encode($array);

<?php

session_start();

require_ once ("../model/dbconn.php");

require_ once ("../model/pos.php");

function cmp($a, $b) {

return strcmp($a["id_item"], $b["id_item"]);

}

$term = $_POST['term'];

$pos = new pos();

$html ='';

$data = $pos->autoCompleteItem($term);

$hasil = array();

$i=1;

foreach ($data[1] as $row)

{

$hasil[$i]['item_name'] = $row['item_name'];

$hasil[$i]['id_item'] = $row['id_item'];

$hasil[$i]['unit'] = $row['unit'];

$hasil[$i]['price'] = $row['price'];

$hasil[$i]['stock'] = $row['stock'];

$i++;

}

usort($hasil, "cmp");

$no = 1;

foreach($hasil as $key)

{

$html .= '<tr>';

$html .= '<td>'.$no.'</td>';

$html .= '<td>'.$key['id_item'].'</td>';

$html .= '<td>'.$key['item_name'].'</td>';

$html .= '<td style="text-align:right">'.number_format($key['price']).'</td>';

$html .= '<td style="text-align:right">'.$key['stock'].$key['unit'].'</td>';

$html .= '</tr>';

$no++;

}

$array = array();

$array['data'] = $html;

echo json_encode($array);

c_search_item.php used to show item list in the index page dashboard. After create this module, check your dashboard or index page and then try search the item in the text box. If successful the result like the following image:

v_item.php

<?php 
$titlepage="Master Item";
$idsmenu=1; 
include "../../library/config.php";
require_ once("../model/dbconn.php");
require_ once("../model/pos.php");
include "../layout/top-header.php";
include "../../library /check_login.php";
include "../../library /check_access.php";
include "../layout/header.php"; ?>

<section class="content">
	<div class="box box-success">
		<div class="box-header with-border">
			<h3 class="box-title">Master Item</h3>
		</div>
		<!--./ box header-->
		<div class="box-body">
			<div class="row">
				<div class="col-md-6">
					<button type="submit" class="btn btn-primary " id="btnadd" name=""><i class="fa fa-plus"></i> Add Item</button>
					<br>
				</div>
			</div>
			<div class="box-body table-responsive no-padding" style="max-width:1124px;">
				<table id="table_item" class="table  table-bordered table-hover ">
					<thead>
						<tr class="tableheader">
							<th style="width:40px">#</th>
							<th style="width:60px">Id</th>
							<th style="width:300px">Item</th>
							<th style="width:120px">Price</th>
							<th style="width:60px">Stok</th>
							<th style="width:250px">Note</th>
							<th></th>
						</tr>
					</thead>
					<tbody></tbody>
				</table>
			</div>		
		</div>
	</div><!-- /.box -->

</section><!-- /.content -->

<div id="modalmasteritem" class="modal fade ">
	<div class="modal-dialog modal-md">
		<div class="modal-content">
			<div class="modal-header">
				<button type="button" class="close" data-dismiss="modal">×</button>
				<h4 class="modal-title">Master Item Form</h4>
			</div>
			<!--modal header-->
			<div class="modal-body">
				<div class="form-horizontal">
					<div class="box-body">
						<div class="form-group"> <label class="col-sm-1  control-label">Id</label>
							<div class="col-sm-11"><input type="text" class="form-control " id="txtiditem" name="txtiditem" value="*New" placeholder="" disabled=""><input type="hidden" id="inputcrud" name="inputcrud" class="" value="N"> </div>
						</div>
						<div class="form-group"> <label class="col-sm-1  control-label">Item</label>
							<div class="col-sm-11"><input type="text" class="form-control " id="txtname" name="txtname" value="" placeholder="Please fill out item name"> </div>
						</div>
						<div class="form-group"> <label class="col-sm-1  control-label">Stok</label>
							<div class="col-sm-11"><input type="text" class="form-control decimal" id="txtstock" name="" value="0" placeholder=""> </div>
						</div>
						<div class="form-group"> <label class="col-sm-1  control-label">Unit</label>
							<div class="col-sm-11"><input type="text" class="form-control " id="txtunit" name="" value="" placeholder="Please fill out unit name"> </div>
						</div>
						<div class="form-group"> <label class="col-sm-1  control-label">Price</label>
							<div class="col-sm-11">
								<div class="input-group">
									<span class="input-group-addon">Rp.</span>
									<input type="text" class="form-control money" id="txtprice" name="" value="" placeholder=""></div>
								</div>
							</div>
							<div class="form-group"> <label class="col-sm-1  control-label">Note</label>
								<div class="col-sm-11"><textarea class="form-control " rows="3" id="txtnote" name="" placeholder="Note"></textarea> </div>
							</div>
							<div class="form-group"> <label class="col-sm-1  control-label"></label>
								<div class="col-sm-11"><button type="submit" title="Save Button" class="btn btn-primary " id="btnsaveitem" name=""><i class="fa fa-save"></i> Save</button> <span id="infoproses"></span> </div>
							</div>
						</div>
					</div>
				</div>
				<div class="modal-footer">
					<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
				</div>
				<!--modal footer-->
			</div>
			<!--modal-content-->
		</div>
		<!--modal-dialog modal-lg-->
	</div>


	<?php include "../layout/footer.php"; //footer template ?> 
	<?php include "../layout/bottom-footer.php"; //footer template ?> 
	<script src="j_item.js"></script>
</body>
</html>

100

<?php

$titlepage="Master Item";

$idsmenu=1;

include "../../library/config.php";

require_ once("../model/dbconn.php");

require_ once("../model/pos.php");

include "../layout/top-header.php";

include "../../library /check_login.php";

include "../../library /check_access.php";

include "../layout/header.php"; ?>

<h3 class="box-title">Master Item</h3>

</div>

<br>

</div>

<thead>

<th style="width:120px">Price</th>

</tr>

</thead>

</table>

</div>

</div>

</section>

<h4 class="modal-title">Master Item Form</h4>

</div>

</div>

</div>

</div>

</div>

<div class="form-group"> <label class="col-sm-1 control-label">Price</label>

</div>

</div>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

</div>

</div>

</div>

</div>

<?php include "../layout/footer.php"; //footer template ?>

<?php include "../layout/bottom-footer.php"; //footer template ?>

</body>

</html>

v_item.php used to create the master item page.

c_item.php

<?php
session_start();
require_ once ("../model/dbconn.php");
require_ once ("../model/pos.php");
if( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && ( $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' ) )
{
	$pos = new pos();
	$method=$_POST['method'];
	if($method == 'get_detail_item')
	{
		$id_item=$_POST['id_item'];
		$pos = new pos();
		$data = $pos->getItem($id_item);
		$array['data'] = $data[1];
		$array['result'] = $data[0];
		echo json_encode($array);
	}
	if($method == 'save_item')
	{
		$iditem = $_POST['id_item'];
		$nameitem = $_POST['item_name'];
		$unit= $_POST['unit'];
		$stock = $_POST['stock'];
		$price = $_POST['price'];
		$note = $_POST['note'];
		$crud=$_POST['crud'];
		$pos = new pos();
		if($_POST['crud'] == 'N')
		{
			$array = $pos->saveItem($nameitem,$price,$unit,$stock,$note);
			if($array[0] == true)
			{
				$result['id_item'] = $array[2];
			}
			
		}
		else
		{
			$array = $pos->updateItem($iditem,$nameitem,$price,$unit,$stock,$note);
		}
		$result['result'] = $array[0];
		$result['error'] = $array[1];
		$result['crud'] = $_POST['crud'];
		echo json_encode($result);
	}
	
	if($method == 'getdata'){
		$pos = new pos();
		$array = $pos->getListItem();
		$data = $array[1];
		$i=0;
		foreach ($data as $key) {
			$button = '<button  type="submit" id_item="'.$key['id_item'].'"  title="Tombol edit barang" class="btn btn-sm btn-primary btnedit "  id="btnedit'.$key['id_item'].'"  ><i class="fa fa-edit"></i></button> <button  type="submit" id_item="'.$key['id_item'].'"  title="Tombol hapus barang" class="btn btn-danger btn-sm btndelete "  id="btndelete'.$key['id_item'].'"  ><i class="fa fa-trash"></i></button>';

			$data[$i]['price'] =  number_format($data[$i]['price']);
			$data[$i]['DT_RowId']= $data[$i]['id_item'];
			$data[$i]['stock']= number_format($data[$i]['stock']);
			$data[$i]['button'] = $button;
			$i++;
		}
		$datax = array('data' => $data);
		echo json_encode($datax);
	}
	if($method == 'delete_item'){
		$id_item=$_POST['id_item'];
		$pos = new pos();
		$array = $pos->deleteItem($id_item);
		$data['result'] = $array[0];
		$data['error'] = $array[1];
		echo json_encode($data);
	}
	
} else {
	exit('No direct access allowed.');
}

<?php

session_start();

require_ once ("../model/dbconn.php");

require_ once ("../model/pos.php");

if( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && ( $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' ) )

{

$pos = new pos();

$method=$_POST['method'];

if($method == 'get_detail_item')

{

$id_item=$_POST['id_item'];

$pos = new pos();

$data = $pos->getItem($id_item);

$array['data'] = $data[1];

$array['result'] = $data[0];

echo json_encode($array);

}

if($method == 'save_item')

{

$iditem = $_POST['id_item'];

$nameitem = $_POST['item_name'];

$unit= $_POST['unit'];

$stock = $_POST['stock'];

$price = $_POST['price'];

$note = $_POST['note'];

$crud=$_POST['crud'];

$pos = new pos();

if($_POST['crud'] == 'N')

{

$array = $pos->saveItem($nameitem,$price,$unit,$stock,$note);

if($array[0] == true)

{

$result['id_item'] = $array[2];

}

else

{

$array = $pos->updateItem($iditem,$nameitem,$price,$unit,$stock,$note);

}

$result['result'] = $array[0];

$result['error'] = $array[1];

$result['crud'] = $_POST['crud'];

echo json_encode($result);

}

if($method == 'getdata'){

$pos = new pos();

$array = $pos->getListItem();

$data = $array[1];

$i=0;

foreach ($data as $key) {

$button = '<button type="submit" id_item="'.$key['id_item'].'" title="Tombol edit barang" class="btn btn-sm btn-primary btnedit " id="btnedit'.$key['id_item'].'" ><i class="fa fa-edit"></i></button> <button type="submit" id_item="'.$key['id_item'].'" title="Tombol hapus barang" class="btn btn-danger btn-sm btndelete " id="btndelete'.$key['id_item'].'" ><i class="fa fa-trash"></i></button>';

$data[$i]['price'] = number_format($data[$i]['price']);

$data[$i]['DT_RowId']= $data[$i]['id_item'];

$data[$i]['stock']= number_format($data[$i]['stock']);

$data[$i]['button'] = $button;

$i++;

}

$datax = array('data' => $data);

echo json_encode($datax);

}

if($method == 'delete_item'){

$id_item=$_POST['id_item'];

$pos = new pos();

$array = $pos->deleteItem($id_item);

$data['result'] = $array[0];

$data['error'] = $array[1];

echo json_encode($data);

}

} else {

exit('No direct access allowed.');

}

c_item is a master item backend. All of PHP function to manipulate data in master item placed in this file.

j_item.js

$(document).ready( function () 
{
	money();
	decimal();
	var value = {
		method : "getdata"
	};
	$('#table_item').DataTable({
		"paging": true,
		"lengthChange": false,
		"searching": true,
		"ordering": true,
		"info": false,
		"responsive": true,
		"autoWidth": false,
		"pageLength": 50,
		"dom": '<"top"f>rtip',
		"ajax": {
			"url": "c_item.php",
			"type": "POST",
			"data":value,
		},
		"columns": [
		{ "data": "urutan" },
		{ "data": "id_item" },
		{ "data": "item_name" },
		{ "data": "price" },
		{ "data": "stock" },
		{ "data": "note" },
		{ "data": "button" },
		]
	});
	$("#table_item_filter").addClass("pull-right");
});

$(document).on( "click","#btnadd", function() {
	$(".contentharga").remove();
	$("#modalmasteritem").modal('show');
	newitem();
});
function newitem()
{
	$("#txtiditem").val("*New");
	$("#txtname").val("");
	$("#txtstock").val(0);
	$("#txtprice").val(0);
	$("#txtnote").val("");
	$("#inputcrud").val("N");
	$("#txtunit").change();
	set_focus("#txtname");
}
$(document).on( "click",".btnedit", function() {
	var id_item = $(this).attr("id_item");
	var value = {
		id_item: id_item,
		method : "get_detail_item"
	};
	$.ajax(
	{
		url : "c_item.php",
		type: "POST",
		data : value,
		success: function(data, textStatus, jqXHR)
		{
			var hasil = jQuery.parseJSON(data);
			data = hasil.data;
			$("#inputcrud").val("E");
			$("#txtiditem").val(data.id_item);
			$("#txtname").val($.trim(data.item_name));
			$("#txtunit").val($.trim(data.unit));
			$("#txtstock").val(data.stock);
			$("#txtprice").val(addCommas(data.price));
			$("#txtnote").val($.trim(data.note));
			$("#modalmasteritem").modal('show');
			set_focus("#txtname");
		},
		error: function(jqXHR, textStatus, errorThrown)
		{
		}
	});
});
$(document).on( "click","#btnsaveitem", function() {
	var id_item = $("#txtiditem").val();
	var item_name = $("#txtname").val();
	var unit = $("#txtunit").val();
	var stock = cleanString($("#txtstock").val());
	var price = cleanString($("#txtprice").val());
	var note = $("#txtnote").val();
	var crud=$("#inputcrud").val();
	if(crud == 'E'){
		if(id_item == '' || id_item== null ){
			$.notify({
				message: "Item Id invalid"
			},{
				type: 'warning',
				delay: 8000,
			});		
			$("#txtiditem").focus();
			return;
		}	
	}
	if(item_name == '' || item_name== null ){
		$.notify({
			message: "Please fill out item name"
		},{
			type: 'warning',
			delay: 8000,
		});		
		$("#txtname").focus();
		return;
	}

	var value = {
		id_item: id_item,
		item_name: item_name,
		unit:unit,
		stock:stock,
		price:price,
		note:note,
		crud:crud,
		method : "save_item"
	};
	$(this).prop('disabled', true);
	proccess_waiting("#infoproses");
	$.ajax(
	{
		url : "c_item.php",
		type: "POST",
		data : value,
		success: function(data, textStatus, jqXHR)
		{
			$("#btnsaveitem").prop('disabled', false);
			$("#infoproses").html("");
			var data = jQuery.parseJSON(data);
			if(data.ceksat == 0){
				$.notify(data.error);
			}else{
				if(data.crud == 'N'){
					if(data.result == 1){
						$.notify('Save item successfuly');
						var table = $('#table_item').DataTable(); 
						table.ajax.reload( null, false );
						newitem();				
					}else{
						$.notify({
							message: "Error save item, error :"+data.error
						},{
							type: 'danger',
							delay: 8000,
						});
						set_focus("#txtiditem");
					}
				}else if(data.crud == 'E'){
					if(data.result == 1){
						$.notify('Update item successfuly');
						var table = $('#table_item').DataTable(); 
						table.ajax.reload( null, false );
						$("#modalmasteritem").modal("hide");
					}else{
						$.notify({
							message: "Error update item, error :"+data.error
						},{
							type: 'danger',
							delay: 8000,
						});					
						set_focus("#txtiditem");
					}
				}else{
					$.notify({
						message: "Invalid request"
					},{
						type: 'danger',
						delay: 8000,
					});	
				}
			}
		},
		error: function(jqXHR, textStatus, errorThrown)
		{
			$("#btnsaveitem").prop('disabled', false);
		}
	});
});
$(document).on( "click",".btndelete", function() {
	var id_item = $(this).attr("id_item");
	swal({   
		title: "Delete",   
		text: "Delete master item with id : "+id_item+" ?",   
		type: "warning",   
		showCancelButton: true,   
		confirmButtonColor: "#DD6B55",   
		confirmButtonText: "Delete",   
		closeOnConfirm: true }, 
		function(){   
			var value = {
				id_item: id_item,
				method : "delete_item"
			};
			$.ajax(
			{
				url : "c_item.php",
				type: "POST",
				data : value,
				success: function(data, textStatus, jqXHR)
				{
					var data = jQuery.parseJSON(data);
					if(data.result ==1){
						$.notify('Delete item successfuly');
						var table = $('#table_item').DataTable(); 
						table.ajax.reload( null, false );
					}else{
						$.notify({
							message: "Error delete item, error :"+data.error
						},{
							type: 'eror',
							delay: 8000,
						});	
					}
				},
				error: function(jqXHR, textStatus, errorThrown)
				{
				}
			});
		});
});

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

$(document).ready( function ()

{

money();

decimal();

var value = {

method : "getdata"

};

$('#table_item').DataTable({

"paging": true,

"lengthChange": false,

"searching": true,

"ordering": true,

"info": false,

"responsive": true,

"autoWidth": false,

"pageLength": 50,

"dom": '<"top"f>rtip',

"ajax": {

"url": "c_item.php",

"type": "POST",

"data":value,

"columns": [

{ "data": "urutan" },

{ "data": "id_item" },

{ "data": "item_name" },

{ "data": "price" },

{ "data": "stock" },

{ "data": "note" },

{ "data": "button" },

]

});

$("#table_item_filter").addClass("pull-right");

});

$(document).on( "click","#btnadd", function() {

$(".contentharga").remove();

$("#modalmasteritem").modal('show');

newitem();

});

function newitem()

{

$("#txtiditem").val("*New");

$("#txtname").val("");

$("#txtstock").val(0);

$("#txtprice").val(0);

$("#txtnote").val("");

$("#inputcrud").val("N");

$("#txtunit").change();

set_focus("#txtname");

}

$(document).on( "click",".btnedit", function() {

var id_item = $(this).attr("id_item");

var value = {

id_item: id_item,

method : "get_detail_item"

};

$.ajax(

{

url : "c_item.php",

type: "POST",

data : value,

success: function(data, textStatus, jqXHR)

{

var hasil = jQuery.parseJSON(data);

data = hasil.data;

$("#inputcrud").val("E");

$("#txtiditem").val(data.id_item);

$("#txtname").val($.trim(data.item_name));

$("#txtunit").val($.trim(data.unit));

$("#txtstock").val(data.stock);

$("#txtprice").val(addCommas(data.price));

$("#txtnote").val($.trim(data.note));

$("#modalmasteritem").modal('show');

set_focus("#txtname");

error: function(jqXHR, textStatus, errorThrown)

{

}

});

$(document).on( "click","#btnsaveitem", function() {

var id_item = $("#txtiditem").val();

var item_name = $("#txtname").val();

var unit = $("#txtunit").val();

var stock = cleanString($("#txtstock").val());

var price = cleanString($("#txtprice").val());

var note = $("#txtnote").val();

var crud=$("#inputcrud").val();

if(crud == 'E'){

if(id_item == '' || id_item== null ){

$.notify({

message: "Item Id invalid"

},{

type: 'warning',

delay: 8000,

});

$("#txtiditem").focus();

return;

}

if(item_name == '' || item_name== null ){

$.notify({

message: "Please fill out item name"

},{

type: 'warning',

delay: 8000,

});

$("#txtname").focus();

return;

}

var value = {

id_item: id_item,

item_name: item_name,

unit:unit,

stock:stock,

price:price,

note:note,

crud:crud,

method : "save_item"

};

$(this).prop('disabled', true);

proccess_waiting("#infoproses");

$.ajax(

{

url : "c_item.php",

type: "POST",

data : value,

success: function(data, textStatus, jqXHR)

{

$("#btnsaveitem").prop('disabled', false);

$("#infoproses").html("");

var data = jQuery.parseJSON(data);

if(data.ceksat == 0){

$.notify(data.error);

}else{

if(data.crud == 'N'){

if(data.result == 1){

$.notify('Save item successfuly');

var table = $('#table_item').DataTable();

table.ajax.reload( null, false );

newitem();

}else{

$.notify({

message: "Error save item, error :"+data.error

},{

type: 'danger',

delay: 8000,

});

set_focus("#txtiditem");

}

}else if(data.crud == 'E'){

if(data.result == 1){

$.notify('Update item successfuly');

var table = $('#table_item').DataTable();

table.ajax.reload( null, false );

$("#modalmasteritem").modal("hide");

}else{

$.notify({

message: "Error update item, error :"+data.error

},{

type: 'danger',

delay: 8000,

});

set_focus("#txtiditem");

}

}else{

$.notify({

message: "Invalid request"

},{

type: 'danger',

delay: 8000,

});

}

error: function(jqXHR, textStatus, errorThrown)

{

$("#btnsaveitem").prop('disabled', false);

}

});

$(document).on( "click",".btndelete", function() {

var id_item = $(this).attr("id_item");

swal({

title: "Delete",

text: "Delete master item with id : "+id_item+" ?",

type: "warning",

showCancelButton: true,

confirmButtonColor: "#DD6B55",

confirmButtonText: "Delete",

closeOnConfirm: true },

function(){

var value = {

id_item: id_item,

method : "delete_item"

};

$.ajax(

{

url : "c_item.php",

type: "POST",

data : value,

success: function(data, textStatus, jqXHR)

{

var data = jQuery.parseJSON(data);

if(data.result ==1){

$.notify('Delete item successfuly');

var table = $('#table_item').DataTable();

table.ajax.reload( null, false );

}else{

$.notify({

message: "Error delete item, error :"+data.error

},{

type: 'eror',

delay: 8000,

});

}

error: function(jqXHR, textStatus, errorThrown)

{

}

});

Add the following code to pos.php

/******************************************************************************
    START TABEL m_item
    *******************************************************************************/
    public function getListItem()
    {
      $db = $this->dblocal;
      try
      {
       $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM m_item t, 
        (SELECT @rownum := 0) r ORDER BY id_item ASC");
       $stmt->execute();
       $stat[0] = true;
       $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
       return $stat;
     }
     catch(PDOException $ex)
     {
       $stat[0] = false;
       $stat[1] = $ex->getMessage();
       return $stat;
     }
   }

   public function getItem($id_item){
    $db = $this->dblocal;
    try
    {
     $stmt = $db->prepare("select a.* from m_item a where a.id_item = :id ");
     $stmt->bindParam("id",$id_item);
     $stmt->execute();
     $stat[0] = true;
     $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
     return $stat;
   }
   catch(PDOException $ex)
   {
     $stat[0] = false;
     $stat[1] = $ex->getMessage();
     return $stat;
   }
 }


 public function updateItem($iditem,$item_name,$price,$unit,$stock,$note)
 {
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("UPDATE m_item 
    SET  item_name = UPPER(:item_name),
    unit= :unit,
    stock= :stock, 
    price= :price,
    note= :note 
    WHERE id_item= :iditem;");

   $stmt->bindParam("iditem",$iditem);
   $stmt->bindParam("item_name",$item_name);
   $stmt->bindParam("price",$price);
   $stmt->bindParam("note",$note);
   $stmt->bindParam("unit",$unit);
   $stmt->bindParam("stock",$stock);

   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success Edit!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function saveItem($item_name,$price,$unit,$stock,$note){
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("call saveItem(:item_name,:unit,:stock,:price,:note)");
   $stmt->bindParam("item_name",$item_name);
   $stmt->bindParam("price",$price);
   $stmt->bindParam("note",$note);
   $stmt->bindParam("unit",$unit);
   $stmt->bindParam("stock",$stock);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success save!";
   $stat[2] =  $stmt->fetchColumn(0);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function deleteItem($iditem)
{
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("delete from m_item where id_item = :id");
   $stmt->bindParam("id",$iditem);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = "Success Delete!";
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}

public function autoCompleteItem($term)
{
  $trm = "%".$term."%";
  $db = $this->dblocal;
  try
  {
   $stmt = $db->prepare("SELECT a.* FROM m_item a WHERE  item_name like :term or id_item  like :term order by item_name asc");
   $stmt->bindParam("term",$trm);
   $stmt->execute();
   $stat[0] = true;
   $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
   return $stat;
 }
 catch(PDOException $ex)
 {
   $stat[0] = false;
   $stat[1] = $ex->getMessage();
   return $stat;
 }
}
 /*******************************************************************************
 END OF TABEL M_Item
 *******************************************************************************/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

/******************************************************************************

START TABEL m_item

*******************************************************************************/

public function getListItem()

{

$db = $this->dblocal;

try

{

$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM m_item t,

(SELECT @rownum := 0) r ORDER BY id_item ASC");

$stmt->execute();

$stat[0] = true;

$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

public function getItem($id_item){

$db = $this->dblocal;

try

{

$stmt = $db->prepare("select a.* from m_item a where a.id_item = :id ");

$stmt->bindParam("id",$id_item);

$stmt->execute();

$stat[0] = true;

$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

public function updateItem($iditem,$item_name,$price,$unit,$stock,$note)

{

$db = $this->dblocal;

try

{

$stmt = $db->prepare("UPDATE m_item

SET item_name = UPPER(:item_name),

unit= :unit,

stock= :stock,

price= :price,

note= :note

WHERE id_item= :iditem;");

$stmt->bindParam("iditem",$iditem);

$stmt->bindParam("item_name",$item_name);

$stmt->bindParam("price",$price);

$stmt->bindParam("note",$note);

$stmt->bindParam("unit",$unit);

$stmt->bindParam("stock",$stock);

$stmt->execute();

$stat[0] = true;

$stat[1] = "Success Edit!";

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

public function saveItem($item_name,$price,$unit,$stock,$note){

$db = $this->dblocal;

try

{

$stmt = $db->prepare("call saveItem(:item_name,:unit,:stock,:price,:note)");

$stmt->bindParam("item_name",$item_name);

$stmt->bindParam("price",$price);

$stmt->bindParam("note",$note);

$stmt->bindParam("unit",$unit);

$stmt->bindParam("stock",$stock);

$stmt->execute();

$stat[0] = true;

$stat[1] = "Success save!";

$stat[2] = $stmt->fetchColumn(0);

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

public function deleteItem($iditem)

{

$db = $this->dblocal;

try

{

$stmt = $db->prepare("delete from m_item where id_item = :id");

$stmt->bindParam("id",$iditem);

$stmt->execute();

$stat[0] = true;

$stat[1] = "Success Delete!";

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

public function autoCompleteItem($term)

{

$trm = "%".$term."%";

$db = $this->dblocal;

try

{

$stmt = $db->prepare("SELECT a.* FROM m_item a WHERE item_name like :term or id_item like :term order by item_name asc");

$stmt->bindParam("term",$trm);

$stmt->execute();

$stat[0] = true;

$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

/*******************************************************************************

END OF TABEL M_Item

*******************************************************************************/

Don’t forget to add new table m_item with table structure like the following image:

Add new store procedures in the database. Execute the script below to create saveItem store procedures

DELIMITER $$

USE 'pos'$$

DROP PROCEDURE IF EXISTS 'saveItem'$$

CREATE  PROCEDURE 'saveItem'(var_item_name VARCHAR(100),var_unit VARCHAR(4),var_stock INT,var_price INT,var_note TEXT)
BEGIN
	DECLARE newnumber,newiditem VARCHAR(6);
	DECLARE countitem INT;
		SELECT COUNT(id_item) FROM m_item INTO countitem;
		IF(countitem = 0 ) THEN
			SET newnumber = '1';
		ELSE
			SELECT TRIM(CAST((CAST(SUBSTR(id_item,3,6) AS UNSIGNED)+1) AS CHAR(4)))    
			FROM m_item 
			ORDER BY id_item DESC LIMIT 1 INTO newnumber;
		END IF; 
		IF (LENGTH(newnumber) = 1) THEN
			SET newiditem=CONCAT('PB000',newnumber);
		ELSE
			IF (LENGTH(newnumber) = 2) THEN
				SET newiditem=CONCAT('PB00',newnumber);
			ELSE
				IF (LENGTH(newnumber) = 3) THEN
					SET newiditem=CONCAT('PB0',newnumber);
				ELSE
					SET newiditem=CONCAT('PB',newnumber);
				END IF ;
			END IF ;
		END IF ;
		 INSERT INTO m_item(id_item,item_name,unit,stock,price,note)
		 	VALUES(newiditem,var_item_name,var_unit,var_stock,var_price,var_note);
		 SELECT newiditem AS id_item;
    END$$

DELIMITER ;

DELIMITER $$

USE 'pos'$$

DROP PROCEDURE IF EXISTS 'saveItem'$$

CREATE PROCEDURE 'saveItem'(var_item_name VARCHAR(100),var_unit VARCHAR(4),var_stock INT,var_price INT,var_note TEXT)

BEGIN

DECLARE newnumber,newiditem VARCHAR(6);

DECLARE countitem INT;

SELECT COUNT(id_item) FROM m_item INTO countitem;

IF(countitem = 0 ) THEN

SET newnumber = '1';

ELSE

SELECT TRIM(CAST((CAST(SUBSTR(id_item,3,6) AS UNSIGNED)+1) AS CHAR(4)))

FROM m_item

ORDER BY id_item DESC LIMIT 1 INTO newnumber;

END IF;

IF (LENGTH(newnumber) = 1) THEN

SET newiditem=CONCAT('PB000',newnumber);

ELSE

IF (LENGTH(newnumber) = 2) THEN

SET newiditem=CONCAT('PB00',newnumber);

ELSE

IF (LENGTH(newnumber) = 3) THEN

SET newiditem=CONCAT('PB0',newnumber);

ELSE

SET newiditem=CONCAT('PB',newnumber);

END IF ;

INSERT INTO m_item(id_item,item_name,unit,stock,price,note)

VALUES(newiditem,var_item_name,var_unit,var_stock,var_price,var_note);

SELECT newiditem AS id_item;

END$$

DELIMITER ;

Until this part, We have created 5 tables and a MySQL store procedure.

Please test in your browser and make sure the system works as shown below:

Up to this point, the tutorial how to create master item page in point of sale php has been completed. The last step we will make the core of this application: making a sales system/simple point of sale in php and adminLTE. If there is any question please fill in the comment field at the end of the paragraph.

This complete point of sale project can be downloaded in part 5 of the article.