article

Thursday, March 10, 2022

PHP OOP Mysqli CRUD (Create Read Update and Delete) Jquery AJAX with Bootstrap 5 Modal

PHP OOP Mysqli CRUD (Create Read Update and Delete) Jquery AJAX with Bootstrap 5 Modal

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

Jquery
https://jquery.com/download/
CDN : jsDelivr CDN
https://www.jsdelivr.com/package/npm/jquery
https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

Bootstrap icons
https://icons.getbootstrap.com/#install
https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `address` varchar(150) 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, 'Caesar', 'Vance', '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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
//index.php
<!DOCTYPE html>
<html>
<head>
    <title>PHP OOP Mysqli CRUD (Create Read Update and Delete) Jquery AJAX</title>
    <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" />
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css">
</head>
<body>
    <body>
<div class="container">
    <div class="row"><p><h1>PHP OOP Mysqli CRUD (Create Read Update and Delete) Jquery AJAX with Bootstrap 5 Modal</h1></p></div>
    <div style="margin-left:auto; margin-right:auto; padding:auto; width:70%;">
        <p class="pull-right"><a id="add" style="cursor:pointer;" class="btn btn-primary"><i class="bi bi-clipboard2-plus-fill"></i> Add New</a></p>
 
        <div id="table"></div>
        <div id="alert" class="alert alert-success" style="display:none;">
            <center><span id="alerttext"></span></center>
        </div>
    </div>
<?php include('modal.php'); ?>
</div>
<script>
$(document).ready(function(){
    showTable();
 
    //add
    $('#add').click(function(){
        $('#addnew').modal('show');
        $('#addForm')[0].reset();
    });
 
    $('#addbutton').click(function(){
        var first = $('#firstname').val();
        var last = $('#lastname').val();
        var address = $('#address').val();
        if(first!='' && last!==''){
            var addForm = $('#addForm').serialize();
            $.ajax({
                type: 'POST',
                url: 'add.php',
                data: addForm,
                success:function(){
                    $('#addnew').modal('hide');
                    $('#addForm')[0].reset();
                    showTable();
                    $('#alert').slideDown();
                    $('#alerttext').text('Member Added Successfully');
                }
            });
        }
        else{
            alert('Please input both Fields')
        }
         
    });
    //
    //edit
    $(document).on('click', '.edit', function(){
        var id = $(this).data('id');
        var first = $('#first'+id).text();
        var last = $('#last'+id).text();
        var address = $('#address'+id).text();
        $('#editmem').modal('show');
        $('#eid').val(id);
        $('#efirstname').val(first);
        $('#elastname').val(last);
        $('#eaddress').val(address);
    });
 
    $('#editbutton').click(function(){
        var id = $('#eid').val(); //alert(id);
        var editForm = $('#editForm').serialize();
        $.ajax({
            type: 'POST',
            url: 'edit.php',
            data: editForm + "&id="+id,
            success:function(response){
                console.log(response);
                $('#editmem').modal('hide');
                $('#editForm')[0].reset();
                showTable();
                $('#alert').slideDown();
                $('#alerttext').text('Member Updated Successfully');
            }
        });
    });
    //
    //delete
    $(document).on('click', '.delete', function(){
        var id = $(this).data('id');
        var first = $('#first'+id).text();
        $('#delmem').modal('show');
        $('#dfirstname').text(first);
        $('#delbutton').val(id);
    });
 
    $('#delbutton').click(function(){
        var id = $(this).val();
        $.ajax({
            type: 'POST',
            url: 'delete.php',
            data: {
                id: id,
            },
            success:function(){
                $('#delmem').modal('hide');
                showTable();
                $('#alert').slideDown();
                $('#alerttext').text('Member Deleted Successfully');
            }
        });
    });
 
});
 
