How to delete data from database in codeigniter? 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.
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);
Delete Data from 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.
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 the delete_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 with codeigniter to create the user interface. Read this tutorial to know more about integrating 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.
Read:- CodeIgniter CRUD - Insert Database
- CodeIgniter CRUD - Read Database
- CodeIgniter CRUD - Update Database
That explains about deleting data from database in codeigniter. I hope you find this tutorial useful.
