Delete is one of the primitive processes performed on database management system. And it’s always a good practice to confirm the delete action of users with a message to avoid accidental clicks and data deletion. In this tutorial we’ll see how to delete row(s) from mysql database with confirmation message in php. Implementing database deletion using procedural style has been discussed a lot but here I want to show you how to achieve it using OOP Method (Object Oriented Programming) in php.
Implementing PHP Delete from MySQL with Confirmation
For better understanding of database deleting task, I go with an example. Consider having user details stored in database. As user interface, we should fetch and display the user records in a neat grid view along with a delete button or link on each row. When the user clicks on the delete button, a message pops up to confirm the delete process. If the user clicks ok, then that particular user record will be deleted and the page refreshes to show up the remaining user records from the database.
Recommended Read: How to do Secure File Upload in PHP
Creating MySQL Database
First we’ll create the database required for the example. Run the below sql statements to create database and required USERS table.
CREATE DATABASE `customers`;
USE `customers`;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`fname` varchar(30) NOT NULL,
`lname` varchar(30) NOT NULL,
`email` varchar(60) NOT NULL,
`city` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `users` (`id`, `fname`, `lname`, `email`, `city`) VALUES
(1, 'Jim', 'Connor', 'jimconnor@yahoo.com', 'Las Vegas'),
(2, 'Taylor', 'Fox', 'taylorfox@hotmail.com', 'San Francisco'),
(3, 'Julia', 'Brown', 'juliabrown@gmail.com', 'Los Angeles'),
(4, 'Daniel', 'Greyson', 'danielgreyson@hotmail.com', 'New York'),
(5, 'Rose', 'Harris', 'roseharris@gmail.com', 'New York');
Create Class File ‘DeleteClass.class.php’
Next we need to create a php class file where we place all our database transactions. Create a php file ‘DeleteClass.class.php’ and copy paste the below code to it.
<?php
class DeleteClass
{
// connect to mysql database
public function __construct($hostname, $username, $password, $database)
{
$this->connection = new mysqli($hostname, $username, $password, $database);
if($this->connection->connect_errno)
die("Error " . $this->connection->connect_error);
}
// fetch all the user records from db
public function select_user()
{
$result = $this->connection->query("select * from users");
return $result->fetch_assoc();
}
// delete user record for given id
public function delete_user($id)
{
return $this->connection->query("delete from users where id = " . $id);
}
}
?>
As you can see we have created three methods inside the class,
- constructor method which establish the connection to mysql database during initialization.
- select_user() method which fetch and returns all the user records from
userstable. - delete_user($id) method which deletes the user record from mysql with the given
id.
Create index.php
Now create another file ‘index.php’ in your working folder and copy paste the below code.
<?php
include_once('DeleteClass.class.php');
// create new object and establish mysql db connection
$con = new DeleteClass('localhost','myusername','mypassword','customers');
// fetch user data
$result = $con->select_user();
// delete user record
if(isset($_GET['id']))
{
$con->delete_user($_GET['id']);
header('Location: index.php');
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP Delete MySQL Row with Confirmation Message Example</title>
<meta content="width=device-width, initial-scale=1.0" name="viewport" >
<link rel="stylesheet" href="path/to/bootstrap.css" type="text/css" />
<script type="text/javascript">
function delete_user(uid)
{
if (confirm('Are You Sure to Delete this Record?'))
{
window.location.href = 'index.php?id=' + uid;
}
}
</script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<table class="table table-bordered table-hover">
<thead>
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email ID</th>
<th>City</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php
$i = 1;
foreach($result as $row) { ?>
<tr>
<td><?php echo $i++; ?></td>
<td><?php echo $row['fname']; ?></td>
<td><?php echo $row['lname']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['city']; ?></td>
<td class="text-center"><a href="javascript: delete_user(<?php echo $row['id']; ?>)"><img src="images/delete_icon.png" alt="Delete" /></a></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>
First we load the class file we have created earlier with the line include_once('DeleteClass.php'); to callback the methods we have created earlier.
Next we create a new instance (object) of the class with the line,
$con = new DeleteClass('localhost', 'myusername', 'mypassword', 'customers');
The controller function will be invoked when creating new objects and this is where the database connection is established.
Next we fetch the user data by making a callback and store it in an array with this line,
$result = $con->select_user();
Later we loop through the $result array and display its contents in a neat tabular format. To style the html table element we had taken advantage of twitter bootstrap css and used its classes .table .table-bordered .table-hover. Learn more about twitter bootstrap table styles here.
Also there is one javascript function delete_user() which pops up the confirmation message to the users when they click on the delete button, and pass on the id value as an url parameter like this,
window.location.href = 'index.php?id=' + uid;
Then we get the id for the record to delete and make a callback to the delete_user() function like this,
<?php
if(isset($_GET['id']))
{
$con->delete_user($_GET['id']);
header('Location: index.php');
}
?>
The statement header('Location: index.php'); will redirect the page to the same url and refreshes the user table list after deletion.
That’s it. Now run the index file and the user details will be displayed in a neat table like this,
When the user clicks on the delete icon (X), the java script function will be invoked and the confirmation message pops up like this,
If the user confirms deletion with ok message, then page will be submitted to delete the particular user record from db and once again refresh the table list.
Recommended Read:- How to Import CSV File to MySQL Database using PHP
- How to Export MySQL Database to CSV File using PHP
And that was all about implementing data deletion process from mysql with confirmation message in PHP.
