In this tutorial, you will learn how you can create a simple CRUD application using PHP MySQLi with MySQL database. And through this application, you can perform a basic user Insert, Fetch, Update, and Delete operation.
Follow the below steps to create this PHP CRUD Application:
1. MySQL Database and table creation.
First, we need to create a database and database table where we save the users’ data or records.
In the database, we will save only two information of a user – name
and email
.
- Database Name –
crud_app
- Table Name –
users
So then, open your phpMyAdmin and create a database called crud_app
, and then use the following SQL code to create the users table and structure of the table.
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
email varchar(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
2. Creation of PHP CRUD App.
In this step, we will create the app files. In this step, we will create the app files. So go to your wamp www
directory or XAMPP htdocs
folder, and here create a new folder called php-crud
(this is the main app folder).
If you are curious to know what PHP files we have to create to build this CRUD application, see the following image and folder structure tree.

php-crud/
├─ db_connection.php
├─ delete.php
├─ edit.php
├─ fetch-data.php
├─ index.php
├─ insert-data.php
├─ style.css
├─ update.php
2.1. db_connection.php
This db_connection.php
is for making the database connection.
<?php
$host = "localhost";
$db_user = "root";
$db_password = "";
$db_name = "crud_app";
// Create connection
$conn = mysqli_connect($host, $db_user, $db_password, $db_name);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
2.2. insert-data.php
This insert-data.php
is for inserting a new user into the database.
<?php
function insertData($conn, $u_name, $u_email)
{
$u_name = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_name)));
$u_email = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_email)));
// IF NAME OR EMAIL IS EMPTY
if (empty($u_name) || empty($u_email)) {
return 'Please fill all required fields.';
}
//IF EMAIL IS NOT VALID
elseif (!filter_var($u_email, FILTER_VALIDATE_EMAIL)) {
return 'Invalid email address.';
} else {
$check_email = mysqli_query($conn, "SELECT `email` FROM `users` WHERE `email` = '$u_email'");
// IF THE EMAIL IS ALREADY IN USE
if (mysqli_num_rows($check_email) > 0) {
return 'This email is already registered. Please try another.';
}
// INSERTING THE USER DATA
$query = mysqli_query($conn, "INSERT INTO `users`(`name`,`email`) VALUES('$u_name','$u_email')");
// IF USER INSERTED
if ($query) {
return true;
}
return 'Opps something is going wrong!';
}
}
2.3. fetch-data.php
The fetch-data.php is for fetching users from the database.
In this file you can see two functions –
fetchUsers()
– For fetching all users.fetchUser()
– Fetching a single user by the id of the user.
<?php
// FETCH ALL USERS
function fetchUsers($conn){
$query = mysqli_query($conn,"SELECT * FROM `users`");
return mysqli_fetch_all($query,MYSQLI_ASSOC);
};
// FETCH SINGLE USER BY ID
function fetchUser($conn, $id){
$id = mysqli_real_escape_string($conn,$id);
$query = mysqli_query($conn,"SELECT * FROM `users` WHERE ``id`='$id'");
$data = mysqli_fetch_assoc($query);
if(!count($data)){
header('Location: index.php');
exit;
}
return $data;
}
2.4. index.php
This is the home page of the CRUD app where you can see the users list, insert a new user.
At the bottom of the index.php
, you can see a piece of JS code, this JS code is for getting confirmation for deleting the user.
<?php
require_once './db_connection.php';
require_once './fetch-data.php';
require_once './insert-data.php';
$all_user = array_reverse(fetchUsers($conn));
if (isset($_POST['u_name']) && isset($_POST['u_email'])) {
$insert_data = insertData($conn, $_POST['u_name'], $_POST['u_email']);
if ($insert_data === true) {
header('Location: index.php');
exit;
}
}
?>
<!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>Simple PHP CRUD Application</title>
<link rel="stylesheet" href="./style.css">
</head>
<body>
<div class="container">
<header class="header">
<h1 class="title">PHP CRUD Application</h1>
<p>By <a href="//www.w3jar.com">w3jar.com</a></p>
</header>
<div class="wrapper">
<div class="form">
<form method="POST">
<label for="userName">Full Name</label>
<input type="text" name="u_name" id="userName" placeholder="Name" autocomplete="off" required>
<label for="userEmail">Email</label>
<input type="email" name="u_email" id="userEmail" placeholder="Email" autocomplete="off" required>
<?php if (isset($insert_data) && $insert_data !== true) {
echo '<p class="msg err-msg">' . $insert_data . '</p>';
}
?>
<input type="submit" value="Submit">
</form>
</div>
<div class="user-list">
<?php if (count($all_user) > 0) : ?>
<table>
<tbody>
<tr>
<th>Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<?php foreach ($all_user as $user) :
$id = $user['id'];
$name = $user['name'];
$email = $user['email'];
?>
<tr>
<td><?php echo $name; ?></td>
<td><?php echo $email; ?></td>
<td>
<a href="edit.php?id=<?php echo $id; ?>" class="edit">Edit</a> |
<a href="delete.php?id=<?php echo $id; ?>" class="delete delete-action">Delete</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php else : ?>
<h2>No records found. Please insert some records.</h2>
<?php endif; ?>
</div>
</div>
</div>
<script>
var delteAction = document.querySelectorAll('.delete-action');
delteAction.forEach((el) => {
el.onclick = function(e) {
e.preventDefault();
if (confirm('Are you sure?')) {
window.location.href = e.target.href;
}
}
});
</script>
</body>
</html>
2.5. update.php
The update.php is for updating an existing user.
<?php
function updateUser($conn, $id, $u_name, $u_email)
{
$id = trim(mysqli_real_escape_string($conn, $id));
$u_name = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_name)));
$u_email = trim(mysqli_real_escape_string($conn, htmlspecialchars($u_email)));
// IF NAME OR EMAIL IS EMPTY
if (empty($u_name) || empty($u_email)) {
return 'Please fill all required fields.';
}
//IF EMAIL IS NOT VALID
elseif (!filter_var($u_email, FILTER_VALIDATE_EMAIL)) {
return 'Invalid email address.';
} else {
$check_email = mysqli_query($conn, "SELECT `email` FROM `users` WHERE `email` = '$u_email' AND `id`!='$id'");
// IF THE EMAIL IS ALREADY IN USE
if (mysqli_num_rows($check_email) > 0) {
return 'This email is already registered. Please try another.';
}
// UPDATE USER DATA
$query = mysqli_query($conn, "UPDATE `users` SET `name`='$u_name', `email`='$u_email' WHERE `id`='$id'");
// IF USER UPDATED
if ($query) {
return true;
}
return 'Opps something is going wrong!';
}
}
2.6. edit.php
The edit.php provides a user interface for updating an existing user.
<?php
require_once './db_connection.php';
require_once './fetch-data.php';
require_once './update.php';
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
header('Location: index.php');
exit;
}
if (isset($_POST['u_name']) && isset($_POST['u_email'])) {
$update_data = updateUser($conn, $_GET['id'], $_POST['u_name'], $_POST['u_email']);
if ($update_data === true) {
header('Location: index.php');
exit;
}
}
$theUser = fetchUser($conn, $_GET['id']);
?>
<!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>Simple PHP CRUD Application</title>
<link rel="stylesheet" href="./style.css">
</head>
<body>
<div class="container">
<header class="header">
<h1 class="title">PHP CRUD Application</h1>
<p>By <a href="//www.w3jar.com">w3jar.com</a></p>
</header>
<div class="wrapper edit-wrapper">
<div class="form">
<form method="POST">
<label for="userName">Full Name</label>
<input type="text" name="u_name" value="<?php echo htmlspecialchars($theUser['name']); ?>" id="userName" placeholder="Name" autocomplete="off" required>
<label for="userEmail">Email</label>
<input type="email" name="u_email" value="<?php echo htmlspecialchars($theUser['email']); ?>" id="userEmail" placeholder="Email" autocomplete="off" required>
<?php if (isset($update_data) && $update_data !== true) {
echo '<p class="msg err-msg">' . $update_data . '</p>';
}
?>
<input type="submit" value="Update">
</form>
</div>
</div>
</div>
</body>
</html>
2.7. style.css
CSS stylesheet for beautifying the CRUD app.
*,
*::before,
*::after {
box-sizing: border-box;
}
body {
font-family: system-ui, -apple-system, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", "Liberation Sans", sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji";
margin: 0;
padding: 50px;
background-color: #f7f7f7;
color: #222222;
}
input {
font-size: 15px;
}
.container {
max-width: 900px;
margin: 0 auto;
padding: 20px;
background-color: white;
box-shadow: 0 1px 2px 0 rgba(0, 0, 0, 0.05);
border-radius: 3px;
}
.header {
text-align: center;
}
.container .wrapper,
.form form {
display: flex;
flex-wrap: wrap;
}
.container .wrapper {
margin-top: 30px;
align-items: flex-start;
}
.container .edit-wrapper {
justify-content: center;
}
.form {
border: 1px solid #cccccc;
flex-basis: 40%;
padding: 20px;
background-color: #f7f7f7;
}
.form form label {
font-weight: bold;
}
.form form [type="text"],
.form form [type="email"] {
width: 100%;
padding: 10px;
border: 1px solid #666666;
border-radius: 2px;
}
.form form [type="text"] {
margin-bottom: 5px;
}
.form form [type="submit"] {
margin-top: 10px;
padding: 7px 10px;
cursor: pointer;
border-radius: 2px;
background: #0d6efd;
color: white;
border: 1px solid rgba(0, 0, 0, 0.1);
box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1), 0 1px 2px 0 rgba(0, 0, 0, 0.06);
}
.msg {
width: 100%;
color: #dc3545;
border: 1px solid #dc3545;
border-radius: 3px;
padding: 10px;
margin-bottom: 0;
}
.user-list {
padding-left: 20px;
flex: 1;
}
table {
border-collapse: collapse;
width: 100%;
}
table th {
background-color: #f7f7f7;
}
table th,
table td {
border: 1px solid #cccccc;
padding: 10px;
text-align: center;
}
a.edit,
a.delete {
background-color: #dc3545;
color: white;
text-decoration: none;
font-size: 14px;
padding: 3px 5px;
border-radius: 2px;
border: 1px solid rgba(0, 0, 0, 0.1);
}
a.edit {
background-color: #0d6efd;
}