The most commonly utilised capability in a CodeIgniter application is CRUD Operations. The Codeigniter CRUD Operations help you manipulate data in the database (Fetch, Insert, Update, and Delete). CRUD in CodeIgniter allows you to view, add, edit, and delete data from the database. In CodeIgniter, CRUD is highly handy for integrating data management functions.
You can add search and pagination to the Codeigniter CRUD application to make it more user-friendly. The data management part must offer search and pagination functionality. Search and pagination are suggested features for the data list while creating the CodeIgniter CRUD application. We’ll teach you how to use MySQL to implement CRUD functions in CodeIgniter, including search and pagination.
The following functionality will be implemented in the example code to connect CRUD with a search filter and pagination in Codeigniter.
Get the members’ information from the database and display it on a web page.
Using the CodeIgniter Pagination library, add pagination links to the data list.
The records can be searched and filtered.
Fill in the blanks in the database with information about the members.
In the database, edit and update member information.
Remove all member information from the database.
Examine the file structure before beginning to build a CodeIgniter CRUD application with search and pagination.
codeigniter_crud/ ├── application/ │ ├── controllers/ │ │ └── Members.php │ ├── models/ │ │ └── Member.php │ └── views/ │ ├── templates/ │ │ ├── header.php │ │ └── footer.php │ └── members/ │ ├── index.php │ ├── view.php │ └── add-edit.php └── assets/ ├── css/ └── images/
Make a database table.
A table in the database must be built to store the member’s information. In the MySQL database, the following SQL creates a members table with some basic columns.
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(20) 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=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Config autoload.php
Define the library and helper you want to load automatically on every request in the config/autoload.php file.
$autoload['libraries'] = array('database', 'session');
$autoload['helper'] = array('url');
the supervisor (Members.php)
The CRUD actions are handled by the Members controller (view, add, edit, and delete).
__construct() – Loads the library, helper, and model that are required. Define the maximum number of records that can be displayed on each page.
index() – The getRows() function of the Member model is used to list the data of members.
SESSION status messages can be retrieved.
Retrieve the search phrase and save it in the SESSION if the search request was submitted.
Create a new instance of the Pagination library.
Using the search terms, retrieve records from the database.
Load the list view after passing the members’ data.
view() — Using the getRows() function of the Member model, display specific member data.
Using the relevant ID, retrieve member info from the database.
Load the details view after passing the member data.
insert() – The insert() method of the Member model is used to add member data to the database.
The form view is initially loaded in order to receive user input.
The uploaded form data is validated using the CodeIgniter Form Validation library if the form is submitted.
Input member information into the database.
edit() – The update() method of the Member model allows you to edit and update specific member data.
Using the relevant ID, retrieve member info from the database.
The pre-filled member data is loaded into the form view.
If you fill out the form and submit it, you will receive a confirmation email.
The CodeIgniter Form Validation library is used to validate the data from the submitted form.
In the database, update member information.
delete() – The delete() method of the Member model is used to remove member data from the database.
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Members extends CI_Controller {
function __construct() {
parent::__construct();
// Load member model
$this->load->model('member');
// Load form helper and library
$this->load->helper('form');
$this->load->library('form_validation');
// Load pagination library
$this->load->library('pagination');
// Per page limit
$this->perPage = 5;
}
public function index(){
$data = array();
// Get messages from the session
if($this->session->userdata('success_msg')){
$data['success_msg'] = $this->session->userdata('success_msg');
$this->session->unset_userdata('success_msg');
}
if($this->session->userdata('error_msg')){
$data['error_msg'] = $this->session->userdata('error_msg');
$this->session->unset_userdata('error_msg');
}
// If search request submitted
if($this->input->post('submitSearch')){
$inputKeywords = $this->input->post('searchKeyword');
$searchKeyword = strip_tags($inputKeywords);
if(!empty($searchKeyword)){
$this->session->set_userdata('searchKeyword',$searchKeyword);
}else{
$this->session->unset_userdata('searchKeyword');
}
}elseif($this->input->post('submitSearchReset')){
$this->session->unset_userdata('searchKeyword');
}
$data['searchKeyword'] = $this->session->userdata('searchKeyword');
// Get rows count
$conditions['searchKeyword'] = $data['searchKeyword'];
$conditions['returnType'] = 'count';
$rowsCount = $this->member->getRows($conditions);
// Pagination config
$config['base_url'] = base_url().'members/index/';
$config['uri_segment'] = 3;
$config['total_rows'] = $rowsCount;
$config['per_page'] = $this->perPage;
// Initialize pagination library
$this->pagination->initialize($config);
// Define offset
$page = $this->uri->segment(3);
$offset = !$page?0:$page;
// Get rows
$conditions['returnType'] = '';
$conditions['start'] = $offset;
$conditions['limit'] = $this->perPage;
$data['members'] = $this->member->getRows($conditions);
$data['title'] = 'Members List';
// Load the list page view
$this->load->view('templates/header', $data);
$this->load->view('members/index', $data);
$this->load->view('templates/footer');
}
public function view($id){
$data = array();
// Check whether member id is not empty
if(!empty($id)){
$data['member'] = $this->member->getRows(array('id' => $id));;
$data['title'] = 'Member Details';
// Load the details page view
$this->load->view('templates/header', $data);
$this->load->view('members/view', $data);
$this->load->view('templates/footer');
}else{
redirect('members');
}
}
public function add(){
$data = array();
$memData = array();
// If add request is submitted
if($this->input->post('memSubmit')){
// Form field validation rules
$this->form_validation->set_rules('first_name', 'first name', 'required');
$this->form_validation->set_rules('last_name', 'last name', 'required');
$this->form_validation->set_rules('email', 'email', 'required|valid_email');
$this->form_validation->set_rules('gender', 'gender', 'required');
$this->form_validation->set_rules('country', 'country', 'required');
// Prepare member data
$memData = array(
'first_name'=> $this->input->post('first_name'),
'last_name' => $this->input->post('last_name'),
'email' => $this->input->post('email'),
'gender' => $this->input->post('gender'),
'country' => $this->input->post('country')
);
// Validate submitted form data
if($this->form_validation->run() == true){
// Insert member data
$insert = $this->member->insert($memData);
if($insert){
$this->session->set_userdata('success_msg', 'Member has been added successfully.');
redirect('members');
}else{
$data['error_msg'] = 'Some problems occured, please try again.';
}
}
}
$data['member'] = $memData;
$data['title'] = 'Add Member';
// Load the add page view
$this->load->view('templates/header', $data);
$this->load->view('members/add-edit', $data);
$this->load->view('templates/footer');
}
public function edit($id){
$data = array();
// Get member data
$memData = $this->member->getRows(array('id' => $id));
// If update request is submitted
if($this->input->post('memSubmit')){
// Form field validation rules
$this->form_validation->set_rules('first_name', 'first name', 'required');
$this->form_validation->set_rules('last_name', 'last name', 'required');
$this->form_validation->set_rules('email', 'email', 'required|valid_email');
$this->form_validation->set_rules('gender', 'gender', 'required');
$this->form_validation->set_rules('country', 'country', 'required');
// Prepare member data
$memData = array(
'first_name'=> $this->input->post('first_name'),
'last_name' => $this->input->post('last_name'),
'email' => $this->input->post('email'),
'gender' => $this->input->post('gender'),
'country' => $this->input->post('country')
);
// Validate submitted form data
if($this->form_validation->run() == true){
// Update member data
$update = $this->member->update($memData, $id);
if($update){
$this->session->set_userdata('success_msg', 'Member has been updated successfully.');
redirect('members');
}else{
$data['error_msg'] = 'Some problems occured, please try again.';
}
}
}
$data['member'] = $memData;
$data['title'] = 'Update Member';
// Load the edit page view
$this->load->view('templates/header', $data);
$this->load->view('members/add-edit', $data);
$this->load->view('templates/footer');
}
public function delete($id){
// Check whether member id is not empty
if($id){
// Delete member
$delete = $this->member->delete($id);
if($delete){
$this->session->set_userdata('success_msg', 'Member has been removed successfully.');
}else{
$this->session->set_userdata('error_msg', 'Some problems occured, please try again.');
}
}
// Redirect to the list page
redirect('members');
}
}Modeling (Member.php)
The Member model is in charge of database-related tasks (Fetch, Add, Edit, and Delete).
Define the table name with __construct().
getRows() – Retrieves the members’ data from the database using the parameters supplied in $params. On success, the filtered records are returned.
insert() — Inserts data from a member into the database. If successful, returns the row ID; if unsuccessful, returns FALSE.
update() – Based on the row ID, update member data in the database. If the function succeeds, it returns TRUE; if it fails, it returns FALSE.
remove() – Using the row ID, delete a record from the database. If the function succeeds, it returns TRUE; if it fails, it returns FALSE.
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Member extends CI_Model{
function __construct() {
// Set table name
$this->table = 'members';
}
/*
* Fetch members data from the database
* @param array filter data based on the passed parameters
*/
function getRows($params = array()){
$this->db->select('*');
$this->db->from($this->table);
if(array_key_exists("conditions", $params)){
foreach($params['conditions'] as $key => $val){
$this->db->where($key, $val);
}
}
if(!empty($params['searchKeyword'])){
$search = $params['searchKeyword'];
$likeArr = array('first_name' => $search, 'last_name' => $search, 'email' => $search);
$this->db->or_like($likeArr);
}
if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
$result = $this->db->count_all_results();
}else{
if(array_key_exists("id", $params)){
$this->db->where('id', $params['id']);
$query = $this->db->get();
$result = $query->row_array();
}else{
$this->db->order_by('first_name', 'asc');
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']);
}
$query = $this->db->get();
$result = ($query->num_rows() > 0)?$query->result_array():FALSE;
}
}
// Return fetched data
return $result;
}
/*
* Insert members data into the database
* @param $data data to be insert based on the passed parameters
*/
public function insert($data = array()) {
if(!empty($data)){
// Add created and modified date if not included
if(!array_key_exists("created", $data)){
$data['created'] = date("Y-m-d H:i:s");
}
if(!array_key_exists("modified", $data)){
$data['modified'] = date("Y-m-d H:i:s");
}
// Insert member data
$insert = $this->db->insert($this->table, $data);
// Return the status
return $insert?$this->db->insert_id():false;
}
return false;
}
/*
* Update member data into the database
* @param $data array to be update based on the passed parameters
* @param $id num filter data
*/
public function update($data, $id) {
if(!empty($data) && !empty($id)){
// Add modified date if not included
if(!array_key_exists("modified", $data)){
$data['modified'] = date("Y-m-d H:i:s");
}
// Update member data
$update = $this->db->update($this->table, $data, array('id' => $id));
// Return the status
return $update?true:false;
}
return false;
}
/*
* Delete member data from the database
* @param num filter data based on the passed parameter
*/
public function delete($id){
// Delete member data
$delete = $this->db->delete($this->table, array('id' => $id));
// Return the status
return $delete?true:false;
}
}The elements (header, footer, etc.) of web pages are stored in the views/templates/ directory.
templates/header.php
The header portion of the web page is stored in this file. The HTML table and form are styled with the Bootstrap 4 library. As a result, include the Bootstrap library’s CSS file as well as the custom stylesheet file (if any).
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title><?php echo $title; ?> | CodeIgniter CRUD with Search and Pagination</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css">
<link rel="stylesheet" href="<?php echo base_url('assets/css/style.css'); ?>">
</head>
<body>templates/footer.php
The footer of the web page is stored in this file.
</body>
</html>members/ The views/members/ directory contains the Members controller’s view files.
members/index.php
At first, all of the members’ information is fetched from the database and shown on a webpage with links to Add, Edit, and Delete them.
The CRUD data list now includes a search option. The search input area allows the user to filter and sort the members list.
Using the Pagination class’s create links() function, pagination links are added to the bottom of the CRUD data list.
The View button takes the user to the members/view page, where he or she can perform a Read activity.
The Add link takes the user to the members/add page, where they can conduct the Create action.
The Edit link takes the user to the members/edit page, where they can update their information.
The Delete link takes the user to the members/delete page, where they can delete something.
<div class="container">
<h2><?php echo $title; ?></h2>
<!-- Display status message -->
<?php if(!empty($success_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-success"><?php echo $success_msg; ?></div>
</div>
<?php }elseif(!empty($error_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-danger"><?php echo $error_msg; ?></div>
</div>
<?php } ?>
<div class="row">
<div class="col-md-12 search-panel">
<!-- Search form -->
<form method="post">
<div class="input-group mb-3">
<input type="text" name="searchKeyword" class="form-control" placeholder="Search by keyword..." value="<?php echo $searchKeyword; ?>">
<div class="input-group-append">
<input type="submit" name="submitSearch" class="btn btn-outline-secondary" value="Search">
<input type="submit" name="submitSearchReset" class="btn btn-outline-secondary" value="Reset">
</div>
</div>
</form>
<!-- Add link -->
<div class="float-right">
<a href="<?php echo site_url('members/add/'); ?>" class="btn btn-success"><i class="plus"></i> New Member</a>
</div>
</div>
<!-- Data list table -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php if(!empty($members)){ foreach($members as $row){ ?>
<tr>
<td><?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['gender']; ?></td>
<td><?php echo $row['country']; ?></td>
<td>
<a href="<?php echo site_url('members/view/'.$row['id']); ?>" class="btn btn-primary">view</a>
<a href="<?php echo site_url('members/edit/'.$row['id']); ?>" class="btn btn-warning">edit</a>
<a href="<?php echo site_url('members/delete/'.$row['id']); ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?')">delete</a>
</td>
</tr>
<?php } }else{ ?>
<tr><td colspan="7">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
<!-- Display pagination links -->
<div class="pagination pull-right">
<?php echo $this->pagination->create_links(); ?>
</div>
</div>
</div>
members/view.php
The view() function of the members controller loads this view. The Bootstrap card view displays the specific member details.
<div class="container">
<h2><?php echo $title; ?></h2>
<div class="col-md-6">
<div class="card" style="width:400px">
<div class="card-body">
<h4 class="card-title"><?php echo $member['first_name'].' '.$member['last_name']; ?></h4>
<p class="card-text"><b>Email:</b> <?php echo $member['email']; ?></p>
<p class="card-text"><b>Gender:</b> <?php echo $member['gender']; ?></p>
<p class="card-text"><b>Country:</b> <?php echo $member['country']; ?></p>
<p class="card-text"><b>Created:</b> <?php echo $member['created']; ?></p>
<a href="<?php echo site_url('members'); ?>" class="btn btn-primary">Back To List</a>
</div>
</div>
</div>
</div>members/add-edit.php
The members controller’s add() and edit() methods load this view.
An HTML form is displayed in response to an add request to receive the user’s input (name, edit, gender, country, etc.).
An HTML form is displayed with pre-filled member data in the input fields when an edit request is made.
<div class="container">
<h2><?php echo $title; ?></h2>
<!-- Display status message -->
<?php if(!empty($success_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-success"><?php echo $success_msg; ?></div>
</div>
<?php }elseif(!empty($error_msg)){ ?>
<div class="col-xs-12">
<div class="alert alert-danger"><?php echo $error_msg; ?></div>
</div>
<?php } ?>
<div class="row">
<div class="col-md-6">
<form method="post">
<div class="form-row">
<div class="col-md-6 mb-3">
<label>First name</label>
<input type="text" class="form-control" name="first_name" placeholder="Enter first name" value="<?php echo !empty($member['first_name'])?$member['first_name']:''; ?>" >
<?php echo form_error('first_name','<div class="invalid-feedback">','</div>'); ?>
</div>
<div class="col-md-6 mb-3">
<label>Last name</label>
<input type="text" class="form-control" name="last_name" placeholder="Enter last name" value="<?php echo !empty($member['last_name'])?$member['last_name']:''; ?>" >
<?php echo form_error('last_name','<div class="invalid-feedback">','</div>'); ?>
</div>
</div>
<div class="form-group">
<label>Email</label>
<input type="text" class="form-control" name="email" placeholder="Enter email" value="<?php echo !empty($member['email'])?$member['email']:''; ?>" >
<?php echo form_error('email','<div class="invalid-feedback">','</div>'); ?>
</div>
<div class="form-group">
<label>Gender</label>
<div class="custom-control custom-radio custom-control-inline">
<input type="radio" id="gender1" name="gender" class="custom-control-input" value="Male" <?php echo empty($member['gender']) || (!empty($member['gender']) && ($member['gender'] == 'Male'))?'checked="checked"':''; ?> >
<label class="custom-control-label" for="gender1">Male</label>
</div>
<div class="custom-control custom-radio custom-control-inline">
<input type="radio" id="gender2" name="gender" class="custom-control-input" value="Female" <?php echo (!empty($member['gender']) && ($member['gender'] == 'Female'))?'checked="checked"':''; ?> >
<label class="custom-control-label" for="gender2">Female</label>
</div>
<?php echo form_error('gender','<div class="invalid-feedback">','</div>'); ?>
</div>
<div class="form-group">
<label>Country</label>
<input type="text" class="form-control" name="country" placeholder="Enter country" value="<?php echo !empty($member['country'])?$member['country']:''; ?>" >
<?php echo form_error('country','<div class="invalid-feedback">','</div>'); ?>
</div>
<a href="<?php echo site_url('members'); ?>" class="btn btn-secondary">Back</a>
<input type="submit" name="memSubmit" class="btn btn-success" value="Submit">
</form>
</div>
</div>
</div>Delete the index.php file from the URL.
The index.php file is included in the URLs of CRUD operation requests by default. To remove index.php from URLs in CodeIgniter, follow the instructions below.
Remove index.php from the index page variable in the config/config.php file and leave it blank.
$config['index_page'] = '';In the CodeIgniter root directory, create an HTACCESS (.htaccess) file and paste the following URL in it. Replace the Rule code.
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L]It’s important to note that your server’s mod rewrite extension must be enabled. You must specify the path according to your application’s location (RewriteRule (.*)$ /folder name/index.php/$1 [L]).
NOTE :
Our CodeIgniter with MySQL sample CRUD application demonstrates how to use the CodeIgniter framework to perform data management (fetch, insert, update, and delete) operations. To make the CodeIgniter CRUD data list more user-friendly, this example script adds pagination and search features. Because we utilised the Bootstrap toolkit to style the form and table, only a small amount of CSS is needed to create the list and form views. You may also add search and pagination script features to the CodeIgniter CRUD to suit your needs.