CodeIgniter Bootstrap: Delete Data from Database Tutorial

Over sometime I have been writing tutorials about codeigniter crud process like insert, update and fetch. This tutorial is the last part of the codeigniter crud series and it is about database delete in codeigniter and bootstrap frameworks. For the delete process, we are going to use codeigniter delete query which allows us to delete one or more records from database table.

codeigniter-delete-database-query-example

Create MySQL Database

As for the database I'm going to use the same MySQL Database I have used in Insert and Update tutorials.

To create the sample Employee DB, run these sql commands in MySQL.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `tbl_department` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(80) NOT NULL,
  PRIMARY KEY (`department_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `tbl_department` (`department_id`, `department_name`) VALUES
(1, 'Finance'),
(2, 'HQ'),
(3, 'Operations'),
(4, 'Marketing'),
(5, 'Sales');

CREATE TABLE IF NOT EXISTS `tbl_designation` (
  `designation_id` int(4) NOT NULL AUTO_INCREMENT,
  `designation_name` varchar(50) NOT NULL,
  PRIMARY KEY (`designation_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `tbl_designation` (`designation_id`, `designation_name`) VALUES
(1, 'VP'),
(2, 'Manager'),
(3, 'Executive'),
(4, 'Trainee'),
(5, 'Senior Executive');

CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `employee_id` int(4) NOT NULL AUTO_INCREMENT,
  `employee_no` int(6) NOT NULL,
  `employee_name` varchar(60) NOT NULL,
  `department_id` int(4) NOT NULL,
  `designation_id` int(4) NOT NULL,
  `hired_date` date NOT NULL,
  `salary` int(10) NOT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `employee_no` (`employee_no`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `tbl_employee` (`employee_id`, `employee_no`, `employee_name`, `department_id`, `designation_id`, `hired_date`, `salary`) VALUES
(1, 1001, 'Steve John', 1, 2, '2013-08-01', 60000);

How to Delete Database in CodeIgniter?

For better explaining, I'm going to create a table list of employees with corresponding delete link on each row. These delete links will trigger the callback to a controller function delete_employe() by passing the employee id. Which in turn takes up the 'employee_id' as argument and uses the codeigniter delete query to delete the employee record with the given id. After deletion, the employee list will be refreshed to reflect the changes made in the database.

codeigniter-bootstrap-table-fetch-database-data

The Model ('models/employee_model.php')

<?php
/* 
 * File Name: employee_model.php
 */
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class employee_model extends CI_Model
{
    function __construct()
    {
        //Call the Model constructor
        parent::__construct();
    }

    //fetch all employee records
    function get_employee_list()
    {
        $this->db->from('tbl_employee');
        $this->db->join('tbl_department', 'tbl_employee.department_id = tbl_department.department_id');
        $this->db->join('tbl_designation', 'tbl_employee.designation_id = tbl_designation.designation_id');
        $query = $this->db->get();
        return $query->result();
    }
}
?>

For codeigniter delete process, the model requires only one method which is to fetch all the employee records from the table tbl_employee. And these employee details will be later on be used in the codeigniter view to display as a table list with bootstrap table components.

Moreover I have used join queries in the model function delete_employee(), to fetch the department and designation names from the respective DB tables. It's because displaying the department, designation names instead of id's in the table list will make them easily readable.

The Controller ('controllers/deleteemployee.php')

The controller will have two functions, one the index() itself which lists the employee details in a neat tabular format along with delete link at each row (This same approach can be used for database update too with update links). The second one is thedelete_employee() function which uses the codeigniter delete query statement to delete the employee row from database based on the employee id. Upon deletion, it redirects to the index() function to refresh the employee list page.

<?php
/* 
 * File Name: deleteemployee.php
 */
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class deleteemployee extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->helper('url');
        $this->load->database();
        //load the employee model
        $this->load->model('employee_model');
    }

    //index function
    function index()
    {
        //get the employee list
        $data['employee_list'] = $this->employee_model->get_employee_list();
        $this->load->view('delete_employee_view', $data);
    }

    //delete employee record from db
    function delete_employee($id)
    {
        //delete employee record
        $this->db->where('employee_id', $id);
        $this->db->delete('tbl_employee');
        redirect('deleteemployee/index');
    }
}
?>

The View ('views/delete_employee_view.php')

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Delete Database Demo</title>
    <!--link the bootstrap css file-->
    <link href="<?php echo base_url("assets/bootstrap/css/bootstrap.css"); ?>" rel="stylesheet" type="text/css" />
</head>
<body>
<br><br>
<div class="container">
    <div class="row">
        <div class="col-md-8">
            <table class="table table-striped table-hover">
                <thead>
                    <tr class="bg-primary">
                        <th>#</th>
                        <th>Employee No</th>
                        <th>Employee Name</th>
                        <th>Department</th>
                        <th>Designation</th>
                        <th>Delete</th>
                    </tr>
                </thead>
                <tbody>
                    <?php for ($i = 0; $i < count($employee_list); $i++) { ?>
                    <tr>
                        <td><?php echo ($i+1); ?></td>
                        <td><?php echo $employee_list[$i]->employee_no; ?></td>
                        <td><?php echo $employee_list[$i]->employee_name; ?></td>
                        <td><?php echo $employee_list[$i]->department_name; ?></td>
                        <td><?php echo $employee_list[$i]->designation_name; ?></td>
                        <td><a href="<?php echo base_url() . "index.php/deleteemployee/delete_employee/" . $employee_list[$i]->employee_id; ?>">Delete</a></td>
                    </tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

As you can see in the above view file, I have used bootstrap css framework withcodeigniter to create the user interface. Read this tutorial to know more aboutintegrating twitter bootstrap with codeigniter.

Bootstrap provides powerful css components for designing tables and I have used it to create a nice table list of employee details. As I have mentioned earlier in this tutorial, there is a delete link included for each row which will callback the delete_employee() function in the controller by passing employee id.

Note: As a general rule of thumb, when writing Codeigniter CRUD process, just use a single model and controller file like employee_model.php and employee.php. Then write all the required database activities for the entire process in the same model file. In the controller write individual CRUD operations as separate functions like add_employee(), update_employee(), fetch_employee() and delete_employee(). As for the view files, use separate view files to list, add/update depending upon the requirement.

That's it! Now you can easily delete data from database using codeigniter and bootstrap framework following this process.

codeigniter