CRUD operation in PHP and MySQL

Last Updated on Nov 06, 2023

What is CRUD?

CRUD is an acryonym for Create, Read, Update, and Delete. These are the four basic manipulations that we can do to our database. Every dynamic website should have these features.

In this tutorial, we will create a webpage that will implement a CRUD using PHP and MySQL alongside HTML, Bootstrap, and JavaScript. I used the Bootstrap modal in this tutorial, so if you decide to use CSS rather than Bootstrap, make sure to implement your own modal.

Furthermore, I will also show you how to do it in PDO and MySQLi (both in an object-oriented and procedural way). Just choose a method that you prefer.

Create the neccessary files

Before we write our code, let's create the following files: index.php, database.php, insert_name.php, select_name.php, script.js, update_name.php, and delete_name.php.

The index.php is our HTML page, which has an insert form, an update form, a delete form, and a table. The database.php file contains our database configuration and connection. insert_name.php, select_name.php, update_name.php, and delete_name.php contain our database manipulations for CREATE, READ, UPDATE, and DELETE (CRUD). Lastly, the script.js contains DOM manipulation that will set the ID and other inputs for the update and delete forms.

Create the CRUD page

Let's start by creating our HTML page. Type the following code in index.php:

<?php
require_once 'database.php';
require_once 'insert_name.php';
require_once 'update_name.php';
require_once 'delete_name.php';
require_once 'select_name.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 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 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 select_name.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.

Then create the database table that we will manipulate in this tutorial. 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

Now let's create a connection to the database that we previously created. Type the following code in database.php:

PDO

<?php
$host = 'localhost';
$dbname = 'crud';
$user = 'root';
$password = '';

$database = new PDO("mysql:host={$host};dbname={$dbname}", $user, $password);

MySQLi Object Oriented

<?php
$host = 'localhost';
$dbname = 'crud';
$user = 'root';
$password = '';

$database = new mysqli($host, $user, $password, $dbname);

MySQLi Procedural

<?php
$host = 'localhost';
$dbname = 'crud';
$user = 'root';
$password = '';

$database = mysqli_connect($host, $user, $password, $dbname);

When connecting to your database, make sure you have the correct configuration based on your server.

Create the insert_name action

Let's create the CREATE part of the CRUD. Type the following code in insert_name.php:

PDO

