Delete Multiple Records from Database in CodeIgniter

Delete multiple records on a single click is very useful for the large data list. This feature provides a user-friendly way to remove multiple records from the database quickly. The user doesn’t need to click multiple times for delete multiple rows, instead of that, all records can be deleted on a single click.

Multiple records deletion functionality can be easily implemented using checkboxes. You can use the checkbox to select each record in the data list and delete all the selected records from the database. Also, the Select / Deselect all checkboxes feature will make it easy to select multiple records because it allows the user to check or uncheck all rows at once. In this tutorial, we will show you how to delete multiple records from database using checkbox in CodeIgniter.

The example code, the following steps will be followed to delete multiple records from database in CodeIgniter.

• Retrieve all users data from the MySQL database and list them in an HTML table.
• Attach checkbox to each row to select multiple records.
• Add a checkbox in the table header to check or uncheck all checkboxes on a single click.
• Delete button to remove all checked rows from users table in the MySQL database.

Create Database Table

To store the user’s data a table needs to be created in the database. The following SQL creates a users table with some basic fields in the MySQL database.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(200) 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;

Controller (Users.php)

The Users controller contains 2 functions, __construct() and index().
__construct() – Load the User model to fetch data from the database.

index() –

• Fetch users data from the database using getRows() function of the User model.
• Pass the user data to view and load this view.
• If the delete request is submitted:
  • Get selected rows ID using $_POST in PHP.
  • Check whether the user select at least one records and the ID array is not empty.
  • Based on the selected user’s ID, delete records from the database using delete() function of the User model.
• Pass the status message to the view.

<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Users extends CI_Controller {
    
    function __construct() {
        parent::__construct();
        
        // Load user model
        $this->load->model('user');
    }
    
    public function index(){
        $data = array();
        
        // If record delete request is submitted
        if($this->input->post('bulk_delete_submit')){
            // Get all selected IDs
            $ids = $this->input->post('checked_id');
            
             // If id array is not empty
            if(!empty($ids)){
                // Delete records from the database
                $delete = $this->user->delete($ids);
                
                // If delete is successful
                if($delete){
                    $data['statusMsg'] = 'Selected users have been deleted successfully.';
                }else{
                    $data['statusMsg'] = 'Some problem occurred, please try again.';
                }
            }else{
                $data['statusMsg'] = 'Select at least 1 record to delete.';
            }
        }
        
        // Get user data from the database
        $data['users'] = $this->user->getRows();
        
        // Pass the data to view
        $this->load->view('users/index', $data);
    }
    
}

Model (User.php)

The User model handles the database related works.

• __construct() – Specify the table name of the database.
• getRows() – Fetch the records from the users table based on the specified conditions and returns as an array.
• delete() – Delete records from the users table based on the specified ID.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class User extends CI_Model{
    
    function __construct() {
        $this->tblName = 'users';
    }
    
    /*
     * Fetch posts data from the database
     * @param id returns a single record if specified, otherwise all records
     */
    function getRows($params = array()){
        $this->db->select('*');
        $this->db->from($this->tblName);
        
        //fetch data by conditions
        if(array_key_exists("where",$params)){
            foreach ($params['where'] as $key => $value){
                $this->db->where($key,$value);
            }
        }
        
        if(array_key_exists("order_by",$params)){
            $this->db->order_by($params['order_by']);
        }
        
        if(array_key_exists("id",$params)){
            $this->db->where('id',$params['id']);
            $query = $this->db->get();
            $result = $query->row_array();
        }else{
            //set start and limit
            if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
                $this->db->limit($params['limit'],$params['start']);
            }elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
                $this->db->limit($params['limit']);
            }
            
            if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
                $result = $this->db->count_all_results();
            }else{
                $query = $this->db->get();
                $result = ($query->num_rows() > 0)?$query->result_array():FALSE;
            }
        }

        //return fetched data
        return $result;
    }
    
    /*
     * Delete data from the database
     * @param id array/int
     */
    public function delete($id){
        if(is_array($id)){
            $this->db->where_in('id', $id);
        }else{
            $this->db->where('id', $id);
        }
        $delete = $this->db->delete($this->tblName);
        return $delete?true:false;
    }
    
}

View (users/index.php)

The jQuery is used to show the delete confirmation dialog and integrate select all checkboxes functionality. So, include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

The following jQuery is used for confirmation dialog and select all checkboxes functionality.

• The delete_confirm() function checks whether the user selects at least one checkbox and display an alert or confirmation dialog before submitting the form to Users controller for delete multiple records.
• The Select / Deselect All CheckBoxes using jQuery helps the user to check / uncheck all checkboxes on a single click.

<script>
function delete_confirm(){
    if($('.checkbox:checked').length > 0){
        var result = confirm("Are you sure to delete selected users?");
        if(result){
            return true;
        }else{
            return false;
        }
    }else{
        alert('Select at least 1 record to delete.');
        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>

Initially, all the records from the users table are listed in an HTML table. The user can select single or multiple rows in the table and delete multiple records from the MySQL database in CodeIgniter application.

• All the user’s data are retrieved from the database.
• Multiple rows can be selected using checkbox provided on each table row.
• By clicking the checkbox in the table header, all checkboxes will be checked or unchecked.
• Once the delete button is clicked, a dialog will appear for the confirmation.
• After the confirmation, the form is submitted to delete selected records from the database.

<!-- Display the status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="alert alert-success"><?php echo $statusMsg; ?></div>
<?php } ?>

<!-- Users data list -->
<form name="bulk_action_form" action="" method="post" onSubmit="return delete_confirm();"/>
    <table class="bordered">
        <thead>
        <tr>
            <th><input type="checkbox" id="select_all" value=""/></th>        
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Phone</th>
        </tr>
        </thead>
        <?php if(!empty($users)){ foreach($users as $row){ ?>
        <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>

Delete Multiple Records from MySQL Database in PHP

Conclusion

If you want to make the data management section user-friendly, multiple delete is a must-have functionality for your CodeIgniter application. It provides an effective way to delete multiple records in CodeIgniter. Using our example code, you can check/uncheck all records at once, get multiple checked checkbox value in CodeIgniter and delete selected rows from the database on a single click.