Database:
database name:product_database
tablename:product
field:
p_id
p_name
p_code
tablename:category
field:
c_id
c_name
tablename:product_category
field:
id(primary key)
product_id(forign key) with p_id
category_id(forign key) with c_id
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>PRODUCT</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="js/bootstrap.min.css">
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script type="text/javascript" src="js/transaction.js"></script>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<div class="container">
<h2 class="text-center">PRODUCT</h2>
<form id="product" method="POST">
<div class="form-group">
<label for="name">Name:</label>
<input id="name" type="text" name="name" class="form-control">
<span id="error"></span>
</div>
<div class="form-group">
<label for="code">code</label>
<input id="code" type="number" name="code" class="form-control">
<span id="error"></span>
</div>
<div class="form-group">
<label for="category">Category</label>
<?php
include "conn.php";
$res = mysqli_query($con,"SELECT * FROM category");
while($row = mysqli_fetch_assoc($res))
{
?>
<span><?php echo $row['c_name'] ?></span>
<input type="checkbox" id="<?php echo $row['c_id'];?>" name="cat[]" class="checkbox-inline" value=<?php echo $row['c_id']; ?> />
<?php
}
?>
</div>
<div class="form-group">
<button id="add" name="add" type="button" class="btn btn-primary btn-md">ADD</button>
<button id="resetform" name="reset" type="reset" value="Reset" class="btn btn-primary btn-md">Reset</button>
</div>
<div class="form-group">
<table id="display" class="table table-striped table-responsive">
<thead>
<tr>
<th>Update</th>
<th>Delete</th>
<th>Name</th>
<th>Code</th>
<th>Category</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
</div>
</body>
</html>
conn.php
<?php
$con=mysqli_connect("localhost","root","","product_database");
?>
view.php
<?php
include "conn.php";
?>
<?php
$json = array();
$query = mysqli_query ($con, "SELECT P.p_id AS 'product_id', P.p_name AS 'product_name',P.p_code AS 'product_code', GROUP_CONCAT(C.c_name) AS 'category_name' FROM Product P LEFT JOIN product_category PC ON P.p_id = PC.product_id LEFT JOIN category C ON PC.category_id = C.c_id GROUP BY pc.product_id");
while($row = mysqli_fetch_assoc($query))
{
$bus = array(
'p_id' => $row['product_id'],
'p_name' => $row['product_name'],
'p_code' => $row['product_code'],
'c_name' => $row['category_name'],
'result' => 'success'
);
array_push($json, $bus);
}
$jsonstring = json_encode($json);
echo $jsonstring;
?>
insert.php
<?php
include "conn.php";
?>
<?php
//name is set then execute belove code
if (!empty($_POST['name'])) {
$p_name = $_POST['name'];
$p_code = $_POST['code'];
//insert data in product table(name,code)
mysqli_query($con, "insert into product values('','$p_name','$p_code')");
//get last insert id from product table
$p_id = mysqli_insert_id($con);
//category is selected then execute belove code
if (!empty($_POST['cat'])) {
$category = $_POST['cat'];
$c_name = array();
//insert selected category in product_category table
foreach ($category as $cid => $c_id) {
mysqli_query($con, "insert into product_category (product_id,category_id)values('" . $p_id . "','" . $c_id . "')");
}
//using comma sepreted ids c_name select from category table
$ids = implode(",", $category);
$query_c = mysqli_query($con, "select c_name from category where c_id in ($ids)");
//one by one c_name get from $query and store in one array variable
while ($row = mysqli_fetch_assoc($query_c)) {
$c_name[] = $row['c_name'];
}
//fetched array converted into comma sepreted value and pass into json
$c_name = implode(',', $c_name);
//pass the json data to ajax
$data = array('result' => 'success', 'p_id' => $p_id, 'p_name' => $p_name, 'p_code' => $p_code, 'c_name' => $c_name);
echo json_encode($data);
}
//category is unselected then execute belove code
else {
$c_name = "";
//pass the json data to ajax
$data = array('result' => 'success', 'p_id' => $p_id, 'p_name' => $p_name, 'p_code' => $p_code, 'c_name' => $c_name);
echo json_encode($data);
}
}
//name is blank then execute belove code
else {
$ename = "name is required";
$data = array('message' => $ename);
echo json_encode($data);
}
?>
edit.php
<?php
include "conn.php";
?>
<?php
$ids=$_POST['edit_id']; //get the id of selected table row edit button
$c_id = array();
//get p_name & p_code from product database using specific table row id and store into $p_name & $p_code variable
$query_p = mysqli_query ($con, "SELECT p_name, p_code FROM Product where p_id = '".$ids."'");
while($row = mysqli_fetch_assoc($query_p))
{
$p_name = $row['p_name'];
$p_code = $row['p_code'];
}
//get category id from product_category database using specific table row id and store into $c_id array variable
$query_pc = mysqli_query($con,"SELECT category_id FROM product_category where product_id = '".$ids."'");
while($row = mysqli_fetch_assoc($query_pc))
{
$c_id[] = $row['category_id'];
}
//if category is get from database
if(!empty($c_id))
{
//array($c_id) converted into comma sepreted category id and store into $c_id
$c_id = implode(',',$c_id);
//pass the json data to ajax
$data = array('result' => 'success', 'p_name' => $p_name, 'p_code' => $p_code, 'c_id' => $c_id);
echo json_encode($data);
}
//if category is not get from database
else{
//pass the json data to ajax
$data = array('p_name' => $p_name, 'p_code' => $p_code);
echo json_encode($data);
}
?>
update.php
<?php
include "conn.php";
?>
<?php
//name is set then execute belove code
if(!empty($_POST['name']))
{
$p_name=$_POST['name'];
$p_code=$_POST['code'];
$ids=$_POST['update_id']; //get click edit button row id from form custom attribute update_id(get from ajax header)
//update value in product table(name,code) using ids
mysqli_query($con, "update product set p_name = '$p_name',p_code = '$p_code' where p_id = '".$ids."'");
//delete all old category value from product_category table using ids
mysqli_query($con,"delete from product_category where product_id = $ids");
//category is selected then execute belove code
if(!empty($_POST['cat']))
{
$category=$_POST['cat'];
$c_name = array();
//insert new selected category in product_category table using ids
foreach($category as $cid => $c_id)
{
mysqli_query($con,"insert into product_category (product_id,category_id)values('".$ids."','".$c_id."')");
}
//using comma sepreted id c_name select from category table
$ids = implode(",",$category);
$query_c = mysqli_query($con,"select c_name from category where c_id in ($ids)");
//one by one c_name get from $query and store in one array variable
while($row = mysqli_fetch_assoc($query_c))
{
$c_name[] = $row['c_name'];
}
//fetched array converted into comma sepreted value and pass into json
$c_name = implode(',',$c_name);
//pass the json data to ajax
$data = array('result'=>'success', 'p_name' => $p_name, 'p_code' => $p_code, 'c_name' => $c_name);
echo json_encode($data);
}
//category is unselected then execute belove code
else{
$c_name = "";
//pass the json data to ajax
$data = array('result'=>'success', 'p_name' => $p_name, 'p_code' => $p_code, 'c_name' => $c_name);
echo json_encode($data);
}
}
//name is blank then execute belove code
else{
$ename = "name is required";
$data = array('message' => $ename);
echo json_encode($data);
}
?>
delete.php
<?php
include "conn.php";
?>
<?php
//get the id of selected table row delete button
$ids=$_POST['del_id'];
//delete data in product table(name,code) and also delete on product_category table
mysqli_query($con, "delete from product where p_id='".$ids."'");
?>
transaction.js
$(document).ready(function () {
//view data on table row from database
$.ajax({
url: "view.php",
type: "POST",
contentType: "application/json",
success: function (jsonStr)
{
var obj = jQuery.parseJSON(jsonStr);
var new_row = '';
$.each(obj, function (index, value) {
new_row = "<tr id=" + value.p_id + ">" +
"<td>" + "<button id='edit' editId=" + value.p_id + " name='edit' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-pencil'></span>  Edit</button>" + "</td>" +
"<td>" + "<button id='delete' deleteId=" + value.p_id + " name='delete' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-remove-circle'></span>  DELETE</button>" + "</td>" +
"<td id='name'>" + value.p_name + "</td>" +
"<td id='code'>" + value.p_code + "</td>" +
"<td id='c_name'>" + value.c_name + "</td></tr>";
$("#display").append(new_row);
});
}
});
//click on add button new row add on table as well as add record in database
$(document).on("click", "#add", function () {
var form1 = $("#product").serialize();
$.ajax({
type: "POST",
url: "insert.php",
data: form1,
dataType: "json",
success: function (jsonStr) {
if (jsonStr.result == 'success')
{
$('#error').empty();
var new_row = '';
new_row = "<tr id=" + jsonStr.p_id + ">" +
"<td>" + "<button id='edit' editId=" + jsonStr.p_id + " name='edit' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-pencil'></span>  Edit</button>" + "</td>" +
"<td>" + "<button id='delete' deleteId=" + jsonStr.p_id + " name='delete' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-remove-circle'></span>  DELETE</button>" + "</td>" +
"<td>" + jsonStr.p_name + "</td>" +
"<td>" + jsonStr.p_code + "</td>" +
"<td>" + jsonStr.c_name + "</td></tr>";
$("#display tr:last").after(new_row);
} else {
$('#error').html(jsonStr.message);
}
}
});
});
//click on update button updated row display on table and also update in database
$(document).on("click", "#UPDATE", function () {
var update_id = $("#product[update_id]").attr("update_id"); //get custom attribute value from form
$.ajax({
type: "POST",
url: "update.php",
data: $("#product").serialize() + "&update_id=" + update_id,
dataType: "json",
success: function (jsonStr) {
if (jsonStr.result == 'success')
{
$('#error').empty();
var new_row = '';
new_row = "<tr id=" + update_id + ">" +
"<td>" + "<button id='edit' editId=" + update_id + " name='edit' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-pencil'></span>  Edit</button>" + "</td>" +
"<td>" + "<button id='delete' deleteId=" + update_id + " name='delete' type='button' class='btn btn-primary btn-sm'><span class='glyphicon glyphicon-remove-circle'></span>  DELETE</button>" + "</td>" +
"<td>" + jsonStr.p_name + "</td>" +
"<td>" + jsonStr.p_code + "</td>" +
"<td>" + jsonStr.c_name + "</td></tr>";
$('#display').find('#' + update_id).replaceWith(new_row);
$("#UPDATE").html("ADD"); //use for change name of UPDATE button
$("#UPDATE").attr("id", "add"); //use for change id of UPDATE button
} else {
$('#error').html(jsonStr.message);
}
}
});
});
//click on reset button clear form and custom attribute
$(document).on('click', '#resetform', function () {
$("#product").removeAttr("update_id");
});
//click on edit button particular row data fill on form
$(document).on('click', '#edit', function () {
var editId = $(this).closest("tr").attr("id");
$("#add").off("click"); //use for stop add button click event
$("#add").html("UPDATE"); //use for change name of add button
$("#add").attr("id", "UPDATE"); //use for change id of add button
$("#product").attr("update_id", editId); //set custom attribute update_id value using form id
$.ajax({
type: "POST",
url: "edit.php",
data: {edit_id: editId},
dataType: "json",
success: function (jsonStr) {
//clicked edit button id row data fill on form field
$("#name").val(jsonStr.p_name);
$("#code").val(jsonStr.p_code);
$('input[type="checkbox"]:checked').prop('checked', false); //before check new checkbox value old value remove
if (jsonStr.result == 'success')
{
$.each(jsonStr.c_id.split(','), function (_, val) {
$(':input[value=' + val + ']').prop("checked", true);
});
} else
{
$('input[type="checkbox"]:checked').prop('checked', false);
}
}
});
});
//click on delete button particular row delete on form table
$(document).on('click', '#delete', function () {
var deleteId = $(this).closest("tr").attr("id");
$.ajax({
type: "POST",
url: "delete.php",
data: {del_id: deleteId},
success: function (data) {
$("#" + deleteId).remove();
}
});
});
});
