Hello , friends in this article we will show you . how to delete multiple record from database . Mostly we have to need delete single record but according to project size some times we have to need delete multiple record from database .
So in this this tutorials we learn how to delete multiple record using php and mysql database . By the way we can use the for loop or foor each loop for delete multiple record . but here we use something different . We are using In clause for delete multiple record . lets start
First you have to create index.php file. inside the file you will create a form with POST method . Inside the form we will create a checkbox name = "select_all" and id = "select_all"
- <form action="" method="post">
- <div class="row">
- <div class="col-sm-8">
- <button class="btn btn-danger" type="submit" name="delete_record" id="delete_record" style="margin-bottom:10px;"><i class="glyphicon glyphicon-trash"></i> Delete All</button>
- <?php
- if(isset($_SESSION['msg'])){
- echo $_SESSION['msg'];
- }
- ?>
- <table class="table table-striped table-bordered">
- <thead>
- <tr class="danger">
- <th><input type="checkbox" name="select_all" id="select_all"></th>
- <th>SN</th>
- <th>Title </th>
- </tr>
- </thead>
- <tbody>
- <?php
- $sql = "SELECT * FROM multiple_record_countries ORDER by id DESC LIMIT {$limit} , {$perpage} ";
- $result = $obj->execute_query($sql);
- if($result != FALSE){
- $sn = (($_GET['page'] - 1) * 20 ) + 1;
- foreach($result as $row){
- ?>
- <tr>
- <td><input type="checkbox" name="id[]" class="post_checkbox" value="<?php echo $row['id']; ?>"></td>
- <td><?php echo $sn; ?></td>
- <td><?php echo $row['name']; ?></td>
- </tr>
- <?php
- $sn++;
- }
- }
- else{
- ?>
- <tr>
- <td colspan="6">
- <P class="alert alert-info">No Record Found</P>
- </td>
- </tr>
- <?php
- }
- ?>
- </tbody>
- </table>
- </div>
- </div>
-
- </form>
Use this jquery code for checked and unchecked checkbox . If you want to delete a specific record you can checked perticular checkbox . If you want to delete multiple record you can checked SELECT ALL checkbox .
- $(document).ready(function(e) {
- $(document).on('click','#select_all',function(e){
- if( $(this).is(":checked") ){
- $(".post_checkbox").prop('checked',true);
- }
- else{
- $(".post_checkbox").prop('checked',false);
- }
- });
- });
When you click the submit button then you have to follow this procedure . If you checked Select All button and submit the form then delete all single page record .
- $table_name = "multiple_record_countries";
- if(isset($_POST['delete_record'])){
- if(count($_POST['id']) > 0){
- $id_arr = implode(',' , $_POST['id']);
- $where_con = "`id` IN (".$id_arr.")";
- $delete_response = $obj->delete_multiple_record($table_name , $where_con);
- if($delete_response){
- $_SESSION['msg'] = $obj->has_message('Record delete successful !!!');
- header('Location: ' . $_SERVER['HTTP_REFERER']);
- }
- }
- else{
- header('Location: ' . $_SERVER['HTTP_REFERER']);
- }
- }
- <?php
- public function delete_multiple_record($table_name , $con){
- $sql = "DELETE FROM ".$table_name." WHERE ".$con;
- $q = $this->db->prepare( $sql );
- $stmt = $q->execute();
- if( $stmt ) { return TRUE; }
- else{ return FALSE; }
- }
- ?>