What is CRUD (Create, Record, Update, and Delete)?
In this tutorial we are going to learn about how to view, insert, update and delete data from database using PHP and MySQL, This kind of system is also referred to CRUD operation (Create, Record, Update, and Delete) Its actual common task for every web-based software which has too much database and need to view insert Edit and delete the records from the database in PHP and MySQL.
Basically this tutorial is a second part of How to create Login and Logout system using google ReCaptcha in PHP and MySQL in this first part we explained how to create login logout system using PHP and MySQL if you do not know how to create user login logout system so kindly first check this tutorial. After that now come back to this tutorial. So let’s start.
Below is the example image of view insert edit and delete records from the database using PHP and MySQL.
STEPS
We need to follow the following steps below.
Create directory For insert update and delete Data.
Example image for the directory.
It is a good practice to create the required folders on your computer so you can proceed and manage it easily.
- CSS folder for custom styling of HTML and for storing the bootstrap library (bootstrap.min.css).
- Includes has the database connection file with name conn.php.
- Js folder for storing the bootstrap library (bootstrap.min.js).
- Launch your text editor and create a new file and save it as "view_student.php" in your main folder.
- Create a new file and save it as "add_student.php" in the same place where you store view_students.php file.
- Create one more new file and save it as "edit_student.php" in the same place as you can see in the above directory image.
Create a Database and a Table in it.
Open your phpMyAdmin and create a database with the name (school) and create a table (students) in it using the below query.
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
We have created a simple table in the database with the columns like first_name, last_name, and age.
Create a Database connection.
Create a new file with the name of (conn.php) and Write the below code in it and save in includes folder.
<?php
$con=mysqli_connect("localhost","root","","school");
?>
Now we create an HTML form and write a PHP script to insert records into database in our (add_student.php) file.
PHP script to insert data into the database.
<?php
//For database connection
include "includes/conn.php";
$msg="";
//for form submit
if(isset($_POST['submit'])){
$first_name=mysqli_real_escape_string($con,$_POST['first_name']);
$last_name=mysqli_real_escape_string($con,$_POST['last_name']);
$age=mysqli_real_escape_string($con,$_POST['age']);
//query to insert records intodatabase
$insert=mysqli_query($con,"INSERT INTO `students`( `first_name`,`last_name`,`age`) VALUES ('$first_name','$last_name','$age')");
if(mysqli_affected_rows($con)>0){
//status message
$msg="<div class='alert alert-success'>record added Successfully</div>";
}
else{
$msg="<div class='alert alert-danger'>record not added Successfully</div>";
}
}
?>
HTML form to insert data into Database.
<html>
<head>
<!--bootstrap libraray-->
<link rel="stylesheet" href="css/bootstrap.min.css">
<!--custom css-->
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-sm-8 offset-2">
<h1 class="">Add Student</h1>
<div class="col-sm-12">
<?php
echo $msg;
?>
<!-- html form-->
<form action="" method="post" role="form">
<div class="form-group">
<label>Enter First Name</label>
<input type="text" class="form-control" name="first_name">
</div>
<div class="form-group">
<label>Enter Last Name</label>
<input type="text" class="form-control" name="last_name">
</div>
<div class="form-group">
<label>Enter Age</label>
<input type="number" class="form-control" name="age">
</div>
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" name="submit">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
<!--bootstrap libraray-->
<script src="js/bootstrap.min.js"></script>
<body>
</html>
Now you can add data into the database your HTML form looks like as shown below.
Now we create an HTML table to display the student's records and write a PHP scripts for retrieving and deleting the database records in our (view_student.php) file.
<?php
//For database connection
include "includes/conn.php";
?>
<html>
<head>
<title> </title>
<!--bootstrap libraray-->
<link rel="stylesheet" href="css/bootstrap.min.css">
<!--custom css-->
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-lg-12">
<div class="card alert">
<div class="card-header">
<h4> View Students Data </h4>
<div class="card-header-right-icon">
<a href="add_student.php" class="btn btn-success" >Add Students</a>
</div>
</div>
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<?php
// PHP script for deliting the records from database
if(isset($_GET['action'])&&$_GET['action']=="delete"){
//id which we want to delete
$id=mysqli_real_escape_string($con,$_GET['id']);
$delet=mysqli_query($con,"delete from `students` WHERE id='$id'");
if(mysqli_affected_rows($con)>0){
$msg="<div class='alert alert-success'>Record deleted Successfully</div>";
}
else{
$msg="<div class='alert alert-danger'>Record not deleted Successfully</div>";
}
echo $msg;
}
?>
<!-- html table -->
<table class="table table-striped table-bordered">
<thead>
<tr>
<th>id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<?php
//query for retriving the records from database
$query= mysqli_query($con,"select * from students");
foreach($query as $val){
?>
<tbody>
<tr>
<td><?php echo $val["id"];?></td>
<td><?php echo $val["first_name"];?></td>
<td><?php echo $val["last_name"];?></td>
<td><?php echo $val["age"];?></td>
<!--actions buttons for edit and delete-->
<td><a href="edit_student.php?id=<?php echo $val['id']; ?>" class="btn btn-info" >Edit </a><a href="view_student.php?action=delete&id=<?php echo $val['id']; ?>" onclick="return confirmm();" class="btn btn-danger" >Delete</a></td>
</tr>
</tbody>
<?php
}
?>
</table>
</div>
</div>
</div>
<!-- /# card -->
</div>
<!-- /# column -->
</div>
<!-- /# row -->
</div>
</div>
</div>
</div>
<!--bootstrap libraray-->
<script src="js/bootstrap.min.js"></script>
<script>
// confirmation msg before deleting teh record
function confirmm(){
return confirm("Are you sure to delete this record?");
}
</script>
<body>
</html>
Now you can view and delete student’s records after retrieving the data from the database your HTML table looks like as shown below.
Now we create an HTML form to edit the student's records and write a PHP scripts for retrieving and updating the database records in our (edit_student.php) file.
PHP Script to retrieve and update the database records.
<?php
//For database connection
include "includes/conn.php";
$msg="";
//id of record which we want to edit
$id=mysqli_real_escape_string($con,$_GET['id']);
//for form submit
if(isset($_POST['submit'])){
//Sql injections
$first_name=mysqli_real_escape_string($con,$_POST['first_name']);
$last_name=mysqli_real_escape_string($con,$_POST['last_name']);
$age=mysqli_real_escape_string($con,$_POST['age']);
//query to update records in database
$update=mysqli_query($con,"UPDATE `students` SET `first_name`='$first_name',`last_name`='$last_name',`age`='$age' where id ='$id'");
if(mysqli_affected_rows($con)>0){
$msg="<div class='alert alert-success'>Record Updated Successfully</div>";
}
else{
$msg="<div class='alert alert-danger'>Record not updated Successfully</div>";
}
}
//query for retriving the records which we want to edit
$query=mysqli_query($con,"select * from students where id='$id'");
$row=mysqli_fetch_array($query);
?>
HTML Form for edit and update the records of Database.
<html>
<head>
<title> </title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-sm-8 offset-2">
<h1 class="">Edit Student</h1>
<div class="col-sm-12">
<?php
echo $msg;
?>
<form action="" method="post" role="form" data-toggle="validator">
<div class="form-group">
<label>Enter catagories Name</label>
<input type="text" class="form-control" value="<?php echo $row['first_name']; ?>" name="first_name">
</div>
<div class="form-group">
<label>Enter Last Name</label>
<input type="text" class="form-control" value="<?php echo $row['last_name']; ?>" name="last_name">
</div>
<div class="form-group">
<label>Enter Age</label>
<input type="number" class="form-control" value="<?php echo $row['age']; ?>" name="age">
</div>
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Update" name="submit">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<body>
</html>
The Bootstrap library is used to styling the Table, Form, and Buttons.
Now you can edit and update student’s records. Your HTML form looks like as shown below.
Other Related Posts
How to create Login, Logout using google ReCaptcha in PHP
How to use PHP for Loop and foreach Loop
How to use PHP while Loop and do while Loop
We have all done here is the final output.
Final Output Image.
We hope you may learn how to view, insert, edit, and delete data from the database using PHP and MySQL. Please share your thoughts and queries in the contact section.
Below is the Download link of the above tutorial.