Selasa, 15 Agustus 2017

Delete Multiple Records With PHP

Delete Multiple Records With PHP - To delete records stored in the database and display them on our site, there are times when we need to delete records. If we delete notes one by one, it will be a lot of time, so with that here we will try to learn how to delete multiple records at once.
Delete multiple records from MySQL in PHP
We will delete the existing records in some lines in the MYSQL database in php with the checkbox. In this tutorial, we will show some user table records using Mysql and php. Therefore, checkboxes must be displayed on each line.

When we will delete the data, it will popup to confirm. After confirmation then the data will be automatically deleted from the database. That way, we will not take much time to delete some notes in the click.

Delete multiple records from MySQL in PHP

users table:
Into the users table we have stored users first_name, last_name, email and phone fields.
CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
    `created` datetime NOT NULL,
    `modified` datetime NOT NULL,
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Deactive',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

To implement delete multiple records in PHP we have used 3 files, the files details are given below.

dbConfig.php:
This file is used to connect with the database.

<?php
    $dbHost = 'localhost';  //database host name
    $dbUser = 'root';       //database username
    $dbPass = '';           //database password
    $dbName = 'amazighCoder'; //database name
    $conn = mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
    if(!$conn){
        die("Database connection failed: " . mysqli_connect_error());
    }
?>

index.php:
At first you need to include jQuery library. JavaScript delete_confirm() function is used for display delete confirmation popup.

Into the $(document).ready(function(){}); we have wrote some jQuery for implement select all checkbox functionality.

<script src="jquery.min.js"></script>
<script type="text/javascript">
function deleteConfirm(){
    var result = confirm("Are you sure to delete users?");
    if(result){
        return true;
    }else{
        return false;
    }
}

$(document).ready(function(){
    $('#select_all').on('click',function(){
        if(this.checked){
            $('.checkbox').each(function(){
                this.checked = true;
            });
        }else{
             $('.checkbox').each(function(){
                this.checked = false;
            });
        }
    });
    
    $('.checkbox').on('click',function(){
        if($('.checkbox:checked').length == $('.checkbox').length){
            $('#select_all').prop('checked',true);
        }else{
            $('#select_all').prop('checked',false);
        }
    });
});
</script> 

For database access we have included the dbConfig.php file. Using MySQL we will fetch the users data from users table and display the users row into table using PHP.
<?php
    include_once('dbConfig.php');
    $query = mysqli_query($conn,"SELECT * FROM users");
?>
    
<form name="bulk_action_form" action="action.php" method="post" onsubmit="return deleteConfirm();"/>
    <table class="bordered">
        <thead>
        <tr>
            <th><input type="checkbox" name="select_all" id="select_all" value=""/></th>        
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Phone</th>
        </tr>
        </thead>
        <?php
            if(mysqli_num_rows($query) > 0){
                while($row = mysqli_fetch_assoc($query)){
        ?>
        <tr>
            <td align="center"><input type="checkbox" name="checked_id[]" class="checkbox" value="<?php echo $row['id']; ?>"/></td>        
            <td><?php echo $row['first_name']; ?></td>
            <td><?php echo $row['last_name']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['phone']; ?></td>
        </tr> 
        <?php } }else{ ?>
            <tr><td colspan="5">No records found.</td></tr> 
        <?php } ?>
    </table>
    <input type="submit" class="btn btn-danger" name="bulk_delete_submit" value="Delete"/>
</form>

Action.php:
Once the delete button is clicked the form would be submitted to the action.php file. In this file we will get the selected rows ID and delete records from users table depending on the respective ID. When the data deletion process is completed, we will store success message into SESSION and redirect to index.php.
<?php
    session_start();
    include_once('dbConfig.php');
    if(isset($_POST['bulk_delete_submit'])){
        $idArr = $_POST['checked_id'];
        foreach($idArr as $id){
            mysqli_query($conn,"DELETE FROM users WHERE id=".$id);
        }
        $_SESSION['success_msg'] = 'Users have been deleted successfully.';
        header("Location:index.php");
    }
?>

Note : This code does not protect against SQL injection attack

Silahkan berkomentar yang baik dan menarik sesuai dengan isi konten.
Komentar yang tidak diperbolehkan :

1. Berbau penghinaan SARA & PXXN
2. Komentar dengan Link hidup ( akan dianggap spam )
3. Komentar tidak nyambung dengan isi postingan
EmoticonEmoticon