Creating a CRUD (Create, Read, Update, Delete) application is a fundamental step for any developer looking to build dynamic web applications. In this tutorial, we will guide you through building a simple CRUD application using PHP and MySQL. This article is aimed at beginners, so we’ll break it down into easy-to-follow steps. By the end of this guide, you’ll have a fully functional system for managing records in a MySQL database.
What Is CRUD?
CRUD refers to the four basic operations of persistent storage:
- Create: Insert new data into the database.
- Read: Retrieve data from the database.
- Update: Modify existing data in the database.
- Delete: Remove data from the database.
By the end of this tutorial, you will have a working web application in which you can add, display, update, and delete records.
Setting Up the Environment
Before jump into the exercise, you need to have the development environment setup. Please refer this post for setting up
Create Database
Make sure you have the database setup. Create testdb database using XAMPP. Refers to link for creating new database.
Setting up a MySQL user other than ‘root’
You need to create a new user to be able to access to the MySQL database server from PHP script.
Please refer the post for creating new database user.
Create database table ‘tbl_users’
CREATE TABLE `tbl_users` (
`id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) NOT NULL,
`middle_name` varchar(25) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`email_id` varchar(65) DEFAULT NULL,
`mobile_no` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;Create Database Connection
Create dbConfig.php file and paste following code into that file.
<?php
//Database Details
$hostName = "localhost";//If your database is on remote machine,replace localhost with IP Address
$DBname= "testdb";
$DBuser= "testuser";
$userPass= ""; //no password
// Create connection
$conn = mysqli_connect($hostName , $DBuser, $userPass, $DBname);
// Check connection
if ($conn->connect_error) {
die("Connection to mysql database failed: " . $conn->connect_error);
}
echo "Connect to mysql database established successfully!";
mysqli_close($conn);
?>Create Form and Table for filling and displaying user data respectively.
Create index.php file which will have a form for entering user data and a table for displaying user data.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Basic CRUD Application Using PHP and MySQL</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<h1 class="text-center">Basic CRUD Application Using PHP and MySQL</h1>
<div class="container">
<div class="row">
<div class="col-md-4">
<form action="create.php" method="POST">
<div class="form-row">
<legend>Enter User Details</legend>
<div class="row">
<div class="col-xl-6">
<input type="text" name="first_name" class="form-control form-control-sm input-field " id="first_name" placeholder="First Name*" required>
</div>
<div class="col-xl-6">
<input type="text" name="middle_name" class="form-control form-control-sm input-field " id="middle_name" placeholder="Middle Name">
</div>
</div>
<div class="row my-2">
<div class="col-xl-6">
<input type="text" name="last_name" class="form-control form-control-sm input-field " id="last_name" placeholder="Last Name">
</div>
<div class="col-xl-6">
<select class="form-select form-select-sm" name="religion" id="religion" required>
<option value="">Select Gender</option>
<option value="Male">Male</option>
<option value="Female">Female</option>
<option value="Others">Others</option>
</select>
</div>
</div>
<div class="row my-2">
<div class="col-xl-6">
<input type="email" name="email_id" class="form-control form-control-sm input-field " id="email_id" placeholder="Email ID">
</div>
<div class="col-xl-6">
<input type="number" name="mobile_no" class="form-control form-control-sm input-field " id="mobile_no" placeholder="Mobile No">
</div>
</div>
<button type="submit" class="btn btn-success mx-auto" id='submitBtn' name='submitBtn'>Submit</button>
</div>
</form>
</div>
<div class="col-md-8">
<legend>Users List</legend>
<div class="table-responsive">
<table class="table">
<thead class="bg-dark text-white">
<tr>
<th>First Name</th>
<th>Middle Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Email ID</th>
<th>Mobile No</th>
</tr>
</thead>
<tbody>
<!--Paste readAll.php data here-->
</tbody>
</table>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
Create Record Example:
Let’s start from creating a record. We will add a new record to tbl_users table. Create a create.php file which will receive user data from form and insert it into tbl_users table
<?php
include "dbConfig.php";
$first_name=$_POST['first_name'];
$middle_name=$_POST['middle_name'];
$last_name=$_POST['last_name'];
$gender=$_POST['gender'];
$email_id=$_POST['email_id'];
$mobile_no=$_POST['mobile_no'];
$sql = "INSERT INTO tbl_users(first_name, middle_name,last_name,gender,email_id,mobile_no) VALUES ('$first_name', '$middle_name','$last_name','$gender','$email_id','$mobile_no')";
if (mysqli_query($conn, $sql)) {
echo "Record added successfully<br>";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($sql_connection);
}
?>Read Records Example
Create readAll.php file which will fetch users data from tbl_users table and display the fetched data in bootstrap table.
Paste following code in index.php at line no 69 after <!–Paste readAll.php data here –>
<?php
include "dbConfig.php";
$sql = "SELECT * FROM tbl_users";
$result = mysqli_query($conn,$sql);
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['middle_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['gender'] . "</td>";
echo "<td>" . $row['email_id'] . "</td>";
echo "<td>" . $row['mobile_no'] . "</td>";
echo "<td>";
echo '<a href="read.php?id='. $row['id'] .'" class="mr-3">View</a>';
echo '<a href="update.php?id='. $row['id'] .'" class="mr-3">Update</a>';
echo '<a href="delete.php?id='. $row['id'] .'" title="Delete Record">Delete</a>';
echo "</td>";
echo "</tr>";
}
}
?>Update Record
Next is, let’s try to update the information of existing record. For this create a update.php which will contain code for fetching user data from form for updating. Below is the PHP code.
<?php
include "dbConfig.php";
$id=$_POST['id'];
$first_name=$_POST['first_name'];
$middle_name=$_POST['middle_name'];
$last_name=$_POST['last_name'];
$gender=$_POST['gender'];
$email_id=$_POST['email_id'];
$mobile_no=$_POST['mobile_no'];
$sql = "UPDATE tbl_users
SET
first_name = 'first_name',
middle_name= 'middle_name',
last_name = 'last_name',
gender= 'gender',
email_id= 'email_id',
mobile_no= 'mobile_no'
WHERE id = '$id'";
if(mysqli_query($conn, $sql)){
echo "Record updated successfully<br>";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>Delete Record Example
Create delete.php file and paste following code into it.
<?php
include "dbConfig.php";
$id=$_POST['id'];
$sql = "DELETE FROM tbl_users WHERE id = '$id'";
$result = mysqli_query($conn,$sql);
if(mysqli_query($conn, $sql)){
echo "Record deleted successfully<br>";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>Conclusion
Congratulations! You have built a basic CRUD application using PHP and MySQL. This simple project introduces essential web development skills like connecting to a database, handling forms, and managing database records.
By mastering these fundamental CRUD operations, you are now well-equipped to handle the core functionality of many dynamic web applications!
1 thought on “Basic CRUD Application using PHP and MySQL”