function showTable(){
    $.ajax({
        type: 'POST',
        url: 'fetch.php',
        data: {
            fetch: 1
        },
        success:function(data){
            $('#table').html(data);
        }
    });
}
</script>
</body>
</html>
conn.php
?
1
2
3
4
5
6
7
8
//conn.php
<?php
$conn = new mysqli("localhost", "root", "", "testingdb");
  
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>
fetch.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
//fetch.php
<?php
    include('conn.php');
    if(isset($_POST['fetch'])){
        ?>
        <table class="table table-striped table-bordered table-hover">
            <thead>
                <th>Firstname</th>
                <th>Lastname</th>
                <th>Address</th>
                <th>Action</th>
            </thead>
            <tbody>
            <?php
                $query=$conn->query("select * from members");
                while($row=$query->fetch_array()){
                    ?>
                    <tr>
                        <td><span id="first<?php echo $row['id']; ?>"><?php echo $row['firstname']; ?></span></td>
                        <td><span id="last<?php echo $row['id']; ?>"><?php echo $row['lastname']; ?></span></td>
                        <td><span id="address<?php echo $row['id']; ?>"><?php echo $row['address']; ?></span></td>
                        <td>
                            <a style="cursor:pointer;" class="btn btn-warning edit" data-id="<?php echo $row['id']; ?>"><i class="bi bi-pencil"></i> Edit</a> ||
                            <a style="cursor:pointer;" class="btn btn-danger delete" data-id="<?php echo $row['id']; ?>"><i class="bi bi-trash"></i> Delete</a>
                        </td>
                    </tr>
                    <?php
                }
             
            ?>
            </tbody>
        </table>
        <?php
    }
?>
add.php
?
1
2
3
4
5
6
7
8
9
10
11
//add.php
<?php
    include('conn.php');
    if(isset($_POST['firstname'])){
        $firstname=$_POST['firstname'];
        $lastname=$_POST['lastname'];
        $address=$_POST['address'];
 
        $conn->query("insert into members (firstname, lastname, address) values ('$firstname', '$lastname', '$address')");
    }
?>
modal.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
//modal.php
<!-- Add New -->
    <div class="modal fade" id="addnew" tabindex="-1" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                  <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Add New Member</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                  </div>
                <div class="modal-body">
                <div class="container-fluid">
                <form id="addForm">
                    <div class="row">
                        <div class="col-md-2">
                            <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="firstname" id="firstname">
                        </div>
                    </div>
                    <div style="height:10px;"></div>
                    <div class="row">
                        <div class="col-md-2">
                            <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="lastname" id="lastname">
                        </div>
                    </div>               
                    <div style="height:10px;"></div>
                    <div class="row">
                        <div class="col-md-2">
                            <label class="control-label" style="position:relative; top:7px;">Address:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="address" id="address">
                        </div>
                    </div>
                </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" id="addbutton" class="btn btn-primary"> Save</a>
                </form>
                </div>
                 
            </div>
        </div>
    </div>
 
<!-- Edit -->
    <div class="modal fade" id="editmem" tabindex="-1" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Edit Member</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                <div class="container-fluid">
                <form id="editForm">
                    <div class="row">
                        <div class="col-md-2">
                            <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="efirstname" id="efirstname">
                        </div>
                    </div>
                    <div style="height:10px;"></div>
                    <div class="row">
                        <div class="col-md-2"><input type="hidden" name="eid" id="eid">
                            <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="elastname" id="elastname">
                        </div>
                    </div>                   
                    <div style="height:10px;"></div>
                    <div class="row">
                        <div class="col-md-2">
                            <label class="control-label" style="position:relative; top:7px;">Address:</label>
                        </div>
                        <div class="col-md-10">
                            <input type="text" class="form-control" name="eaddress" id="eaddress">
                        </div>
                    </div>
                </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" id="editbutton" class="btn btn-warning"> Update</a>
                </form>
                </div>
                 
            </div>
        </div>
    </div>
 
<!-- Delete -->
    <div class="modal fade" id="delmem" tabindex="-1" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Delete Member</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                <div class="container-fluid">
                    <h5><center>Firstname: <strong><span id="dfirstname"></span></strong></center></h5>
                </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" id="delbutton" class="btn btn-danger"> Delete</button>
                </div>
                 
            </div>
        </div>
    </div>
edit.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//edit.php
<?php
    include('conn.php');
    if(isset($_POST['efirstname'])){
        $firstname=$_POST['efirstname'];
        $lastname=$_POST['elastname'];
        $address=$_POST['eaddress'];
        $id=$_POST['id'];
 
        $conn->query("update members set firstname='$firstname', lastname='$lastname', address='$address' where id='$id'");
 
        $response[] = array("firstname"=>$firstname,"lastname" => $lastname,"address" => $address,"member_id" => $id);
        echo json_encode($response);
    }
?>
delete.php
?
1
2
3
4
5
6
7
8
9
//delete.php
<?php
    include('conn.php');
    if(isset($_POST['id'])){
        $id=$_POST['id'];
 
        $conn->query("delete from members where id='$id'");
    }
?>

Posted by ednalan at 9:37 PM . Under , .

Related Post