Object-Oriented CRUD System using PHP PDO and MySQL
Object-Oriented CRUD System using PHP PDO and MySQL
Database CRUD is one of a common functional pack that is required to manage and manipulate data of an entity based application. We have already seen basic CRUD example using PHP and MySQL. In this example, I have used the object-oriented method for implementing CRUD functionalities. I have created a card-like list view to display all the records from the database. I have used Bootstrap modal to show the edit form and the response messages. Also, the edit and delete actions are handled using jQuery AJAX.
I used a CRUD controller class consisting of the create, read, update and the delete functions. In this class, I have used Dao controller which dealt with the database connection establishment. I created the instance for the CrudController class to invoke its functions to perform CRUD operations. In the CRUD controller, I have created the Dao object to get the database connection object. I used PDO statement to perform the database CRUD operations.
Read and Display Card-Like List View
I have created PDO statement to fetch data from the database and store it into a PHP array. This array will be iterated to display the database results in a card-like list view. For each card, I have shown the Edit and Delete buttons to trigger the database update and delete actions via jQuery AJAX. The screenshot for the card-like list view is,
The jQuery AJAX functions are defined in the crudEvent.js JavaScript file that is imported in this list page HTML. This list page contains markup for the edit modal which is initially hidden and popped up by clicking the edit handle.
<?php
include_once 'CrudController.php';
$crudcontroller = new CrudController();
$result = $crudcontroller->readData();
?>
<html>
<head>
<title>CRUD</title>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script
src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
<script src="crudEvent.js"></script>
</head>
<body>
<div class="row">
<a href="add.php"><button class="btn btn-primary btn-add">Add
New Record</button></a>
</div>
<div class="row" id="container">
<?php
if (! empty($result)) {
foreach ($result as $k => $v) {
?>
<div class="box-container">
<div class="title">
<a href="<?php echo $result[$k]["url"]; ?>"><?php echo $result[$k]["title"]; ?></a>
</div>
<div class="description">
<?php echo $result[$k]["description"]; ?>
...
</div>
<div class="category">
<?php echo $result[$k]["category"]; ?>
</div>
<div class="action">
<button class="btn-action bn-edit"
id="<?php echo $result[$k]["id"]; ?>">Edit</button>
<button class="btn-action bn-delete"
id="<?php echo $result[$k]["id"]; ?>">Delete</button>
</div>
</div>
<?php
}
}
?>
</div>
</body>
</html>
Create New Record
This is the add form consisting of Title, Description, URL and Category fields. The user will enter their input for these fields. Then, the user data will be validated and passed to the PHP to create the PDO statement for the insert query.
<?php
if (isset($_POST["add"])) {
include_once 'CrudController.php';
$crudcontroller = new CrudController();
$result = $crudcontroller->add($_POST);
header("Location: index.php");
}
?>
<html>
<head>
<title>CRUD</title>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script
src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
<script src="crudEvent.js"></script>
</head>
<body>
<div class="row">
<div class="form-container">
<form method="POST">
<div class="form-group">
<div class="row">
<label>Title</label> <input type="text"
name="title" id="title" class="form-control"
required>
</div>
</div>
<div class="form-group">
<div class="row">
<label>Description</label>
<textarea class="form-control" id="description"
name="description" required></textarea>
</div>
</div>
<div class="form-group">
<div class="row">
<label>URL</label> <input type="text" name="url"
id="url" class="form-control" required>
</div>
</div>
<div class="form-group">
<div class="row">
<label>Category</label> <input type="text"
name="category" id="category"
class="form-control" required>
</div>
</div>
<div class="form-group">
<div class="row">
<button class="btn btn-primary" name="add">Submit</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
And the CrudController function to create the insert statement is,
public function add($formArray)
{
$title = $_POST['title'];
$description = $_POST['description'];
$url = $_POST['url'];
$category = $_POST['category'];
$dao = new Dao();
$conn = $dao->openConnection();
$sql = "INSERT INTO `tb_links`(`title`, `description`, `url`, `category`) VALUES ('" . $title . "','" . $description . "','" . $url . "','" . $category . "')";
$conn->query($sql);
$dao->closeConnection();
}
CURD: Update Delete jQuery AJAX function
This jQuery script shows the event handling functions for raising the request to perform the database update and delete actions. It calls PHP code to invoke the update or delete action as per the request. This request will be sent with the reference of the unique record id based on which the database update delete actions will be executed.
<script>
$(document).ready(function(){
$(document).on('click' , '.bn-edit' ,function(){
var id = this.id;
$.ajax({
url: 'read.php',
type: 'POST',
dataType: 'JSON',
data: {"id":id,"type":"single"},
success:function(response){
$("#edit-modal").modal('show');
$('#title').val(response.title);
$('#description').val(response.description);
$('#url').val(response.url);
$("#category").val(response.category);
$("#id").val(id);
}
});
});
$(document).on('click' , '#update' ,function(){
$.ajax({
url: 'edit.php',
type: 'POST',
dataType: 'JSON',
data: $("#frmEdit").serialize(),
success:function(response){
$("#messageModal").modal('show');
$("#msg").html(response);
$("#edit-modal").modal('hide');
loadData();
}
});
});
$(document).on('click' , '.bn-delete' ,function(){
if(confirm("Are you sure want to delete the record?")) {
var id = this.id;
$.ajax({
url: 'delete.php',
type: 'POST',
dataType: 'JSON',
data: {"id":id},
success:function(response){
loadData();
}
});
}
});
});
function loadData() {
$.ajax({
url: 'read.php',
type: 'POST',
data: {"type":"all"},
success:function(response){
$("#container").html(response);
}
});
}
</script>
And the CrudController functions to perform the update and the delete actions are,
<?php
/* Edit a Record */
public function edit($formArray)
{
$id = $_POST['id'];
$title = $_POST['title'];
$description = $_POST['description'];
$url = $_POST['url'];
$category = $_POST['category'];
$dao = new Dao();
$conn = $dao->openConnection();
$sql = "UPDATE tb_links SET title = '" . $title . "' , description='" . $description . "', url='" . $url . "', category='" . $category . "' WHERE id=" . $id;
$conn->query($sql);
$dao->closeConnection();
}
/* Delete a Record */
public function delete($id)
{
$dao = new Dao();
$conn = $dao->openConnection();
$sql = "DELETE FROM `tb_links` where id='$id'";
$conn->query($sql);
$dao->closeConnection();
}
?>
Add Edit Form Screenshot
Below screenshots shows the add form and the edit modal that is used in this object-oriented crud example.
Leave Comment