PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

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

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

//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"])
         
    );
}
?>

Tutorial101

article

Friday, December 17, 2021

PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

Related Post

Search This Blog

HOME

Category

Popular Posts