How to build a simple CRUD application in PHP and MySQL

CRUD operations are basic database operations in a web application. It uses select, insert, update and delete statements on the database. CRUD stands for Create, Read, Update and Delete.

In this topic, we will develop a simple CRUD application in PHP and MySQL. It will display a list of registered users in an HTML table. Using this application, we can add, update, delete and view user details from the database.

Display data in an html table from database

Watch YouTube Video

CRUD application using PHP and MYSQLFolders and Files

We will use the below folder structure and files for this project:

CRUD operation in PHP

We have created a folder called 'crud' under 'xampp/htdocs'. In the folder 'cfg', we have kept dbconnect.php to connect to the MySQL database. In 'css' folder we have our custom stylesheet.

index.php is to display the list of users. upd_user.php is to update user details. del_user.php is to delete the user from the database.

Create the MySQL table

We will create a table named "users" in the MySQL database. We will create this table in the "test" database. The table structure is given below:

Crud in php and mysql example

Table has 4 columns.

  1. id - primary key and auto incremented
  2. name - name of the user
  3. email - email ID of the user
  4. address - address of the user
See the below sql script to create the table:

users.sql


CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Connect to MySQL database (dbconnect.php)

We will use dbconnect.php under 'cfg' folder to connect to the database.

dbconnect.php


<?php
$server = "localhost";
$userid = "root";
$pwd = "";
$dbname = "test";
$conn = new mysqli($server, $userid, $pwd, $dbname);
if ($conn->connect_error) {
    die("Connection Error: " . $conn->connect_error);
}

We are creating an instance of mysqli with four parameters.

  1. server - localhost
  2. userid - we are using root user for our demo.
  3. password - no password for the root user
  4. database name - test database in our case.

We will just include this file in other programs where we need to access the database. You can also read How to connect to MySQL database in PHP using MySQLi and PDO.

Develop Create User Form (Create)

Create user form in PHP

A simple create user form is created using three input fields. We will make this form very simple using minimum styles. it has Name, Email and Address fields with a submit button. Let's look at the form below:


<form class="form-1" action="" method="post">
    <div class="col-md-12 form-group">
        <label class="fw-bold">Name</label>
        <input type="text" class="form-control" name="name" value="<?php echo $name; ?>" placeholder="Enter your Name">
        <div class="input-err text-danger">
            <?=$name_err?>
        </div>
    </div>
    <div class="col-md-12 form-group">
        <label class="fw-bold">Email</label>
        <input type="text" class="form-control" name="email" value="<?php echo $email; ?>"
            placeholder="Enter your Email">
        <div class="input-err text-danger">
            <?=$email_err?>
        </div>
    </div>
    <div class="col-md-12 form-group">
        <label class="fw-bold">Address</label>
        <textarea class="form-control" name="addr" placeholder="Enter your Address"><?php echo $addr; ?></textarea>
        <div class="input-err text-danger">
            <?=$addr_err?>
        </div>
    </div>

    <div class="col-md-12 form-group text-center">
        <button type="submit" class="btn btn-primary" name="submit">Submit</button>
        <a href="index.php" class="btn btn-danger" name="submit">Cancel</a>
    </div>
</form>

We are using Bootstrap 5 and the CDN is included in the header.php. You can see that for each input field, we are displaying a validation error message. You will see that in the PHP code how we are validating the inputs.

PHP code to process submitted data

When the form is submitted, we first need to validate the input and then insert a row in the "users" table. See the below code for input validation:

Form input validation


<?php
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$addr = trim($_POST['addr']);

// validate input
if ($name == "") {
    $name_err = "Name is mandatory";
    $error = true;
}

if ($addr == "") {
    $addr_err = "Address is mandatory";
    $error = true;
}

if ($email == "") {
    $email_err = "Email is mandatory";
    $error = true;
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    $email_err = "Invalid Email format";
    $error = true;
} else { // check if email already registered
    $sql = "select * from users where email = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $email);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        $email_err = "Email already registered";
        $error = true;
    }
}

It checks if there are values in the fields and gives messages accordingly. It also validates the email format and checks if the email is already registered or not. Note that we are using a flag to indicate a validation error using a Boolean variable $error.

Insert form data in the database (Create)

This is our C (Create or Insert) part of CRUD. See the below PHP code:


<?php
// all validations passed
if (!$error) {
    $sql = "insert into users (name, email, address) value(?, ?, ?)";
    try {
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("sss", $name, $email, $addr);
        $stmt->execute();
        $_SESSION['succ_msg'] = "User Added successfully";
        header("Location: index.php");

    } catch (Exception $e) {
        $_SESSION['err_msg'] = $e->getMessage();
    }
}

If there are no validation errors, then $error will be false. So !$error will be true, then go ahead and insert the data in the database using prepared statements. If the database insert is successful, we add the success message in a session variable and redirect the user to the home page. In case of an error, we display the error on the same page only.

Let us now see the R (Read or Select) part of it.

Display Users (Read)

Display data in an html table from database

We can display the users in an html table using a for loop in index.php. See the below code:


<table class="table table-bordered table-stripped">
    <tr>
        <thead class="table-dark">
        <th>Id</th><th>Name</th><th>Email</th><th>Address</th><th>Action</th>
        </thead>
    </tr>
    <?php
       $sql = "select * from users order by id";
       $stmt = $conn->prepare($sql);
       $stmt->execute();
       $result = $stmt->get_result();
       if ($result->num_rows > 0) {
           $row = $result->fetch_assoc();
           foreach ($result as $row) {?>
           <tr>
               <td><?php echo $row['id']; ?></td>
               <td><?php echo $row['name']; ?></td>
               <td><?php echo $row['email']; ?></td>
               <td><?php echo $row['address']; ?></td>
               <td><a class="btn btn-primary " href="upd_user.php?id=<?php echo $row['id']; ?>">Update</a>  <a class="btn btn-danger " href="del_user.php?id=<?php echo $row['id']; ?>" onClick="return confirm('Are you sure you want to delete the user <?php echo $row["name"] ?>?')">Delete</a>
               </td>
           </tr>
            <?php }
       } else {?>
            <tr><td colspan="5">No Users Found</td></tr>
        <?php }?>
