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.
Watch YouTube Video
Folders and Files
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.
Development Steps
Step 1 - Create the MySQL table
Step 2 - Connect to the database (dbconnect.php)
Step 3 - Develop create user form (Create)
Step 4 - Display Users (Read)
Step 5 - Update User (Update)
Step 6 - Delete User (Delete)
We will create a table named "users" in MySQL database. We will create this table in "test" database. The table structure is given below:
Table has 4 columns.
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;
We will use dbconnect.php under 'cfg' folder to connect to the database.
dbconnect.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.
We will just include this file in other programs where we need to access the database. You can read How to connect to MySQL database in PHP using MySQLi and PDO.
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.
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:
$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.
This is our C (Create or Insert) part of CRUD. See the below PHP code:
// 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 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 are displaying the error in the same page only.
Let us now see the R (Read or Select) part of it.
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 form 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 and kept 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;
}
Here we will see U (Update) part of the CRUD operations. For updating user details, you can click on the Update button. It will call upd_user.php with id as parameter. It will display the selected user details in the update form. Let's see the below code:
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 '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 add user form. After validation is done, it will update the record. see the below PHP code for updating the user:
// 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";
}
}
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 just 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.
Test the ApplicationRun 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.
Important NoteI 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 accordingly.
Download Source CodeClick on the download button to download the source code. You can always modify the code as per your requirements.
Watch YouTube Video
ConclusionIn this example, we have developed a CRUD application in PHP and MySQL. We have used very simple forms and PHP code. You can always improve it. Hope it will be useful for you.