Create database table
CREATE TABLE `member` (
`id` int(11) NOT NULL,
`name` varchar(150) NOT NULL,
`email` varchar(150) NOT NULL,
`phone` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css
datatables
https://datatables.net/
Add advanced interaction controls
to your HTML tables the free & easy way
index.php
//index.php
<!doctype html>
<head>
<title>PHP Mysql PDO CRUD Server Side Ajax DataTables</title>
<link rel="stylesheet" type="text/css" href="styles.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.2/css/jquery.dataTables.min.css">
</head>
<body>
<div class="container">
<br />
<h3 align="center">PHP Mysql PDO CRUD Server Side Ajax DataTables</h3>
<br />
<div align="right">
<button type="button" id="add_button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#userModal">
Add Member
</button>
</div>
<br />
<table id="member_table" class="table table-striped">
<thead bgcolor="#6cd8dc">
<tr class="table-primary">
<th width="30%">ID</th>
<th width="50%">Name</th>
<th width="30%">Email</th>
<th width="30%">Phone</th>
<th scope="col" width="5%">Edit</th>
<th scope="col" width="5%">Delete</th>
</tr>
</thead>
</table>
<div class="modal" id="userModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Add Member</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form method="post" id="member_form" enctype="multipart/form-data">
<div class="modal-body">
<label>Enter Name</label>
<input type="text" name="name" id="name" class="form-control" />
<br />
<label>Enter Email</label>
<input type="email" name="email" id="email" class="form-control" />
<br />
<label>Enter Phone</label>
<input type="text" name="phone" id="phone" 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-secondary" data-bs-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.13.2/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('#add_button').click(function(){
$('#member_form')[0].reset();
$('.modal-title').text("Add New 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 name = $('#name').val();
var email = $('#email').val();
if(name != '' && email != '')
{
$.ajax({
url:"insertupdated.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("Name, email Fields are Required");
}
});
$(document).on('click', '.update', function(){
var member_id = $(this).attr("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);
$('#name').val(data.name);
$('#email').val(data.email);
$('#phone').val(data.phone);
$('.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 user?"))
{
$.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=projectdb', 'root', '' ); ?>
//fetch.php
<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM member ";
if(isset($_POST["search"]["value"]))
{
$query .= 'WHERE name LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR email 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["name"];
$sub_array[] = $row["email"];
$sub_array[] = $row["phone"];
$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">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 member");
$statement->execute();
$result = $statement->fetchAll();
return $statement->rowCount();
}
?>
insertupdated.php
//insertupdated.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["operation"]))
{
if($_POST["operation"] == "Add")
{
$statement = $connection->prepare("
INSERT INTO member (name, email, phone) VALUES (:name, :email, :phone)");
$result = $statement->execute(
array(
':name' => $_POST["name"],
':email' => $_POST["email"],
':phone' => $_POST["phone"]
)
);
}
if($_POST["operation"] == "Edit")
{
$statement = $connection->prepare(
"UPDATE member
SET name = :name, email = :email, phone = :phone WHERE id = :id");
$result = $statement->execute(
array(
':name' => $_POST["name"],
':email' => $_POST["email"],
':phone' => $_POST["phone"],
':id' => $_POST["member_id"]
)
);
}
}
?>
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 member WHERE id = '".$_POST["member_id"]."' LIMIT 1"
);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$output["id"] = $row["id"];
$output["name"] = $row["name"];
$output["email"] = $row["email"];
$output["phone"] = $row["phone"];
}
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 member WHERE id = :id"
);
$result = $statement->execute(
array(':id' => $_POST["member_id"])
);
}
?>