<?php
if(!empty($_POST['insert'])) {
  $statement = $database->prepare("INSERT INTO name (lastname, firstname, middlename, suffix) 
  VALUES (:lastname, :firstname, :middlename, :suffix) ");
  $statement->bindParam(':lastname', $_POST['lastname']);
  $statement->bindParam(':firstname', $_POST['firstname']);
  $statement->bindParam(':middlename', $_POST['middlename']);
  $statement->bindParam(':suffix', $_POST['suffix']);
  $statement->execute();
}

MySQLi Object Oriented

<?php
if(!empty($_POST['insert'])) {  
  $statement = $database->prepare("INSERT INTO name (lastname, firstname, middlename, suffix) 
  VALUES (?, ?, ?, ?) ");
  $statement->bind_param("ssss", $_POST['lastname'], $_POST['firstname'], $_POST['middlename'], $_POST['suffix']);
  $statement->execute();
}

MySQLi Procedural

<?php
if(!empty($_POST['insert'])) {  
  $statement = mysqli_prepare($database, "INSERT INTO name (lastname, firstname, middlename, suffix) 
  VALUES (?, ?, ?, ?) ");
  mysqli_stmt_bind_param($statement, "ssss", $_POST['lastname'], $_POST['firstname'], $_POST['middlename'], $_POST['suffix']);
  mysqli_stmt_execute($statement);
}

If you run and submit the form, it should already be inserted into your database. To make sure, check it out in your MySQL database.

Create the select_name page

Similar to insert_name.php, let's create the READ part of the CRUD. Type the following code in select_name.php:

PDO

<?php
$statement = $database->prepare("SELECT name_id, lastname, firstname, middlename, suffix 
FROM name");
$statement->execute();
$record = $statement->fetchAll();

MySQLi Object Oriented

<?php
$statement = $database->prepare("SELECT name_id, lastname, firstname, middlename, suffix 
FROM name");
$statement->execute();
$record = $statement->get_result()->fetch_all(MYSQLI_ASSOC);

MySQLi Procedural

<?php
$statement = mysqli_prepare($database, "SELECT name_id, lastname, firstname, middlename, suffix 
FROM name");
mysqli_stmt_execute($statement);
$record = mysqli_fetch_all(mysqli_stmt_get_result($statement), MYSQLI_ASSOC);

After refreshing the page, the warning and error must be gone now, and you should already see the table and the records that you previously submitted.

Create the javascript

In our table, when we click the edit button, you will notice that the value of the lastname, firstname, middlename, and suffix is not set in the update form inputs. This is where the javascript code comes in. We will create a code so that when the update and delete button is clicked, the necessary inputs will automatically be set in the update form and delete form, including the name_id, which is the identifier of our record.

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 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.

Create the update_name action

Now let's create the UPDATE part of the CRUD. Type the following code in update_name.php:

PDO

<?php
if(!empty($_POST['update'])) {
  $statement = $database->prepare("UPDATE name SET lastname = :lastname, 
  firstname = :firstname, middlename = :middlename, suffix = :suffix 
  WHERE name_id = :name_id");
  $statement->bindParam(':lastname', $_POST['lastname']);
  $statement->bindParam(':firstname', $_POST['firstname']);
  $statement->bindParam(':middlename', $_POST['middlename']);
  $statement->bindParam(':suffix', $_POST['suffix']);
  $statement->bindParam(':name_id', $_POST['name-id']);
  $statement->execute();
}

MySQLi Object Oriented

<?php
if(!empty($_POST['update'])) {  
  $statement = $database->prepare("UPDATE name SET lastname = ?, 
  firstname = ?, middlename = ?, suffix = ? WHERE name_id = ?");
  $statement->bind_param("ssssi", $_POST['lastname'], $_POST['firstname'], 
  $_POST['middlename'], $_POST['suffix'], $_POST['name-id']);
  $statement->execute();
}

MySQLi Procedural

<?php
if(!empty($_POST['update'])) {  
  $statement = mysqli_prepare($database, "UPDATE name SET lastname = ?, 
  firstname = ?, middlename = ?, suffix = ? WHERE name_id = ?");
  mysqli_stmt_bind_param($statement, "ssssi", $_POST['lastname'], $_POST['firstname'], 
  $_POST['middlename'], $_POST['suffix'], $_POST['name-id']);
  mysqli_execute($statement);
}

The update feature should now be working after clicking the save button in the update form.

Create the delete_name page

Lastly, let's create the DELETE part of the CRUD. Type the following code in delete_name.php:

PDO

<?php
if(!empty($_POST['delete'])) {
  $statement = $database->prepare("DELETE FROM name WHERE name_id = :name_id");
  $statement->bindParam(':name_id', $_POST['name-id']);
  $statement->execute();
}

MySQLi Object Oriented

<?php
if(!empty($_POST['delete'])) {  
  $statement = $database->prepare("DELETE FROM name WHERE name_id = ?");
  $statement->bind_param('i', $_POST['name-id']);
  $statement->execute();
}

MySQLi Procedural

<?php
if(!empty($_POST['delete'])) {
  $statement = mysqli_prepare($database, "DELETE FROM name WHERE name_id = ?");
  mysqli_stmt_bind_param($statement ,'i', $_POST['name-id']);
  mysqli_stmt_execute($statement);
}

And finally, the delete feature should now be working when you choose YES in the delete prompt.

Summary

In this tutorial, you have learned how to create a simple CRUD implementation using PHP and MySQL. In addition, you also learned how to use JavaScript to set the value of our update and delete forms. Furthermore, you also learned to do it in PDO and MySQLi.

CRUD operation in PHP and MySQL

What is CRUD?

Create the neccessary files

Create the CRUD page

Create Database crud and Table name

Connect the Database

PDO

MySQLi Object Oriented

MySQLi Procedural

Create the insert_name action

PDO

MySQLi Object Oriented

MySQLi Procedural

Create the select_name page

PDO

MySQLi Object Oriented

MySQLi Procedural

Create the javascript

Create the update_name action

PDO

MySQLi Object Oriented

MySQLi Procedural

Create the delete_name page

PDO

MySQLi Object Oriented

MySQLi Procedural

Summary

CRUD operation using Object Oriented in PHP and MySQL

Upload image file with PHP and MySQL

Create a Signup and Login System with PHP and MySQL