How to Implement CRUD Operations in Your Application Using PHP and MySQL

Almost every website on the internet has some sort of a Create, Read, Update and Delete feature. These are some of the basic features one tends to find on any website from websites like Facebook, Twitter, Tiktok and Instagram to smaller sites.

This post is a tutorial on how you can implement CRUD operations on your website or app using PHP. We are going to be building a one-pager registration app where you can add, read, update and delete personal info(name and email). This info will be saved in a database of course.

Our final application at the end of the tutorial is going to look like this.

Prerequisites

To be able to understand what is being done in this tutorial, you are going to need the following;

Basic knowledge of PHP and OOP in PHP.
Basic knowledge of HTML and bootstrap(a CSS framework).
Basic knowledge of SQL because we are going to store user data in a MySQL database.
Basic knowledge of HTTP.

You will also have to install the following applications;

PHP. Beginners are advised to install PHP and its web server bundled together, this can be achieved by installing PHP using XAMPP or MAMP. I am using MAMP because it works well on Mac as it is built for macOS. If your computer is running on Windows download and install XAMPP instead. (You can check out my article on how to install XAMPP here). Download MAMP from its official website.
MySQL. In this tutorial, we are going to be using a service called phpMyAdmin that comes with XAMPP and MAMP to administer our database. It is easier to use compared to running MySQL commands on the command line.

Now that you have all the required software installed, let's get started.

The first thing is to create a folder for our tutorial product. Go to the drive on your computer where your have MAMP or XAMPP installed, open up the XAMPP or MAMP application folder, inside this folder you'd find an htdocs folder. Create your project folder inside this htdocs folder. I'm going to call mine php_crud_tutorial.
After doing that open MAMP and start the Apache web server.
Creating a database
The next thing we are going to do before we start writing any code is to create a database for our user data. Like I said before we'll be using phpMyAdmin to create our database. Mine runs on port 8889.

Screenshot 2021-03-11 at 13.39.12.png

To create a new database click on the new link in the top left hand corner of phpMyAdmin homepage. We are going to name our database php_crud_tutorial.
In our newly created database, create a new table called registered_users with the following four columns which are; id(whose values will be auto incremented), name, email and date(whose default value will be the current timestamp)

Screenshot 2021-03-11 at 13.48.35.png

Writing Code

