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