Bootstrap 4 CDN
https://getbootstrap.com/docs/4.3/getting-started/introduction/
https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css
https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js
Modal
https://getbootstrap.com/docs/4.3/components/modal/
jquery
https://releases.jquery.com/jquery/
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');
ALTER TABLE `members`
ADD PRIMARY KEY (`id`);
ALTER TABLE `members`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
//index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<div class="container">
<h1 class="page-header text-center">PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal</h1>
<div class="row">
<div class="col-12">
<button id="addnew" class="btn btn-primary"><span class="glyphicon glyphicon-plus"></span> Add New Member</button>
<div id="alert" class="alert alert-info text-center" style="margin-top:20px; display:none;">
<button class="close"><span aria-hidden="true">×</span></button>
<span id="alert_message"></span>
</div>
<table class="table table-bordered table-striped" style="margin-top:20px;">
<thead>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th width="150">Action</th>
</thead>
<tbody id="tbody"></tbody>
</table>
</div>
</div>
</div>
<!-- Modals -->
<?php include('modal.html'); ?>
<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="script.js"></script>
</body>
</html>
modal.html
//modal.html
<!-- Add New -->
<div class="modal fade" id="add" tabindex="-1" role="dialog">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Add New</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<form id="addForm">
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Firstname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="firstname">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Lastname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="lastname">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Address:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control" name="address">
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
<button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</a>
</form>
</div>
</div>
</div>
</div>
<!-- Edit -->
<div class="modal fade" id="edit" tabindex="-1" role="dialog">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Edit Member</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<form id="editForm">
<input type="hidden" class="id" name="id">
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Firstname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control firstname" name="firstname">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Lastname:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control lastname" name="lastname">
</div>
</div>
<div class="row form-group">
<div class="col-sm-2">
<label class="control-label" style="position:relative; top:7px;">Address:</label>
</div>
<div class="col-sm-10">
<input type="text" class="form-control address" name="address">
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
<button type="submit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
</form>
</div>
</div>
</div>
</div>
<!-- Delete -->
<div class="modal fade" id="delete" tabindex="-1" role="dialog">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Delete Member</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<p class="text-center">Are you sure you want to Delete</p>
<h2 class="text-center fullname"></h2>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
<button type="button" class="btn btn-danger id"><span class="glyphicon glyphicon-trash"></span> Yes</button>
</div>
</div>
</div>
</div>
script.js
//script.js
$(document).ready(function(){
fetch();
//add
$('#addnew').click(function(){
$('#add').modal('show');
});
$('#addForm').submit(function(e){
e.preventDefault();
var addform = $(this).serialize();
//console.log(addform);
$.ajax({
method: 'POST',
url: 'add.php',
data: addform,
dataType: 'json',
success: function(response){
$('#add').modal('hide');
if(response.error){
$('#alert').show();
$('#alert_message').html(response.message);
}
else{
$('#alert').show();
$('#alert_message').html(response.message);
fetch();
}
}
});
});
//
//edit
$(document).on('click', '.edit', function(){
var id = $(this).data('id');
getDetails(id);
$('#edit').modal('show');
});
$('#editForm').submit(function(e){
e.preventDefault();
var editform = $(this).serialize();
$.ajax({
method: 'POST',
url: 'edit.php',
data: editform,
dataType: 'json',
success: function(response){
if(response.error){
$('#alert').show();
$('#alert_message').html(response.message);
}
else{
$('#alert').show();
$('#alert_message').html(response.message);
fetch();
}
$('#edit').modal('hide');
}
});
});
//
//delete
$(document).on('click', '.delete', function(){
var id = $(this).data('id');
getDetails(id);
$('#delete').modal('show');
});
$('.id').click(function(){
var id = $(this).val();
$.ajax({
method: 'POST',
url: 'delete.php',
data: {id:id},
dataType: 'json',
success: function(response){
if(response.error){
$('#alert').show();
$('#alert_message').html(response.message);
}
else{
$('#alert').show();
$('#alert_message').html(response.message);
fetch();
}
$('#delete').modal('hide');
}
});
});
//
//hide message
$(document).on('click', '.close', function(){
$('#alert').hide();
});
});
function fetch(){
$.ajax({
method: 'POST',
url: 'fetch.php',
success: function(response){
$('#tbody').html(response);
}
});
}
function getDetails(id){
$.ajax({
method: 'POST',
url: 'fetch_row.php',
data: {id:id},
dataType: 'json',
success: function(response){
if(response.error){
$('#edit').modal('hide');
$('#delete').modal('hide');
$('#alert').show();
$('#alert_message').html(response.message);
}
else{
$('.id').val(response.data.id);
$('.firstname').val(response.data.firstname);
$('.lastname').val(response.data.lastname);
$('.address').val(response.data.address);
$('.fullname').html(response.data.firstname + ' ' + response.data.lastname);
}
}
});
}
db.php
//db.php
<?php
Class connDB{
private $server = "mysql:host=localhost;dbname=testingdb";
private $username = "root";
private $password = "";
private $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,);
protected $conn;
public function open(){
try{
$this->conn = new PDO($this->server, $this->username, $this->password, $this->options);
return $this->conn;
}
catch (PDOException $e){
echo "There is some problem in connection: " . $e->getMessage();
}
}
public function close(){
$this->conn = null;
}
}
?>
fetch.php
//fetch.php
<?php
include_once('db.php');
$database = new connDB();
$db = $database->open();
try{
$sql = 'SELECT * FROM members';
foreach ($db->query($sql) as $row) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['address']; ?></td>
<td>
<button class="btn btn-success btn-sm edit" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-edit"></span> Edit</button>
<button class="btn btn-danger btn-sm delete" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-trash"></span> Delete</button>
</td>
</tr>
<?php
}
}
catch(PDOException $e){
echo "There is some problem in connection: " . $e->getMessage();
}
//close connection
$database->close();
?>
add.php
//add.php
<?php
include_once('db.php');
$output = array('error' => false);
$database = new connDB();
$db = $database->open();
try{
//use of prepared statement to prevent sql injection
$stmt = $db->prepare("INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
//if-else statement executing prepared statement
if ($stmt->execute(array(':firstname' => $_POST['firstname'] , ':lastname' => $_POST['lastname'] , ':address' => $_POST['address'])) ){
$output['message'] = 'Member added successfully';
}
else{
$output['error'] = true;
$output['message'] = 'Something went wrong. Cannot add member';
}
}
catch(PDOException $e){
$output['error'] = true;
$output['message'] = $e->getMessage();
}
//close connection
$database->close();
echo json_encode($output);
?>
edit.php
//edit.php
<?php
include_once('db.php');
$output = array('error' => false);
$database = new connDB();
$db = $database->open();
try{
$id = $_POST['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
//if-else statement executing query
if($db->exec($sql)){
$output['message'] = 'Member updated successfully';
}
else{
$output['error'] = true;
$output['message'] = 'Something went wrong. Cannot update member';
}
}
catch(PDOException $e){
$output['error'] = true;
$output['message'] = $e->getMessage();
}
//close connection
$database->close();
echo json_encode($output);
?>
fetch_row.php
//fetch_row.php
<?php
include_once('db.php');
$output = array('error' => false);
$database = new connDB();
$db = $database->open();
try{
$id = $_POST['id'];
$stmt = $db->prepare("SELECT * FROM members WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->execute();
$output['data'] = $stmt->fetch();
}
catch(PDOException $e){
$output['error'] = true;
$output['message'] = $e->getMessage();
}
//close connection
$database->close();
echo json_encode($output);
?>
delete.php
//delete.php
<?php
include_once('db.php');
$output = array('error' => false);
$database = new connDB();
$db = $database->open();
try{
$sql = "DELETE FROM members WHERE id = '".$_POST['id']."'";
//if-else statement executing query
if($db->exec($sql)){
$output['message'] = 'Member deleted successfully';
}
else{
$output['error'] = true;
$output['message'] = 'Something went wrong. Cannot delete member';
}
}
catch(PDOException $e){
$output['error'] = true;
$output['message'] = $e->getMessage();;
}
//close connection
$database->close();
echo json_encode($output);
?>
