In my previous post, I showed you how to CRUD in PHP and MySQL. In that tutorial, we created a webpage that will manipulate the name table in a procedural way.
In this article, we will do the same thing but in an object-oriented way. We will create it using HTML, Bootstrap, JavaScript, PHP, and MySQL. Just like in my previous post, I will use a bootstrap modal in this tutorial, so if you use CSS instead of Bootstrap, make sure to implement your own modal.
Without further delay, let's get started.
-
Create the neccessary files
First things first, let's create the following files and understand their purpose: index.php, database.php, Name.php, name-action.php, and script.js.
The index.php contains our HTML page, which has an insert form, an edit form modal, a delete form modal, and a table that will contain the records. The database.php file contains our MySQL configuration and connection. The Name.php contains our Name class. The name-action.php is responsible for the CRUD operation that will make use of Name class. Finally, script.js contains the DOM manipulation that will set the ID and other inputs when the edit and delete buttons are clicked.
-
Create the CRUD page
Now let's create our HTML page. Type the following code in index.php
<?php require_once 'name-action.php'; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>CRUD operation using Object Oriented in PHP and MySQL</title> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN" crossorigin="anonymous"> </head> <body class="bg-body-tertiary"> <!-- Edit Modal - Start --> <div class="modal fade" id="edit-modal" tabindex="-1" aria-labelledby="edit-modal-label" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <h2 class="modal-title fs-4" id="edit-modal-label">Edit Name</h2> <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button> </div> <div class="modal-body"> <form method="post"> <input type="hidden" name="name-id" id="edit-name-id"> <div class="py-2"> <label class="fw-medium">Last Name</label> <input class="form-control" type="text" name="lastname" id="edit-lastname"> </div> <div class="py-2"> <label class="fw-medium">First Name</label> <input class="form-control" type="text" name="firstname" id="edit-firstname"> </div> <div class="py-2"> <label class="fw-medium">Middle Name</label> <input class="form-control" type="text" name="middlename" id="edit-middlename"> </div> <div class="py-2"> <label class="fw-medium">Suffix</label> <input class="form-control" type="text" name="suffix" id="edit-suffix"> </div> <div class="py-2"> <input class="btn btn-primary" type="submit" name="update" value="Save"> </div> </form> </div> </div> </div> </div> <!-- Edit Modal - End --> <!-- Delete Modal - Start --> <div class="modal fade" id="delete-modal" tabindex="-1" aria-labelledby="delete-modal-label" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <h2 class="modal-title fs-4" id="delete-modal-label">Are you sure you want to delete this name?</h2> </div> <div class="modal-body"> <form method="post"> <input type="hidden" name="name-id" id="delete-name-id"> <div class="row justify-content-around" > <input class="col-4 btn btn-danger" type="submit" name="delete" value="Yes"> <button class="col-4 btn btn-secondary" type="button" data-bs-dismiss="modal" aria-label="Close">No</button> </div> </form> </div> </div> </div> </div> <!-- Delete Modal - End --> <div class="container mt-3"> <!-- Insert Form - Start --> <div class="card"> <h1 class="card-header">CRUD operation using Object Oriented in PHP and MySQL</h1> <div class="card-body"> <form class="row" method="post"> <div class="col-md-6 py-2"> <label class="fw-medium" for="lastname">Last Name</label> <input class="form-control" type="text" name="lastname" id="lastname"> </div> <div class="col-md-6 py-2"> <label class="fw-medium" for="firstname">First Name</label> <input class="form-control" type="text" name="firstname" id="firstname"> </div> <div class="col-md-6 py-2"> <label class="fw-medium" for="middlename">Middle Name (Optional)</label> <input class="form-control" type="text" name="middlename" id="middlename"> </div> <div class="col-md-6 py-2"> <label class="fw-medium" for="suffix">Suffix (Optional)</label> <input class="form-control" type="text" name="suffix" id="suffix"> </div> <div class="py-2"> <input class="btn btn-primary" type="submit" name="insert" value="Submit"> </div> </form> </div> </div> <!-- Insert Form - End --> <!-- Name Table - Start --> <div class="card mt-3"> <div class="card-body table-responsive"> <table class="table table-bordered table-striped"> <thead> <tr> <th scope="col">ID</th> <th scope="col">Last Name</th> <th scope="col">First Name</th> <th scope="col">Middle Name</th> <th scope="col">Suffix</th> <th scope="col">Action</th> </tr> </thead> <tbody> <?php if(count($record)): $counter = 0; ?> <?php foreach($record as $row): $counter++; ?> <tr id="action-tr-<?= $counter ?>"> <th scope="row"><?= $row['name_id']; ?></th> <td><?= htmlspecialchars($row['lastname'], ENT_QUOTES); ?></td> <td><?= htmlspecialchars($row['firstname'], ENT_QUOTES); ?></td> <td><?= htmlspecialchars($row['middlename'], ENT_QUOTES); ?></td> <td><?= htmlspecialchars($row['suffix'], ENT_QUOTES); ?></td> <td> <button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#edit-modal">Edit</button> <button class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#delete-modal">Delete</button> </td> </tr> <?php endforeach; ?> <?php else: ?> <tr><td colspan="6" class="text-center">No record yet</td></tr> <?php endif; ?> </tbody> </table> </div> </div> <!-- Name Table - End --> </div> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-C6RzsynM9kWDrMNeT87bh95OGNyZPhcTNXj1NW7RuBCsyN/o0jlpcV8Qyq46cDfL" crossorigin="anonymous"></script> <script src="script.js"></script> </body> </html>If you run the code above, you will see an insert form and a table. In the table, there will be a warning and an error when fetching the table contents, but don't worry about it; it will go away when we get to the name-action.php part.
In addition to the insert form and the table, we also have the update form modal and the delete form modal, which are shown when the edit and delete buttons are clicked in our table.
-
Create Database crud and Table name
Run the following SQL code either in the phpMyAdmin in SQL tab or on the MySQL Command Line Client.
CREATE DATABASE crud; use crud; CREATE TABLE name ( name_id int(11) NOT NULL AUTO_INCREMENT, lastname varchar(100) NOT NULL, firstname varchar(100) NOT NULL, middlename varchar(100) NOT NULL, suffix varchar(10) NOT NULL, PRIMARY KEY (name_id) );
-
Connect the Database
Type the following code in database.php.
<?php $host = 'localhost'; $dbname = 'crud'; $user = 'root'; $password = ''; $database = new PDO("mysql:host={$host};dbname={$dbname}", $user, $password);When connecting to your database, make sure you have the correct configuration based on your MySQL server.
-
Create the class Name
Type the following code inside Name.php:
<?php class Name { private $database; function __construct($database) { $this->database = $database; } function select() { $statement = $this->database->prepare("SELECT name_id, lastname, firstname, middlename, suffix FROM name"); $statement->execute(); $record = $statement->fetchAll(); return $record; } function insert($data) { $statement = $this->database->prepare("INSERT INTO name (lastname, firstname, middlename, suffix) VALUES (:lastname, :firstname, :middlename, :suffix) "); $statement->bindParam(':lastname', $data['lastname']); $statement->bindParam(':firstname', $data['firstname']); $statement->bindParam(':middlename', $data['middlename']); $statement->bindParam(':suffix', $data['suffix']); $statement->execute(); } function update($data) { $statement = $this->database->prepare("UPDATE name SET lastname = :lastname, firstname = :firstname, middlename = :middlename, suffix = :suffix WHERE name_id = :name_id"); $statement->bindParam(':lastname', $data['lastname']); $statement->bindParam(':firstname', $data['firstname']); $statement->bindParam(':middlename', $data['middlename']); $statement->bindParam(':suffix', $data['suffix']); $statement->bindParam(':name_id', $data['nameId']); $statement->execute(); } function delete($nameId) { $statement = $this->database->prepare("DELETE FROM name WHERE name_id = :name_id"); $statement->bindParam(':name_id', $nameId); $statement->execute(); } }In our Name class above, we have a private field named $database, which is the variable for our database connection. The $database field is set in our __construct() method.
In addition, we have four methods for doing the CRUD. select() for getting all the records, insert() for inserting a record, update() for updating a record, and delete() for deleting a record in the database. Both insert() and update() method accepts associative array for parameter while the delete() method only accepts an id.
-
Create the name-action
Now let's make our CRUD page work. Type the following code in name-action.php.
<?php require_once 'database.php'; require_once 'Name.php'; $name = new Name($database); if(!empty($_POST['insert'])) { $name->insert([ 'lastname' => $_POST['lastname'], 'firstname' => $_POST['firstname'], 'middlename' => $_POST['middlename'], 'suffix' => $_POST['suffix'] ]); } if(!empty($_POST['update'])) { $name->update([ 'lastname' => $_POST['lastname'], 'firstname' => $_POST['firstname'], 'middlename' => $_POST['middlename'], 'suffix' => $_POST['suffix'], 'nameId' => $_POST['name-id'] ]); } if(!empty($_POST['delete'])) { $name->delete($_POST['name-id']); } $record = $name->select();If you refresh the CRUD page, the warning and error should be gone now and you can insert and view the records.
-
Create the script.js
Now let's make the update and delete buttons work. Type the following code in script.js.
let counter = 1; let actionTr = document.getElementById('action-tr-'+counter); while(actionTr != null) { const editBtn = actionTr.children[5].firstElementChild; const deleteBtn = actionTr.children[5].lastElementChild; editBtn.addEventListener('click', handleEditClick); deleteBtn.addEventListener('click', handleDeleteClick); counter++; actionTr = document.getElementById('action-tr-'+counter); } function handleEditClick(e) { const tr = e.target.parentElement.parentElement; const nameId = tr.children[0].innerHTML; const lastname = tr.children[1].innerHTML; const firstname = tr.children[2].innerHTML; const middlename = tr.children[3].innerHTML; const suffix = tr.children[4].innerHTML; setEditForm(nameId, lastname, firstname, middlename, suffix); } function handleDeleteClick(e) { const tr = e.target.parentElement.parentElement; const nameId = tr.children[0].innerHTML; setDeleteForm(nameId); } function setEditForm(nameId, lastname, firstname, middlename, suffix) { const editIdInput = document.getElementById('edit-name-id'); const editLastnameInput = document.getElementById('edit-lastname'); const editFirstnameInput = document.getElementById('edit-firstname'); const editMiddlenameInput = document.getElementById('edit-middlename'); const editSuffixInput = document.getElementById('edit-suffix'); editIdInput.value = nameId; editLastnameInput.value = lastname; editFirstnameInput.value = firstname; editMiddlenameInput.value = middlename; editSuffixInput.value = suffix; } function setDeleteForm(nameId) { const deleteIdInput = document.getElementById('delete-name-id'); deleteIdInput.value = nameId; }In our code above, we used a loop statement to get all the edit and delete buttons and then added handleEditClick() and handleDeleteClick() functions for the buttons' click listeners. In those 2 functions, we also use the setEditForm() and setDeleteFrom() functions inside.
After hard refreshing the page, the update form and delete form should now be setting the value of the inputs when the edit and delete buttons are clicked. Additionaly, the edit and delete features should work now.
Summary
In this tutorial, we have created a simple implementation of CRUD using PHP and MySQL in an object-oriented style.
Using HTML and Bootstrap, we created a CRUD page that contains insert form, update form modal, delete form modal, and a table for the records viewing. Then we created a Name class, in which we use in name-action.php. Finally, using the JavaScript, we set the value of inputs in both update form and delete form.
