Delete Multiple Records with Checkbox in PHP & MySQL

Hi! This time I have come up with a php tutorial that demonstrates about deleting multiple records with checkbox using PHP & MySQL database. Under CRUD management, we should provide option for deleting multiple rows from database as deleting records one by one would be time consuming. Also it is recommended to ask for user confirmation before deleting records to avoid accidental clicks. Here we'll see how to implement this multiple delete in php and mysql.

Delete Multiple Records in PHP MySQL using Checkbox:

As for the demo goes, we have to create a user interface with an html table displaying all user records from the database along with a checkbox on each row. The user has to tick the checkbox to pick on the rows to delete and click on the 'Delete' button provided underneath the table.

Just to improve user experience we also have to give an option for the user to select all checkboxes at once by checking on the checkbox placed on the header column of the table. Then we have to show a popup to confirm user action and once user acknowledges it, we have to delete all the selected records and refresh the users table and show some sort of notification.

Create Database:

Here is the mysql database we'd need for the demo.

CREATE DATABASE `db_demo`;
Use `db_demo`;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) 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, 'Daniel', 'Greyson', 'danielgreyson@hotmail.com', 'New York'),
(4, 'Julia', 'Brown', 'juliabrown@gmail.com', 'Los Angeles'),
(5, 'Rose', 'Harris', 'roseharris@gmail.com', 'New York');

HTML & PHP Code:

Here is the index.php file which contains the user interface we have discussed earlier and php code to delete multiple records from database.

index.php

<?php
// mysql connection
$hostname = "localhost";
$username = "root";
$password = "";
$dbname = "db_demo";

$con = mysqli_connect($hostname, $username, $password, $dbname) or die("Error: " . mysqli_error($con));

// fetch records
$result = @mysqli_query($con, "SELECT * FROM users") or die("Error: " . mysqli_error($con));

// delete records
if(isset($_POST['chk_id']))
{
    $arr = $_POST['chk_id'];
    foreach ($arr as $id) {
        @mysqli_query($con,"DELETE FROM users WHERE id = " . $id);
    }
    $msg = "Deleted Successfully!";
    header("Location: index.php?msg=$msg");
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Delete Multiple Rows Using PHP & MySQL</title>
    <meta content="width=device-width, initial-scale=1" name="viewport" >
    <link rel="stylesheet" href="css/bootstrap.css" type="text/css" />
</head>
<body>
<div class="container" style="margin-top: 20px;">
    <div class="row">
        <div class="col-md-8 col-md-offset-2">
            <form action="index.php" method="post">
            <?php if (isset($_GET['msg'])) { ?>
            <p class="alert alert-success"><?php echo $_GET['msg']; ?></p>
            <?php } ?>
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                    <th><input id="chk_all" name="chk_all" type="checkbox"  /></th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email ID</th>
                    <th>City</th>
                    </tr>
                </thead>
                <tbody>
                <?php while($row = mysqli_fetch_assoc($result)) { ?>
                <tr>
                    <td><input name="chk_id[]" type="checkbox" class='chkbox' value="<?php echo $row['id']; ?>"/></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>
                </tr>
                <?php } ?>
                </tbody>
            </table>
            <input id="submit" name="submit" type="submit" class="btn btn-danger" value="Delete Selected Row(s)" />
            </form>
        </div>
    </div>
</div>
<script src="js/jquery-1.10.2.js"></script>
<script type="text/javascript">
$(document).ready(function(){
    $('#chk_all').click(function(){
        if(this.checked)
            $(".chkbox").prop("checked", true);
        else
            $(".chkbox").prop("checked", false);
    });
});

$(document).ready(function(){
    $('#delete_form').submit(function(e){
        if(!confirm("Confirm Delete?")){
            e.preventDefault();
        }
    });
});
</script>
</body>
</html>

We have used two jquery functions in our code, the first one $('#chk_all').click() - which is triggered when the user check/uncheck the header column checkbox chk-all. It selects/deselects all the checkboxes based up the checked status of 'chk_all' checkbox.

And the next jquery function $('#delete_form').submit() will be fired when the user click on the #submit button which pops up a confirmation alert and submits the form only when user clicks OK button.

php-confirmation-box-before-delete-jquery

Once the records are deleted the page will be refreshed with the remaining set of user records and a success notification will be displayed on the page.

delete-multiple-records-in-php-mysql-using-checkbox

This demo uses procedural approach but if you want to do it with PHP PDO, then check for this tutorial.

Don't Miss: How to Import CSV into MySQL Database Using PHP

That explains about how to delete multiple records with checkbox in php and mysql database. You can customize the code to suit your need. Meet you in another interesting tutorial.

Delete Multiple Records with Checkbox in PHP & MySQL

1 comment:

Contact Form