article

Saturday, June 18, 2022

PHP Mysqli CRUD (Create, Read, Update and Delete) using OOP

PHP Mysqli CRUD (Create, Read, Update and Delete) using OOP

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, 'clydety', 'Ednalan', 'Olongapo City'),
(2, 'Airi ', 'Satou', 'Tokyo'),
(13, 'Cairo', 'Ednalan', 'New Cabalan Olongapo City'),
(14, 'Angelica ', 'Ramos', 'London');

ALTER TABLE `members`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
  
Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/

Database Connection

DbConnection.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
//DbConnection.php
<?php
class DbConnection
{   
    private $host = 'localhost';
    private $username = 'root';
    private $password = '';
    private $database = 'devprojectdb';
     
    protected $connection;
     
    public function __construct(){
 
        if (!isset($this->connection)) {
             
            $this->connection = new mysqli($this->host, $this->username, $this->password, $this->database);
             
            if (!$this->connection) {
                echo 'Cannot connect to database server';
                exit;
            }           
        }   
         
        return $this->connection;
    }
}
?>
Database Action
Crud.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
//Crud.php
<?php
include_once('DbConnection.php');
  
class Crud extends DbConnection
{
    public function __construct(){
 
        parent::__construct();
    }
     
    public function read($sql){
 
        $query = $this->connection->query($sql);
         
        if ($query == false) {
            return false;
        }
         
        $rows = array();
         
        while ($row = $query->fetch_array()) {
            $rows[] = $row;
        }
         
        return $rows;
    }
         
    public function execute($sql){
 
        $query = $this->connection->query($sql);
         
        if ($query == false) {
            return false;
        } else {
            return true;
        }       
    }
     
    public function escape_string($value){
         
        return $this->connection->real_escape_string($value);
    }
}
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
//index.php
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>PHP Mysqli CRUD (Create, Read, Update and Delete) using OOP</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
    <h1 class="page-header text-center">PHP Mysqli CRUD (Create, Read, Update and Delete) using OOP</h1>
    <div class="row">
        <div class="col-12">
        <?php
            //start session
            session_start();
 
            include_once('Crud.php');
 
            $crud = new Crud();
 
            //fetch data
            $sql = "SELECT * FROM members";
            $result = $crud->read($sql);
 
            if(isset($_SESSION['message'])){
        ?>
                <div class="alert alert-info text-center">
                    <?php echo $_SESSION['message']; ?>
                </div>
            <?php
                unset($_SESSION['message']);
            } ?>
            <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#add">Add New</button>
            <br><br>
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Firstname</th>
                        <th>Lastname</th>
                        <th>Address</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                        foreach ($result as $key => $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 type="button" class="btn btn-success" data-bs-toggle="modal" data-bs-target="#edit<?php echo $row['id']; ?>">Edit</button> |
                                <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#delete<?php echo $row['id']; ?>">Delete</button> 
                                </td>
                                <?php include('action_modal.php'); ?>
                            </tr>
                            <?php    
                        }
                    ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
<?php include('add_modal.php'); ?>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js" rel="stylesheet"></script>
</body>
</html>
add_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
//add_modal.php
<!-- Add New -->
<div class="modal fade" id="add" tabindex="-1" aria-labelledby="addlabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="addlabel">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" action="add.php">
                <div class="row">
                    <div class="col-lg-2">
                        <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" class="form-control" name="firstname">
                    </div>
                </div>
                <div style="height:10px;"></div>
                <div class="row">
                    <div class="col-lg-2">
                        <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" class="form-control" name="lastname">
                    </div>
                </div>
                <div style="height:10px;"></div>
                <div class="row">
                    <div class="col-lg-2">
                        <label class="control-label" style="position:relative; top:7px;">Address:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" class="form-control" name="address">
                    </div>
                </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
        <button type="submit" name="add" class="btn btn-primary">Save</button>
            </form>
      </div>
    </div>
  </div>
