Select Insert Update and Delete (CRUD) in PHP and MySQL

Create the 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:

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

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.

<?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 the "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)

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)

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.

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 the 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 the 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");