</table>

It selects all the records from the users table and displays them in an html table. The action column is to update and delete the users using two buttons. For update, we are calling upd_user.php with id as a parameter. Similarly, for delete, del_user.php is called with id as a parameter.

I have added some simple styles that I have written in style.css under css folder. It is given below. You can always change the styles to make the page look better.

style.css


body{
  width: 50%;
  margin: auto;
  font-size: large;
}
h1{
	text-align: center;
}

.succ-msg{
  text-align: center;
  color:green;
}

.err-msg{
  text-align: center;
  color:red;
}

form {
  padding: 10px;
  border: 1px solid #242e67;
  border-radius: 7px;
  background: #77d0e9;

}
.input-err{
  height: 30px;
}

Update User (Update)

Here we will see Update (U) operation of CRUD. For updating user details, you can click on the Update button. It will call upd_user.php with id as a parameter. It will display the selected user details in the update form. Let's see the below code:


<?php
if (isset($_REQUEST['id'])) {
    $id = $_REQUEST['id'];
    // get the details for this user to display in the update form
    $sql = "select * from users where id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $name = $row['name'];
        $email = $row['email'];
        $addr = $row['address'];
    } else {
        $_SESSION['err_msg'] = "User details not found";
        header("location:index.php");
    }

} else {
    header("location:index.php");
}

We are taking the input parameter using $_REQUEST['id'] and then selecting the details from the 'users' table for that id. These values are displayed in the form.

Below is the Update form. Note that we have a hidden field for the id.


<div class="container">
    <h1 class="mb-5 mt-5">PHP CRUD - Update User</h1>
    <?php if (isset($_SESSION['err_msg'])) {?>
        <div class="alert alert-danger">
            <?=$_SESSION['err_msg'];unset($_SESSION['err_msg'])?>
        </div>
    <?php }?>
        <form class="form-1" action="" method ="post">
            <input type="hidden" name="id" value="<?=$id?>">
            <div class="col-md-12 form-group">
                <label class="fw-bold">Name</label>
                <input type = "text" class="form-control" name="name" value="<?php echo $name; ?>" placeholder ="Enter your Name">
                <div class="input-err text-danger"><?=$name_err?></div>
            </div>
            <div class="col-md-12 form-group">
                <label class="fw-bold">Email</label>
                <input type = "text" class="form-control" name="email"  value="<?php echo $email; ?>" placeholder ="Enter your Email">
                <div class="input-err text-danger"><?=$email_err?></div>
            </div>
            <div class="col-md-12 form-group">
                <label class="fw-bold">Address</label>
                <textarea class="form-control" name="addr" placeholder ="Enter your Address"><?php echo $addr; ?></textarea>
                <div class="input-err text-danger"><?=$addr_err?></div>
            </div>

            <div class="col-md-12 form-group text-center">
                <button type="submit" class="btn btn-primary" name="submit">Submit</button>
                <a href="index.php" class="btn btn-danger" name="submit">Cancel</a>
            </div>
        </form>
</div>

It is almost the same as Add form, except that we are using a hidden field and displaying the field values.

When the form is submitted, input validations are done in the same way as the Add User form. After validation is done, it will update the record. See the below PHP code for updating the user:


<?php
// all validations passed
    if (!$error) {
        $sql = "update users set name = ?, email = ?, address = ? where id = ?";
        try {
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("sssi", $name, $email, $addr, $id);
            $stmt->execute();
            $_SESSION['succ_msg'] = "User Updated successfully";
            header("Location: index.php");

        } catch (Exception $e) {
            // $_SESSION['err_msg'] = $e->getMessage();
            $_SESSION['err_msg'] = "Some error occurred, please contact Admin";
        }
    }

Delete User (Delete)

We have a delete button in index.php and we are using del_user.php to delete a user. It simply takes the input parameter - id and then deletes the row from the 'users' table for that id. Then it redirects the user to the home page. See the below code for del_user.php


session_start();
include "cfg/dbconnect.php";

if (isset($_REQUEST['id'])) {
    $id = $_REQUEST['id'];
    $sql = "delete from users where id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $_SESSION['succ_msg'] = "User deleted successfully";
}
header("Location: index.php");

Lastly, let us see header.php which is included in other programs. This is for the head section of the html document.


<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=0">
    <meta name="description" content="">
    <title>PHP CRUD</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="css/style.css">
</head>
<?php
session_start();
?>

We are using Bootstrap 5 and added our custom stylesheet. Note that we have used session_start() here.

simple crud operations in php using mysqlTest the Application

Run localhost/crud in the browser. Initially, no users will be displayed in the list. Click on the Add User button and enter some data, then submit the form. It will be listed in the html table.

Test for Update and Delete, you should be able to update user details and delete user also. Also, verify the validation if it displays the messages correctly.

crud operations in php using mysqliImportant Note

I have created this simple form with a minimum number of fields. The purpose was to explain how CRUD operations work in PHP with MySQL. You can always add additional fields and modify them accordingly.

download source code for CRUD in PHP and MySQLDownload Source Code

To download the full source code, click on the download button.

Watch YouTube Video

Conclusion

In this example, I have developed a CRUD application in PHP and MySQL using simple forms and PHP code. You can always improve it. I hope it will be useful to you.