In this guide, you will learn how to perform CRUD operation in PHP and MySQLi. CRUD means four basic functionality like Create, Read, Update, Delete. It is the operations perform in the database. I am explaining to show you step by step through scratch so you can learn better understanding if you are new to learn PHP.
I considered you have already installed xampp or wamp server in local server.
1. File structure
config.php : It used for database connection.
crud.sql : It contain the database table structure.
index.php : It used to fetch the record from database.
create.php : It used to insert the new record.
read.php : It used to fetch the record of particular user.
edit.php : It used to edit the record.
2. Create a database
Open phpmyadmin in localhost with this url http://localhost/phpmyadmin, create a database named ‘crud’. After creating database run the SQL script or import the SQL file crud.sql.
table structure –
CREATE TABLE IF NOT EXISTS `users` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`first_name` varchar(200) DEFAULT NULL,
`last_name` varchar(200) DEFAULT NULL,
`mobile_number` bigint(10) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
COMMIT;
3. Create a database connection file
config.php
<?php
$con=mysqli_connect("localhost", "root", "", "crud");
if(mysqli_connect_errno())
{
echo "Connection Fail".mysqli_connect_error();
}
?>
4. Create a form for data insertion
<form method="POST">
<div class="text-center"><a href="index.php">Listing Page</a></div>
<h2>Insert Detail</h2>
<div class="form-group">
<div class="row">
<div class="col"><input type="text" class="form-control" name="fname" placeholder="First Name" required="true"></div>
<div class="col"><input type="text" class="form-control" name="lname" placeholder="Last Name" required="true"></div>
</div>
</div>
<div class="form-group">
<input type="text" class="form-control" name="contactno" placeholder="Mobile Number" required="true" maxlength="10" pattern="[0-9]+">
</div>
<div class="form-group">
<input type="email" class="form-control" name="email" placeholder="Email" required="true">
</div>
<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Submit</button>
</div>
</form>
5. data insertion code create.php
<?php
//Databse Connection file
include('config.php');
if(isset($_POST['submit']))
{
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$contno=$_POST['contactno'];
$email=$_POST['email'];
// Query for data insertion
$query=mysqli_query($con, "insert into users(first_name,last_name, mobile_number, email) value('$fname','$lname', '$contno', '$email')");
if ($query) {
echo "<script>alert('You have successfully inserted record.');</script>";
echo "<script type='text/javascript'> document.location ='index.php'; </script>";
}
else
{
echo "<script>alert('Something Went Wrong. Please try again');</script>";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
<title>webdeveloperindia.in CRUD</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
color: #566787;
background: #f5f5f5;
font-family: 'Roboto', sans-serif;
}
.form-control {
height: 40px;
box-shadow: none;
color: #969fa4;
}
.form-control:focus {
border-color: #5cb85c;
}
.form-control, .btn {
border-radius: 3px;
}
.signup-form {
width: 450px;
margin: 0 auto;
padding: 30px 0;
font-size: 15px;
}
.signup-form h2 {
color: #636363;
margin: 0 0 15px;
position: relative;
text-align: center;
}
.signup-form h2:before, .signup-form h2:after {
content: "";
height: 2px;
width: 27%;
background: #d4d4d4;
position: absolute;
top: 50%;
z-index: 2;
}
.signup-form h2:before {
left: 0;
}
.signup-form h2:after {
right: 0;
}
.signup-form .hint-text {
color: #999;
margin-bottom: 30px;
text-align: center;
}
.signup-form form {
color: #999;
border-radius: 3px;
margin-bottom: 15px;
background: #f2f3f7;
box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
padding: 30px;
}
.signup-form .form-group {
margin-bottom: 20px;
}
.signup-form input[type="checkbox"] {
margin-top: 3px;
}
.signup-form .btn {
font-size: 16px;
font-weight: bold;
min-width: 140px;
outline: none !important;
}
.signup-form .row div:first-child {
padding-right: 10px;
}
.signup-form .row div:last-child {
padding-left: 10px;
}
.signup-form a {
color: #fff;
text-decoration: underline;
}
.signup-form a:hover {
text-decoration: none;
}
.signup-form form a {
color: #5cb85c;
text-decoration: none;
}
.signup-form form a:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div class="signup-form">
<form method="POST">
<div class="text-center"><a href="index.php">Listing Page</a></div>
<h2>Insert Detail</h2>
<div class="form-group">
<div class="row">
<div class="col"><input type="text" class="form-control" name="fname" placeholder="First Name" required="true"></div>
<div class="col"><input type="text" class="form-control" name="lname" placeholder="Last Name" required="true"></div>
</div>
</div>
<div class="form-group">
<input type="text" class="form-control" name="contactno" placeholder="Mobile Number" required="true" maxlength="10" pattern="[0-9]+">
</div>
<div class="form-group">
<input type="email" class="form-control" name="email" placeholder="Email" required="true">
</div>
<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Submit</button>
</div>
</form>
</div>
</body>
</html>
6. Fetch the record from the database index.php
in this file have code to will fetch all data from the database.
<?php
//connection file
include('config.php');
if(isset($_GET['delid']))
{
$rid=intval($_GET['delid']);
$sql=mysqli_query($con,"delete from users where ID=$rid");
echo "<script>alert('user information deleted');</script>";
echo "<script>window.location.href = 'index.php'</script>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>webdeveloperindia.in CRUD</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
color: #566787;
background: #f5f5f5;
font-family: 'Roboto', sans-serif;
}
.table-responsive {
margin: 30px 0;
}
.table-wrapper {
min-width: 1000px;
background: #fff;
padding: 20px;
box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
font-size: 15px;
padding-bottom: 10px;
margin: 0 0 10px;
min-height: 45px;
}
.table-title h2 {
margin: 5px 0 0;
font-size: 24px;
}
.table-title select {
border-color: #ddd;
border-width: 0 0 1px 0;
padding: 3px 10px 3px 5px;
margin: 0 5px;
}
.table-title .show-entries {
margin-top: 7px;
}
.search-box {
position: relative;
float: right;
}
.search-box .input-group {
min-width: 200px;
position: absolute;
right: 0;
}
.search-box .input-group-addon, .search-box input {
border-color: #ddd;
border-radius: 0;
}
.search-box .input-group-addon {
border: none;
border: none;
background: transparent;
position: absolute;
z-index: 9;
}
.search-box input {
height: 34px;
padding-left: 28px;
box-shadow: none !important;
border-width: 0 0 1px 0;
}
.search-box input:focus {
border-color: #3FBAE4;
}
.search-box i {
color: #a0a5b1;
font-size: 19px;
position: relative;
top: 8px;
}
table.table tr th, table.table tr td {
border-color: #e9e9e9;
}
table.table th i {
font-size: 13px;
margin: 0 5px;
cursor: pointer;
}
table.table td:last-child {
width: 130px;
}
table.table td a {
color: #a0a5b1;
display: inline-block;
margin: 0 5px;
}
table.table td a.view {
color: #03A9F4;
}
table.table td a.edit {
color: #FFC107;
}
table.table td a.delete {
color: #E34724;
}
table.table td i {
font-size: 19px;
}
.pagination {
float: right;
margin: 0 0 5px;
}
.pagination li a {
border: none;
font-size: 13px;
min-width: 30px;
min-height: 30px;
padding: 0 10px;
color: #999;
margin: 0 2px;
line-height: 30px;
border-radius: 30px !important;
text-align: center;
}
.pagination li a:hover {
color: #666;
}
.pagination li.active a {
background: #03A9F4;
}
.pagination li.active a:hover {
background: #0397d6;
}
.pagination li.disabled i {
color: #ccc;
}
.pagination li i {
font-size: 16px;
padding-top: 6px
}
.hint-text {
float: left;
margin-top: 10px;
font-size: 13px;
}
</style>
</head>
<body>
<div class="container-xl webdeveloper">
<div class="table-responsive">
<div class="table-wrapper">
<div class="table-title">
<div class="row">
<div class="col-sm-5">
<h2>User Management</h2>
</div>
<div class="col-sm-7" align="right">
<a href="create.php" class="btn btn-secondary"><span>Add New User</span></a>
</div>
</div>
</div>
<table class="table table-striped table-hover">
<thead>
<tr>
<th>No.</th>
<th>Full Name</th>
<th>Email</th>
<th>Mobile Number</th>
<th>Created Date</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
$ret=mysqli_query($con,"select * from users");
$cnt=1;
$row=@mysqli_num_rows($ret);
if($row>0){
while ($row=mysqli_fetch_array($ret)) {
?>
<!--Fetch the Records -->
<tr>
<td><?php echo $cnt;?></td>
<td><?php echo $row['first_name'];?> <?php echo $row['last_name'];?></td>
<td><?php echo $row['email'];?></td>
<td><?php echo $row['mobile_number'];?></td>
<td> <?php echo $row['created_date'];?></td>
<td>
<a href="read.php?viewid=<?php echo htmlentities ($row['ID']);?>" class="view" title="View" data-toggle="tooltip"><i class="material-icons"></i></a>
<a href="edit.php?editid=<?php echo htmlentities ($row['ID']);?>" class="edit" title="Edit" data-toggle="tooltip"><i class="material-icons"></i></a>
<a href="index.php?delid=<?php echo ($row['ID']);?>" class="delete" title="Delete" data-toggle="tooltip" onclick="return confirm('Do you really want to Delete ?');"><i class="material-icons"></i></a>
</td>
</tr>
<?php
$cnt=$cnt+1;
} } else {?>
<tr>
<th style="text-align:center; color:red;" colspan="6">No Record Found</th>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>
7. Fetch the particular single record (read.php)
in read.php file, we will fetch single record from users table.
read.php
<?php
include('config.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>webdeveloperindia.in CRUD</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
color: #566787;
background: #f5f5f5;
font-family: 'Roboto', sans-serif;
}
.table-responsive {
margin: 30px 0;
}
.table-wrapper {
min-width: 1000px;
background: #fff;
padding: 20px;
box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
font-size: 15px;
padding-bottom: 10px;
margin: 0 0 10px;
min-height: 45px;
}
.table-title h2 {
margin: 5px 0 0;
font-size: 24px;
}
.table-title select {
border-color: #ddd;
border-width: 0 0 1px 0;
padding: 3px 10px 3px 5px;
margin: 0 5px;
}
.table-title .show-entries {
margin-top: 7px;
}
.search-box {
position: relative;
float: right;
}
.search-box .input-group {
min-width: 200px;
position: absolute;
right: 0;
}
.search-box .input-group-addon, .search-box input {
border-color: #ddd;
border-radius: 0;
}
.search-box .input-group-addon {
border: none;
border: none;
background: transparent;
position: absolute;
z-index: 9;
}
.search-box input {
height: 34px;
padding-left: 28px;
box-shadow: none !important;
border-width: 0 0 1px 0;
}
.search-box input:focus {
border-color: #3FBAE4;
}
.search-box i {
color: #a0a5b1;
font-size: 19px;
position: relative;
top: 8px;
}
table.table tr th, table.table tr td {
border-color: #e9e9e9;
}
table.table th i {
font-size: 13px;
margin: 0 5px;
cursor: pointer;
}
table.table td:last-child {
width: 130px;
}
table.table td a {
color: #a0a5b1;
display: inline-block;
margin: 0 5px;
}
table.table td a.view {
color: #03A9F4;
}
table.table td a.edit {
color: #FFC107;
}
table.table td a.delete {
color: #E34724;
}
table.table td i {
font-size: 19px;
}
.pagination {
float: right;
margin: 0 0 5px;
}
.pagination li a {
border: none;
font-size: 13px;
min-width: 30px;
min-height: 30px;
padding: 0 10px;
color: #999;
margin: 0 2px;
line-height: 30px;
border-radius: 30px !important;
text-align: center;
}
.pagination li a:hover {
color: #666;
}
.pagination li.active a {
background: #03A9F4;
}
.pagination li.active a:hover {
background: #0397d6;
}
.pagination li.disabled i {
color: #ccc;
}
.pagination li i {
font-size: 16px;
padding-top: 6px
}
.hint-text {
float: left;
margin-top: 10px;
font-size: 13px;
}
</style>
</head>
<body>
<div class="container-xl">
<div class="table-responsive">
<div class="table-wrapper">
<div class="table-title">
<div class="row">
<div class="col-sm-5">
<h2>User Details</h2>
</div>
</div>
</div>
<table cellpadding="0" cellspacing="0" border="0" class="display table table-bordered" id="hidden-table-info">
<tbody>
<?php
$vid=$_GET['viewid'];
$ret=mysqli_query($con,"select * from users where ID =$vid");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {
?>
<tr>
<th>First Name</th>
<td><?php echo $row['first_name'];?></td>
<th>Last Name</th>
<td><?php echo $row['last_name'];?></td>
</tr>
<tr>
<th>email</th>
<td><?php echo $row['email'];?></td>
<th>Mobile Number</th>
<td><?php echo $row['mobile_number'];?></td>
</tr>
<tr>
<th>Creation Date</th>
<td><?php echo $row['created_date'];?></td>
</tr>
<?php
$cnt=$cnt+1;
}?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>
8. Edit/Update the particular single record edit.php
to updating a record we have to get the row id of that record and store in $editid. we need to gets a record based on the given id.
edit.php
<?php
//Database Connection
include('config.php');
if(isset($_POST['submit']))
{
$eid=$_GET['editid'];
//Getting Post Values
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$contno=$_POST['contactno'];
$email=$_POST['email'];
//Query for data updation
$query=mysqli_query($con, "update users set first_name='$fname',last_name='$lname', mobile_number='$contno', email='$email' where ID='$eid'");
if ($query) {
echo "<script>alert('You have successfully update the data');</script>";
echo "<script type='text/javascript'> document.location ='index.php'; </script>";
}
else
{
echo "<script>alert('Something Went Wrong. Please try again');</script>";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
<title>webdeveloperindia.in CRUD</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
color: #566787;
background: #f5f5f5;
font-family: 'Roboto', sans-serif;
}
.form-control {
height: 40px;
box-shadow: none;
color: #969fa4;
}
.form-control:focus {
border-color: #5cb85c;
}
.form-control, .btn {
border-radius: 3px;
}
.signup-form {
width: 450px;
margin: 0 auto;
padding: 30px 0;
font-size: 15px;
}
.signup-form h2 {
color: #636363;
margin: 0 0 15px;
position: relative;
text-align: center;
}
.signup-form h2:before, .signup-form h2:after {
content: "";
height: 2px;
width: 24%;
background: #d4d4d4;
position: absolute;
top: 50%;
z-index: 2;
}
.signup-form h2:before {
left: 0;
}
.signup-form h2:after {
right: 0;
}
.signup-form .hint-text {
color: #999;
margin-bottom: 30px;
text-align: center;
}
.signup-form form {
color: #999;
border-radius: 3px;
margin-bottom: 15px;
background: #f2f3f7;
box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
padding: 30px;
}
.signup-form .form-group {
margin-bottom: 20px;
}
.signup-form input[type="checkbox"] {
margin-top: 3px;
}
.signup-form .btn {
font-size: 16px;
font-weight: bold;
min-width: 140px;
outline: none !important;
}
.signup-form .row div:first-child {
padding-right: 10px;
}
.signup-form .row div:last-child {
padding-left: 10px;
}
.signup-form a {
color: #fff;
text-decoration: underline;
}
.signup-form a:hover {
text-decoration: none;
}
.signup-form form a {
color: #5cb85c;
text-decoration: none;
}
.signup-form form a:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div class="signup-form">
<form method="POST">
<?php
$eid=$_GET['editid'];
$ret=mysqli_query($con,"select * from users where ID='$eid'");
while ($row=mysqli_fetch_array($ret)) {
?>
<h2>Update detail</h2>
<div class="form-group">
<div class="row">
<div class="col"><input type="text" class="form-control" name="fname" value="<?php echo $row['first_name'];?>" required="true"></div>
<div class="col"><input type="text" class="form-control" name="lname" value="<?php echo $row['last_name'];?>" required="true"></div>
</div>
</div>
<div class="form-group">
<input type="text" class="form-control" name="contactno" value="<?php echo $row['mobile_number'];?>" required="true" maxlength="10" pattern="[0-9]+">
</div>
<div class="form-group">
<input type="email" class="form-control" name="email" value="<?php echo $row['email'];?>" required="true">
</div>
<?php
}?>
<div class="form-group">
<button type="submit" class="btn btn-success btn-lg btn-block" name="submit">Update</button>
</div>
</form>
</div>
</body>
</html>
Below I am providing you some scratch regarding listing, create, edit and delete operation.
Listing
create record
edit record
View record
Delete record
Here I am proving download source code.
Hello, Welcome to webdeveloperindia.in. I am a full-stack web developer. Email – [email protected] | Skype – azaruddin23. I have knowledge of PHP, Laravel, Magento 1/2, Codeigniter, WordPress, Joomla, Shopify, Git, Bitbuket, jQuery, Ajax, Javascript and ReactJS.