How to Implement CRUD Operations in Your Application Using PHP and MySQL
Tags: php, tutorial, beginners, webdev
Published: 2021-03-12 07:47:26

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 site where a user can add, read, update and delete their person 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.

![screencapture-localhost-3000-2021-03-12-07_13_25.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615534106410/1gpBSfxBM.png)

## 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](https://damue.hashnode.dev/how-to-install-xampp)). Download MAMP from its  [official website](https://www.mamp.info/en/downloads/).
- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1615466472481/YPF-NRsz1.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1615467062791/J7DCrev7C.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](https://cdn.hashnode.com/res/hashnode/image/upload/v1615467931460/tcKmTgJy4.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``.



![carbon.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615528437890/5n3IhA--8.png)

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1615528881579/YUNFy8ziy.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.


![screencapture-localhost-3000-2021-03-12-07_13_25.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615529755519/tkyMaJav2.png)

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1615531106238/uxaJ00owg.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.


![delete.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1615532409389/XX5tdwBL1.png)


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](https://twitter.com/flaacko_flaacko), connect with me on LinkedIn at  [Damue Brandon](https://www.linkedin.com/in/damue-brandon-bawe-73ba121a1/) .  The source code for this tutorial is available on gitHub  [here](https://github.com/brandonbawe/php_registration_crud_app).