Bootstrap Datatable Server-side processing https://datatables.net/examples/server_side/simple.html
Bootstrap Modal https://getbootstrap.com/docs/4.0/components/modal/
Database Table
CREATE TABLE `members` (
`id` int(11) NOT NULL,
`firstname` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
`address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `members` (`id`, `firstname`, `lastname`, `address`) VALUES
(1, 'Airi ', 'Satou', 'Tokyo'),
(2, 'Angelica ', 'Ramos', 'London'),
(3, 'Ashton ', 'Cox', 'San Francisco'),
(4, 'Bradley ', 'Greer', 'London'),
(5, 'Brenden ', 'Wagner', 'San Francisco'),
(6, 'Brielle', 'Williamson', 'New York'),
(7, 'Bruno', 'Nash', 'London'),
(8, 'Caesar', 'Vance', 'New York'),
(9, 'Cara', 'Stevens', 'New York'),
(10, 'Cedric', 'Kelly', 'Edinburgh'),
(11, 'Charde', 'Marshall', 'San Francisco');
ALTER TABLE `members`
ADD PRIMARY KEY (`id`);
ALTER TABLE `members`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
index.php
//index.php
<!doctype html>
<head>
<title>PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-3"></div>
<div class="col-6">
<h1>PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form</h1>
<table id="member_table" class="table table-striped">
<thead>
<tr class="table-primary">
<th width="5%">ID</th>
<th width="50%">First Name</th>
<th width="50%">Last Name</th>
<th width="50%">Address</th>
<th scope="col" width="5%">Edit</th>
<th scope="col" width="5%">Delete</th>
</tr>
</thead>
</table>
</br>
<div align="right">
<button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-success btn-lg">Add New Member</button>
</div>
</div>
<div class="col-3"></div>
</div>
<div id="userModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="member_form" enctype="multipart/form-data">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title">Add Member</h4>
</div>
<div class="modal-body">
<label>Enter First Name</label>
<input type="text" name="firstname" id="firstname" class="form-control" />
<br />
<label>Enter Last Name</label>
<input type="text" name="lastname" id="lastname" class="form-control" />
<br />
<label>Enter Address</label>
<input type="text" name="address" id="address" class="form-control" />
<br />
</div>
<div class="modal-footer">
<input type="hidden" name="member_id" id="member_id" />
<input type="hidden" name="operation" id="operation" />
<input type="submit" name="action" id="action" class="btn btn-primary" value="Add" />
<button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
</div>
</div>
</form>
</div>
</div>
</div>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('#add_button').click(function(){
$('#member_form')[0].reset();
$('.modal-title').text("Add Member Details");
$('#action').val("Add");
$('#operation').val("Add");
});
var dataTable = $('#member_table').DataTable({
"paging":true,
"processing":true,
"serverSide":true,
"order": [],
"info":true,
"ajax":{
url:"fetch.php",
type:"POST"
},
"columnDefs":[
{
"targets":[0,3,4],
"orderable":false,
},
],
});
$(document).on('submit', '#member_form', function(event){
event.preventDefault();
var id = $('#id').val();
var firstname = $('#firstname').val();
var lastname = $('#lastname').val();
var address = $('#address').val();
if(firstname != '' && lastname != '' && address != '')
{
$.ajax({
url:"insert.php",
method:'POST',
data:new FormData(this),
contentType:false,
processData:false,
success:function(data)
{
$('#member_form')[0].reset();
$('#userModal').modal('hide');
dataTable.ajax.reload();
}
});
}
else
{
alert("Member First Name, Last Name Fields are Required");
}
});
$(document).on('click', '.update', function(){
var member_id = $(this).attr("id"); alert(member_id);
$.ajax({
url:"fetch_single.php",
method:"POST",
data:{member_id:member_id},
dataType:"json",
success:function(data)
{
$('#userModal').modal('show');
$('#id').val(data.id);
$('#firstname').val(data.firstname);
$('#lastname').val(data.lastname);
$('#address').val(data.address);
$('.modal-title').text("Edit Member Details");
$('#member_id').val(member_id);
$('#action').val("Save");
$('#operation').val("Edit");
}
})
});
$(document).on('click', '.delete', function(){
var member_id = $(this).attr("id");
if(confirm("Are you sure you want to delete this member?"))
{
$.ajax({
url:"delete.php",
method:"POST",
data:{member_id:member_id},
success:function(data)
{
dataTable.ajax.reload();
}
});
}
else
{
return false;
}
});
});
</script>
</body>
</html>
db.php
//db.php <?php $connection = new PDO( 'mysql:host=localhost;dbname=testingdb', 'root', '' ); ?>insert.php
//insert.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["operation"]))
{
if($_POST["operation"] == "Add")
{
$statement = $connection->prepare("
INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
$result = $statement->execute(
array(
':firstname' => $_POST["firstname"],
':lastname' => $_POST["lastname"],
':address' => $_POST["address"]
)
);
}
if($_POST["operation"] == "Edit")
{
$statement = $connection->prepare(
"UPDATE members
SET firstname = :firstname, lastname = :lastname, address = :address WHERE id = :id");
$result = $statement->execute(
array(
':firstname' => $_POST["firstname"],
':lastname' => $_POST["lastname"],
':address' => $_POST["address"],
':id' => $_POST["member_id"]
)
);
}
}
?>
fetch.php
//fetch.php
<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM members ";
if(isset($_POST["search"]["value"]))
{
$query .= 'WHERE firstname LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR lastname LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY id ASC ';
}
if($_POST["length"] != -1)
{
$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row["id"];
$sub_array[] = $row["firstname"];
$sub_array[] = $row["lastname"];
$sub_array[] = $row["address"]; //https://getbootstrap.com/docs/3.3/components/
$sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-primary btn-sm update"><i class="glyphicon glyphicon-pencil"> </i>Edit</button></button>';
$sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-sm delete"><i class="glyphicon glyphicon-remove"> Delete</button>';
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $filtered_rows,
"recordsFiltered" => get_total_all_records(),
"data" => $data
);
echo json_encode($output);
?>
function.php
//function.php
<?php
function get_total_all_records()
{
include('db.php');
$statement = $connection->prepare("SELECT * FROM members");
$statement->execute();
$result = $statement->fetchAll();
return $statement->rowCount();
}
?>
fetch_single.php
//fetch_single.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["member_id"]))
{
$output = array();
$statement = $connection->prepare(
"SELECT * FROM members WHERE id = '".$_POST["member_id"]."' LIMIT 1"
);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$output["id"] = $row["id"];
$output["firstname"] = $row["firstname"];
$output["lastname"] = $row["lastname"];
$output["address"] = $row["address"];
}
echo json_encode($output);
}
?>
delete.php
//delete.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["member_id"]))
{
$statement = $connection->prepare(
"DELETE FROM members WHERE id = :id"
);
$result = $statement->execute(
array(':id' => $_POST["member_id"])
);
}
?>
