So far we have completed Create, Read, and Update part of CRUD. Now let's start the Delete part. We will follow same technique applied in update part for User ID selection. Let's open the file showrecord.php in text editor and add another delete icon along with records list. After modification of the file the final code will be:
showrecord.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>SOLOCODING.NET PHP MYSQL Tutorial</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.0.1/css/font-awesome.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<nav class="navbar navbar-inverse">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="#">
User Management System
</a>
</div>
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav">
<li class="active"><a href="showrecord.php">All Records</a></li>
<li><a href="insert_form.html">Insert</a></li>
<li><a href="showrecord.php">Update</a></li>
<li><a href="showrecord.php">Delete</a></li>
</ul>
</div>
</div>
</nav>
<div class="container">
<div class="text-center" >
<h2>Display Records</h2>
</div>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>User Name</th>
<th>Email</th>
<th>Password</th>
<th>Created at</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php
require_once "connection.php";
$sql = 'SELECT * FROM userlogin';
$retval = $dbc->query($sql);
if(! $retval ) {
die('Could not get data: ' . mysqli_error());
}
else{
while($row = $retval->fetch_assoc()) { ?>
<tr>
<td> <?php echo "{$row['id']}"; ?></td>
<td> <?php echo "{$row['username']}"; ?></td>
<td> <?php echo "{$row['email']}"; ?></td>
<td> <?php echo "{$row['password']}"; ?></td>
<td> <?php echo "{$row['created_at']}"; ?></td>
<td> <a href="edit_record.php?edit_id=<?php echo "{$row['id']}";?>" alt="edit"><i class="fa fa-pencil" aria-hidden="true"></i></a> </td>
<td align='center'>
<span class='delete' data-id="<?php echo $row['id'] ?>"><i class="fa fa-times alert-danger remove" aria-hidden="true"></i></span>
</td>
</tr><?php
}
}
$retval->free();
$dbc->close();
?>
</tbody>
</div>
</body>
<!-- Javascript for Record deletion confirmation -->
<script type="text/javascript">
$(document).ready(function(){
// Delete
$('.delete').click(function(){
var el = this;
// id to be deleted
var deleteid = $(this).data('id');
var confirmalert = confirm("Are you sure to delete record ID (" + deleteid + ") ?");
if (confirmalert === true) {
// AJAX Request
$.ajax({
url: 'delete_record.php',
type: 'POST',
data: { id:deleteid },
success: function(response){
if(response == 1){
// Remove the row from table
$(el).closest('tr').css('background','red');
$(el).closest('tr').fadeOut(1000,function(){
$(this).remove();
});
}else{
alert('Invalid ID.');
}
}
});
}
});
});
</script>
</html>
And here is the output:
Now let's create the a new PHP file which will perform record deletion from database, named delete_record.php and paste the following code into it.
delete_record.php
<?php
require_once "connection.php";
$id = 0;
if(isset($_POST['id'])){
$id = mysqli_real_escape_string($dbc,$_POST['id']);
}
if($id > 0){
$checkRecord = mysqli_query($dbc,"SELECT * FROM userlogin WHERE id=".$id);
$totalrows = mysqli_num_rows($checkRecord);
if($totalrows > 0){
// Delete query
$query = "DELETE FROM userlogin WHERE id=".$id;
mysqli_query($dbc,$query);
echo 1;
exit;
}
else{
echo 0;
exit;
}
}
echo 0;
exit;
?>
Let's try to delete a record:
Here is the screenshots. Application will ask for confirmation. and on select OK it will delete the record.
Finally Application will clear the deleted record without refreshing the page by the help of Ajax. Hete is the result after deletion.
In this part we have learned to delete selected record from MySql Database using PHP. We have also used some Ajax to show updated list of records without refreshing the page. We have come to the end of the User-Management-System. But there ia a bug in this system. To add an user in our system we are still using insert_form.html and this form accepts empty data. In the next part we will rectify those small bugs from our application before wrap up this entire tutorial. Let's move to the last part.
Read: Part:5
0 comments:
Post a Comment