After haven created our database and a table in it to store our user data we can start writing some code. Open your project folder in your favourite code editor(I'm using VSCode) and create the following php files in it.

DatabaseConnection.php
index.php
insert.php
select.php
update.php
delete.php

Screenshot 2021-03-11 at 14.04.54.png

The first thing we have to do is to create a connection to our database as all CRUD operations will be performed on the database. We are going use an OOP approach to achieve this. Using an OOP approach stops us from having to repeat ourselves by creating a new database connection every time we want to carry out an operation on the database. We are going to create a DatabaseConnection class that will contain all the properties and methods required for any database operation we need to perform. So add the following code to your DatabaseConnection.php.

<?php
class DatabaseConnection {
    protected $db_host;
    protected $db_username;
    protected $db_password;
    protected $db_databasename;
    protected $db_port;
    protected $db_socket;
    protected $mysqli;

    function __construct() {
        $this->db_host = 'localhost';
        $this->db_username = 'root';
        $this->db_password =  'root';
        $this->db_databasename = 'php_crud_tutorial';
        $this->db_port = 8889;
        $this->db_socket = '/Applications/MAMP/tmp/mysql/mysql.sock';
        $this->db_connect();
    }

    private function db_connect() {
        $this->mysqli = new mysqli($this->db_host, $this->db_username, $this->db_password, $this->db_databasename, $this->db_port, $this->db_socket);
        if ($this->mysqli->connect_error) {
            die('Connection Failed' . $this->mysqli->connect_error);
        }
    }

    function selectData($db_table, $column) {
        $this->db_connect();
        $sql = "SELECT * FROM " . $db_table . " ORDER BY " . $column;
        $sql = $this->mysqli->query($sql);

        return $sql;
    }

    function selectSingleRecord($db_table, $id) {
        $this->db_connect();
        $sql = "SELECT * FROM " . $db_table . " WHERE id = " . $id;
        $sql = $this->mysqli->query($sql);

        return $sql;
    }

    function updateData($value1, $value2, $value3) {
        $this->db_connect();
        $sql =
        sprintf(
            "UPDATE registered_users SET name = '%s', email = '%s' WHERE id = %d",
            $this->mysqli->real_escape_string($value1),
            $this->mysqli->real_escape_string($value2),
            $value3
        );

        $sql = $this->mysqli->query($sql);

        if ($sql === true) {
            return "Data Updated";
        } else {
            return "FAILED to execute update query";
        }
    }

    function insertData($value1, $value2 ) {
        $this->db_connect();
       $sql = sprintf(
            "INSERT INTO registered_users (name, email) VALUES ('%s', '%s')",
            $this->mysqli->real_escape_string($value1),
            $this->mysqli->real_escape_string($value2)
        );
        $sql = $this->mysqli->query($sql);

        if($sql === true) {
            return $sql;
        } else {
            return "FAILED to execute INSERT query";
        }
    }

    function deleteData($db_table, $condition) {
        $this->db_connect();
        $sql = "DELETE FROM " . $db_table . " WHERE id = " . $condition;
        $sql = $this->mysqli->query($sql);

        if($sql === true) {
            return "Data deleted successfully";
        } else {
            return "FAILED to execute DELETE query";
        }
    } 
}

What the code above does is that it creates a private function that creates a new connection to the database whenever it is called. So we call this function in all the other functions that will be used for database operations. Note that you have to change the database credentials as required.

Note: It is not advisable to always include you database credentials directly like this. It is much better to isolate(hide) credentials in a .env file and use it when needed to connect to the database.

The next file we are going to add code to is the insert.php which is going to have a form with two input fields for name and email as well as some PHP for submitting and validating the form. Add the code below to your insert.php file.

<?php
$name = '';
$email = '';
$form_error_message = "<div class='alert alert-danger'>All Fields are required</div>";

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $ok = true;
    if (empty($_POST['name'])) {
        $ok = false;
        echo $form_error_message;
    } else {
        $name = $_POST['name'];
    }

    if (empty($_POST['email'])) {
        $ok = false;
        echo $form_error_message;
    } else {
        $email = $_POST['email'];
    }

    if ($ok === true) {
        //create new database instance so we can insert data into database
        $newInsertDatabaseInstance = new DatabaseConnection();
        $newInsertDatabaseInstance->insertData($name, $email);
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP CRUD Tutorial</title>
    <!-- Bootstrap CDN --->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
</head>

<body>
    <div class="container my-4">
    <h1 class="text-center">Registration Form</h1>
        <form action="" method="post">
            <div class="form-group">
                <label for="name" class="py-2">Name</label>
                <input name="name" type="text" class="form-control my-3" placeholder="Please Enter Your Name">
            </div>
            <div class="form-group">
                <label for="email" class="py-2">Email</label>
                <input name="email" type="email" class="form-control my-3" placeholder="Please Enter Your Email">
            </div>
            <div class="form-group">
                <button class="btn btn-success">Submit</button>
            </div>

        </form>
    </div>
</body>

</html>

The code above is simple. What it does is it checks if both input fields have been field correctly and if they have been correctly filled, it creates a new instance of our DatabaseConnection class that was declared in our DatabaseConnection.php and calls the insertData() function on the newly created instance to insert data into the database. After typing the code above, to be able to see the form in the browser we have to require the DatabaseConnection.php and insert.php in the index.php file since our application is a one-page application and everything will be viewed in index.php.

To open the application in your browser, navigate to your project folder and run the following command.

php -S localhost:3000

The port don't necessarily have to be 3000 it can be any port you like. If you open point your browser to the specified port you should be able to see our registration form.

Screenshot 2021-03-12 at 06.59.37.png

Now trying inputting and submitting user data, if you have done everything correctly, you'd see submitted data in your database table when open it in phpMyAdmin. The next thing we have to do is to display submitted user data in our application. We are going to do this in select.php. Copy and paste the following code into select.php and require it in index.php.

<?php
// require 'DatabaseConnection.php';

$newSelectInstance = new DatabaseConnection();
$results = $newSelectInstance->selectData('registered_users');

?>
<table class="table container mt-4">
    <thead>
        <tr>
            <th scope="col">ID</th>
            <th scope="col">Name</th>
            <th scope="col">Email</th>
            <th scope="col">Action</th>
        </tr>
    </thead>
    <?php
    foreach ($results as $row) {
        printf(
            "<tbody>
            <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>
                    <button class='btn btn-primary'><a href='' class='text-white text-decoration-none'>Edit</a></button> 
                    <button class='btn btn-danger'><a href='' class='text-white text-decoration-none'>Delete</a></button>
                </td>
            </tr>
        </tbody>",
            htmlspecialchars($row["id"], ENT_QUOTES),
            htmlspecialchars($row["name"], ENT_QUOTES),
            htmlspecialchars($row["email"], ENT_QUOTES)
        );
    }
    ?>
</table>

You should be able to see all the user data in the database displayed like in the image below when you reload the application.

The last features we have to implement are update and delete so that we can delete and update user data using the delete and edit buttons respectively. Let's start with update. Add the code below to your update.php file.

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
</head>

<body>
    <?php
    // Get the value of id and check if every part of it is a digit
    if (isset($_GET['id']) && ctype_digit($_GET['id'])) {
        $id = $_GET['id'];
    } else {
        // Redirect to index.php if id is not set.
        header('Location: index.php');
    }

    $name = $email = '';

    if ($_SERVER["REQUEST_METHOD"] === "POST") {
        $ok = true;
        $form_error = "<div class='container mt-4 alert alert-danger'>❌ Please all form fiels are required</div>";
        if (empty($_POST['name'])) {
            $ok = false;
            echo $form_error;
        } else {
            $name = $_POST['name'];
        }

        if (empty($_POST['email'])) {
            $ok = false;
            echo $form_error;
        } else {
            $email = $_POST['email'];
        }

        if ($ok) {
            require 'DatabaseConnection.php';

            $newUpdateInstance = new DatabaseConnection();
            $newUpdateInstance->updateData($name, $email, $id);
        } else {
            $newDatabaseSelect = new DatabaseConnection();
            $result = $newDatabaseSelect->selectSingleRecord('registered_users', $id);

            foreach ($result as $row) {
                $name = $row['name'];
                $email = $row['email'];
            }
        }
    }

    ?>
    <h1 class="text-center pt-4">Update This Record</h1>
    <form action="" method="POST" class="container mt-4">
        <div class="form-group mb-4">
            <label for="name" class="pb-3">Name</label>
            <input type="text" class="form-control" name="name" id="" placeholder="Enter Your Name" value="<?php echo htmlspecialchars($name, ENT_QUOTES) ?>">
        </div>
        <div class="form-group mb-4">
            <label for="job-title" class="pb-3">Email</label>
            <input type="text" class="form-control" name="email" id="" placeholder="Enter Your Name" value="<?php echo htmlspecialchars($email, ENT_QUOTES) ?>">
        </div>
        <div class="form-group">
            <button class="btn btn-success" name="submit">Update</button>
        </div>

    </form>
</body>

</html>

For us to be able to update a user's data, edit the foreach method in select.php to look like this.

carbon (1).png

Now if you click on any record's Edit button, it should open the update form already populated with that record's name and email ready to be updated. If you update a record, go back to index.php and refresh the page, you should see that the record has been updated. Now let's implement the delete feature so that we are able to delete user's data so add this code to delete.php and require it in index.php.

<?php
if(isset($_GET['id']) && ctype_digit($_GET['id'])) {
    $id = $_GET['id'];
} 

$newDatabaseDeleteInstance = new DatabaseConnection();
$newDatabaseDeleteInstance->deleteData('registered_users', $id);
?>

To be able to delete a record successfully, foreach method in ``select.php``` to look like this.

You should be able to delete a user's data. Voila 😁😁😁 we have successfully implemented all CRUD operations.

Conclusion

Yes!! you have successfully completed this tutorial 🎉🎉🎉. I hope you learnt something from it, now do not limit yourself use the knowledge gained here to do something amazing maybe create a blog or something, just go and do something you'd be proud of. If you have trouble understanding anything covered in this tutorial do not hesitate to share your worries with me down below in the comment section. Follow me on twitter @brandonbawe, connect with me on LinkedIn at Damue Brandon . The source code for this tutorial is available on gitHub here.

How to Implement CRUD Operations in Your Application Using PHP and MySQL

Prerequisites

Creating a database

Writing Code

Conclusion