When you create a web application for managing your data such as accounting, stock, and sale orders. You will need the reports which come with the filters that allow you to view only the data you need. This post will show you how to create a filter form and filter the data in Datatable in CodeIgniter. You can apply this method to other PHP frameworks. It will work the same way.
If you want to learn how to integrate the Datatable plugin in CodeIgniter, you can read this post below.
If you want to learn how to add, edit, and delete the data from the Datatable plugin in CodeIgniter, you can read the following posts.
Adding filter form
Let’s say, you already integrated the Datatable plugin on the view page. If you don’t know how, check this post. Now, you will add the filter form below.
The filter form will filter only the title field from my news table(id, slug, title, text). You can add more input fields you like to meet your case. Just need to add the input fields within the form tag.
Below is my filter form in the view page.
<!-- Filters -->
<div class="container">
<div class="row">
<div class="col-md-12">
<form action="" id="frm_filters" name="frm_filters">
<div class="form-group">
<label for=""><b>Filter</b></label>
<input type="text" name="filter_title" id="filter_title" class="col-md-8" maxlength="128" minlength="1">
<input type="submit" name="frm-filter-btn" id="frm-filter-btn" class="col-md-2 btn-warning" value="Search">
</div>
</form>
</div>
</div>
</div>
<hr>
<!-- data -->
<div class="container">
<div class="row">
<div class="col-md-12">
<!-- Add New Data button -->
<button class="btn btn-primary mb-3" data-toggle="modal" data-target="#add_new_modal">Add New Data</button>
<!-- Render data in datatable plugin -->
<table id="rep_news" class="table table-striped table-bordered table-hover" style="width:100%">
<thead>
<tr>
<th>Id</th>
<th>Title</th>
<th>Slug</th>
<th>Text</th>
<th></th>
</tr>
</thead>
<tfoot>
<tr>
<th>Id</th>
<th>Title</th>
<th>Slug</th>
<th>Text</th>
<th></th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>Fetch data filtered by the filter form
On the filter form, when users click on the search button, we will fetch the data from the news table and render it in the Datatable.
Below is the JS code that I add at the footer of the HTML template. Always add the JS code or JS file at the footer for the best performance.
However, some JS codes require to add in the head section such as Google Analytics tag.
$(document).ready(function() {
$('#frm-filter-btn').on("click", function() {
event.preventDefault();
var table = $('#rep_news');
$.ajax({
url: "<?= base_url('index.php/news/get_news_by_filters') ?>",
dataType: 'JSON',
method: 'POST',
data: {
'filter_news_title': $('#filter_title').val()
},
success: function(data_return) {
console.log(data_return);
// destroy the DataTable
table.dataTable().fnDestroy();
// clear the table body
table.find('tbody').empty();
// reinitiate
table.DataTable({
// # data source as object (JSON object array)
// You must use the exactly format as shown on the link below
// https://datatables.net/manual/data/#Objects
data: data_return,
columns: [{
"data": "id"
},
{
"data": "title"
},
{
"data": "slug"
},
{
"data": "text"
},
{
"data": null
},
],
columnDefs: [{
// # hide the first column
// https://datatables.net/examples/advanced_init/column_render.html
"targets": [0],
// "visible": false
},
{
// # disable search for column number 2
// https://datatables.net/reference/option/columns.searchable
"targets": [3],
"searchable": false,
// # disable orderable column
// https://datatables.net/reference/option/columns.orderable
"orderable": false
},
{
// # action controller (edit,delete)
"targets": [4],
// # column rendering
// https://datatables.net/reference/option/columns.render
"render": function(data, type, row, meta) {
$controlls = '<button class="btn btn-sm btn-info" data-toggle="modal" data-target="#edit_news_modal" data-id="' + row.id + '" data-title="' + row.title + '" data-slug="' + row.slug + '" data-text="' + row.text + '">Edit</button>';
$controlls += '<button class="btn btn-sm btn-danger ml-2" data-toggle="modal" data-target="#delete_news_modal" data-id="' + row.id + '" data-title="' + row.title + '" data-slug="' + row.slug + '" data-text="' + row.text + '">Delete</button>';
return $controlls;
},
"width": 100
}
],
// #set order descending and ascending
// https: //datatables.net/reference/option/order
"order": [
[1, 'desc'],
[2, 'asc']
]
});
}
});
});
}); // document ready endsWhen users click on the search button, the JS code above will activate. We are using Ajax and send the request along with the filter value to “get_news_by_filters” method in the news controller which we don’t create yet.
We send the request as JSON with the POST method. We use the POST method so the filter value won’t show on the URI.
We pass the filter value using data option which is a JSON data type.
If Ajax receives the response and no error occurs, Ajax will continue on success function. This is an Ajax basic. If you are a web developer, I don’t think you will have any issues using Ajax but if you do, please learn the basics from here before continuing further.
Within the success function, you will destroy the Datatable object first, if you initial it with the rep_news table before. Then you will remove the tbody tag to make sure no extra unused tags left. Then you reinitiate the Datatable object with the rep_news table again.
For the minimum options that Datatable needs to work, Only two Datatable options are requested which are data and columns.
The data option, in this case, I use the object as a data source for Datatable. The Format looks like below. It is a JSON object array.
[
{
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$3,120",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"name": "Garrett Winters",
"position": "Director",
"salary": "$5,300",
"start_date": "2011/07/25",
"office": "Edinburgh",
"extn": "8422"
}
]The columns option tells Datatable how many columns from the data source will match the columns in the table. We will use the object key from the data source and assign it to the data key of the columns object.
Note that, the member of columns array must be the same number of <th> tag in our table in the HTML template.
Extra column on my table
In my JS code above, the last column will render the Edit and Delete buttons which don’t come from the field in the database but I need the last column for those buttons in my table. So I assign the null value to the last data key for my purpose.
Then I will write the callback function to render those buttons on the last column in the table by using columnDefs options. In my JS code above, you will see targets[4] which refers to the last column on the table. You will see I write the callback function to render the buttons I need.
The columnDefs and order options are optional. I leave the doc links in the JS code above. So you can read and use them if you want.
Create the get_news_by_filters method in the controller
As I mention above in JS code, we need to create the get_news_by_filters method in our controller. In my case, I will create in news controller.
This method will call the model method in order to fetch the data we need. The code is below.
/**
* get_news_by_filters
*/
public function get_news_by_filters()
{
$news = $this->news_model->get_news_by_ajax();
// var_dump($news);
// exit;
// # Return our data back to ajax with Json format (json_encode)
// you must use "echo" for returning the result you want back to Ajax call
echo json_encode($news);
}Make sure you use echo instead of return. And use json_encode() for the query result array which will get from the get_news_by_ajax method. Again, we don’t build get_news_by_ajax method yet.
Create get_news_by_ajax method in the model
In my news model, I fetch the data from my news table as below.
public function get_news_by_ajax()
{
// https://codeigniter.com/userguide3/database/query_builder.html#looking-for-similar-data
$this->db->like('title', $this->input->post('filter_news_title'));
$query = $this->db->get('news');
// check the number of rows in the result set
if ($query->num_rows() > 0) {
// return the query result as array
return $query->result_array();
} else {
// return the empty array if no row
return array();
}
}Make sure, you return the result data as $query->result_array().
For the query, you can change to any query you like.
Below is the result when users filter the data as “News#”.
And that’s it for today. Hope it is useful.
Tips for WordPress and Datatable
If you want to know how to filter the data in Datatable in WordPress, please check it out here.
