How to show only filtered record count (number of returned data from search, num_rows() ) in codeigniter, jTable.
As now is showing all the row number from database no matter how many search result i have. I dont know how to solve this. All i want is to show only the filtered data record count number. Can anyone help me?
Here is my view.php:
<div class="JtableCrud">
<div class="filtering">
<form class="searchbox_1">
Country Name: <input type="text" class="search_1" name="country_name" id="country_name" />
City Name: <input type="text" class="search_1" name="capital_city" id="capital_city" />
Date from <input class="search_1" type="date" name="from_date" id="from_date"/>
Date to <input class="search_1" type="date" value = "<?php echo date('Y-m-d')?>" name="to_date" id="to_date"/>
<center><button class="submit_1" type="submit" id="LoadRecordsButton">Search</button>
<input class ="submit_1" type="reset" value="Clear fields!"></center>
</form>
</div>
<div id="countryTable"></div>
<script type="text/javascript">
$(document).ready(function () {
//Prepare jTable
$('#countryTable').jtable({
title: 'Country\'s',
paging: true,
pageSize: 10,
sorting: true,
defaultSorting: 'country_name ASC',
selecting: true,
multiselect: true,
selectingCheckboxes: true,
selectOnRowClick: true,
actions: {
listAction: 'get_country',
createAction: 'create_country',
updateAction: 'update_country',
deleteAction: 'delete_country'
},
fields: {
country_id: {
key: true,
list: false
},
country_name: {
title: 'Country Name'
},
country_code: {
title: 'Country Code'
},
surface_area: {
title: 'Surface Area (m<sup>2</sup>)',
width: '13%'
},
continent_name: {
title: 'Continent Name'
},
continent: {
title: 'Continent Code'
},
population: {
title: 'Population'
},
capital_city: {
title: 'Capital City'
},
record_date: {
title: 'Record Date',
type: 'date',
displayFormat: 'mm/dd/yy',
create: false,
edit: false,
sorting: false
}
},
formCreated: function (event, data) {
data.form.find('input[name="country_name"]').addClass('validate[required]');
data.form.find('input[name="country_code"]').addClass('validate[required], custom[onlyLetterSp][maxSize[2]]]');
data.form.find('input[name="surface_area"]').addClass('validate[required,custom[number]]');
data.form.find('input[name="continent_name"]').addClass('validate[required]');
data.form.find('input[name="continent"]').addClass('validate[required], custom[onlyLetterSp][maxSize[2]]');
data.form.find('input[name="population"]').addClass('validate[required], custom[number]]');
data.form.find('input[name="capital_city"]').addClass('validate[required]');
data.form.validationEngine();
},
formSubmitting: function (event, data) {
return data.form.validationEngine('validate');
},
formClosed: function (event, data) {
data.form.validationEngine('hide');
data.form.validationEngine('detach');
}
});
$('#country_name').autocomplete({
source: 'list_country',
minLength: 0,
scroll: true,
autoFocus: true
}).focus(function() {
$(this).autocomplete("search", "")
.autocomplete( "widget" )
.addClass( "country_field" );
});
$('#capital_city').autocomplete({
source: 'list_city',
minLength: 0,
scroll: true,
autoFocus: true
}).focus(function() {
$(this).autocomplete("search", "")
.autocomplete( "widget" )
.addClass( "country_field" );
});
$('#LoadRecordsButton').click(function (e) {
e.preventDefault();
$('#countryTable').jtable('load', {
country_name: $('#country_name').val(),
capital_city: $('#capital_city').val(),
from_date: $('#from_date').val(),
to_date: $('#to_date').val()
});
});
$('#LoadRecordsButton').click();
});
</script>
</div>
Here is my model.php:
<?php
class Country_model extends CI_Model {
public function get_country($country_name,$from_date,$to_date,$capital_city) {
$result = $this->db->query("SELECT SQL_CALC_FOUND_ROWS country_id, country_code, country_name, surface_area,
continent_name, continent, population, capital_city, record_date
FROM country
WHERE (deleted = 0)
AND (country_name LIKE '%" .$this->db->escape_like_str($country_name) . "%' ) "
. "AND (capital_city LIKE '%". $this->db->escape_like_str($capital_city) . "%' ) "
. "AND (record_date BETWEEN'" . $from_date . "' AND date_add('" . $to_date . "', INTERVAL 1 DAY)) "
. "ORDER BY " . filter_input(INPUT_GET, "jtSorting") . "
LIMIT " . filter_input(INPUT_GET, "jtStartIndex") . "," . filter_input(INPUT_GET, "jtPageSize") . ";");
$rows = array();
foreach ($result->result() as $row) {
$rows[] = $row;
}
return $rows;
}
Here is my controller.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class country_tas extends CI_Controller {
public function get_country() {
if (empty($this->input->post('to_date'))){
$to_date = date('Y-m-d');
}else {
$to_date = $this->input->post('to_date');
}
$country_name = $this->input->post('country_name');
$capital_city = $this->input->post('capital_city');
$from_date = $this->input->post('from_date');
$this->load->model('Country_model');
$rows = $this->Country_model->get_country($country_name,$from_date,$to_date,$capital_city);
$num_rows = $this->db->count_all_results('country');
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['TotalRecordCount'] = $num_rows;
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);
}
Okay I solved it!
To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward.
I added to my SELECT query "SQL_CALC_FOUND_ROWS" and then i created this function:
public function record_count(){
$num_rows = $this->db->query('SELECT FOUND_ROWS() count;')->row()->count;
return $num_rows;
}
and i called it on my controller:
$jTableResult['TotalRecordCount'] = $this->Country_model->record_count();
That solved my problem. If you have a better solution please post it.