</div>
action_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
//action_modal.php
<!-- Delete -->
<div class="modal fade" id="delete<?php echo $row['id']; ?>" 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="myModalLabel">Delete Member</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
            <h5>Are sure you want to delete</h5>
            <h2>Name: <b><?php echo $row['firstname'].' '.$row['lastname']; ?></b></h2>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
        <a href="delete.php?id=<?php echo $row['id']; ?>" class="btn btn-danger">Yes</a>
      </div>
    </div>
  </div>
</div>
 
<!-- Edit -->
<div class="modal fade" id="edit<?php echo $row['id']; ?>" 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="myModalLabel">Edit Member</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
            <form method="POST" action="edit.php?id=<?php echo $row['id']; ?>">
                <div class="row">
                    <div class="col-lg-2">
                        <label style="position:relative; top:7px;">Firstname:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" name="firstname" class="form-control" value="<?php echo $row['firstname']; ?>">
                    </div>
                </div>
                <div style="height:10px;"></div>
                <div class="row">
                    <div class="col-lg-2">
                        <label style="position:relative; top:7px;">Lastname:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" name="lastname" class="form-control" value="<?php echo $row['lastname']; ?>">
                    </div>
                </div>
                <div style="height:10px;"></div>
                <div class="row">
                    <div class="col-lg-2">
                        <label style="position:relative; top:7px;">Address:</label>
                    </div>
                    <div class="col-lg-10">
                        <input type="text" name="address" class="form-control" value="<?php echo $row['address']; ?>">
                    </div>
                </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
        <button type="submit" name="edit" class="btn btn-warning">Save</button>
        </form>
      </div>
    </div>
  </div>
</div>
add.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
//add.php
<?php
//start session
session_start();
 
include_once('Crud.php');
  
$crud = new Crud();
  
if(isset($_POST['add'])) {   
    $firstname = $crud->escape_string($_POST['firstname']);
    $lastname = $crud->escape_string($_POST['lastname']);
    $address = $crud->escape_string($_POST['address']);
         
    $sql = "INSERT INTO members (firstname, lastname, address) VALUES ('$firstname','$lastname','$address')";
 
    if($crud->execute($sql)){
        $_SESSION['message'] = 'Member added successfully';
    }
    else{
        $_SESSION['message'] = 'Cannot add member';
    }
         
    header('location: index.php');
}
else{
    $_SESSION['message'] = 'Fill up add form first';
    header('location: index.php');
}
?>
edit.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
//edit.php
<?php
//start session
session_start();
 
include_once('Crud.php');
 
$id = $_GET['id'];
  
$crud = new Crud();
  
if(isset($_POST['edit'])) {   
    $firstname = $crud->escape_string($_POST['firstname']);
    $lastname = $crud->escape_string($_POST['lastname']);
    $address = $crud->escape_string($_POST['address']);
         
    $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
 
    if($crud->execute($sql)){
        $_SESSION['message'] = 'Member updated successfully';
    }
    else{
        $_SESSION['message'] = 'Cannot update member';
    }
         
    header('location: index.php');
}
else{
    $_SESSION['message'] = 'Select user to edit first';
    header('location: index.php');
}
?>
delete.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
//delete.php
<?php
//start session
session_start();
 
include_once('Crud.php');
 
if(isset($_GET['id'])){
 
    //get id
    $id = $_GET['id'];
      
    $crud = new Crud();
 
    $sql = "DELETE FROM members WHERE id = '$id'";
 
    if($crud->execute($sql)){
        $_SESSION['message'] = 'Member deleted successfully';
    }
    else{
        $_SESSION['message'] = 'Cannot delete member';
    }
         
    header('location: index.php');
}
else{
    $_SESSION['message'] = 'Select user to delete first';
    header('location: index.php');
}
?>

Posted by ednalan at 1:02 AM . Under .

Related Post