Multiple records delete datatables ajax

If you want to implement add bulk action like Export CSV , Removing all , single records , Export seleted records to excel and adding filters like days filter to data tables listings using ajax approch 1. You need to include required jQuery for datatables , ajax processing.

<script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
<script src="<?php echo base_url();?>assets/js/datatables/responsive/js/datatables.responsive.js">
</script>
<script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
</script>

2. Your html source file or you can say your view file will goes like below.

<h3 class="page-title">Accounts</h3>
<div class="panel panel-primary" data-collapsed="0">
<div class="col-sm-12">
<div class="panel-body">
<form id="Gexcel" name="Gexcel" method="POST" action="<?php echo base_url(); ?
>accounts/create_document">
<input type="hidden" name="aidds" id="aidds" ><input type="hidden" name="atype" id="atype" value="da" >
</form>
<table id="demo_account_listing" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th class="no-sort"></th>
<th class="trigger_reload">Sr No</th>
<th>ID</th>
<th>Date</th>
<th class="no-sort">Days Left</th>
<th>Name</th>
<th>Email Address</th>
<th>Country</th>
<th>Phone</th>
<th>Mobile</th>
<th>Photo</th>
<th>Source</th>
<th class="no-sort"> Action</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
<script type="text/javascript">
jQuery(window).load(function () {
var $ = jQuery;
var oAllLinksTable = $("#demo_account_listing").dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": baseurl + 'accounts/get_account_listing',
"sPaginationType": "bootstrap",
"oLanguage": {
"sEmptyTable": "No account found.",
"sProcessing": "<img src='<?php echo base_url(); ?>/assets/global/img/loading-spinner-blue.gif'>"
},
"columnDefs": [{
"targets": 'no-sort',
"orderable": false
}],
"aoColumnDefs": [{"bVisible": true, "aTargets": [0]}],
"order": [[0, "desc"]],
processing: true,
"fnServerParams": function (aoData) {
var last_days = $('input[name=days_filter]:checked').val();
aoData.push({name: "days_filter", value: last_days});
},
"fnRowCallback": function (nRow, aData, iDisplayIndex) {
var oSettings = oAllLinksTable.fnSettings();
$("td:nth-child(2)", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
$(nRow).attr("id", 'tr_' + aData[0]);
$("td:nth-child(1)", nRow).html("
<input type='checkbox' name='contacts[]' class='mail-checkbox check_' value='" + aData[0] + "'>");
return nRow;
}
});
var action_radio = '<div class="pull-right check_filter" id="filter_group_radio"><span>
<input type="radio" name="days_filter" id="dsv" class="form-control days_filter" value="7"> 
Last7 Days</span>&nbsp;<span>
<input type="radio" id="df" name="days_filter" class="form-control days_filter" value="14">
Last 14 Days &nbsp;</span><span>
<input type="radio" id="dthi" name="days_filter" class="form-control days_filter" value="30">
Last 30 Days&nbsp;</span><span>
<input type="radio" id="dsx" name="days_filter" class="form-control days_filter" value="60">
Last 60 Days</span></div>';
var extra_action = action_radio + '<div class="pull-right">
<a href="<?php echo base_url(); ?>accounts/new_account">
<button class="btn purple-plum pull-right" type="button">Open New Demo Account</button>
</a>&nbsp;</div>';
$custome_search_filter = extra_action + ' <span class="btn blue" style="height:34px;">
<input type="checkbox" class="checkallbox" id="select_all_"></span><div class="pull-right">
<div class="btn-group myremove"><button data-toggle="dropdown" 
class="btn blue dropdown-toggle dropup" aria-expanded="false">
Bulk Action<i class="fa fa-angle-down"></i></button><ul role="menu" 
class="dropdown-menu pull-right">
<li role="presentation"><a data-toggle="modal" class="deleteRecord" 
href="" tabindex="-1" role="menuitem">
Delete</a></li><li role="presentation">
<a data-toggle="modal" class="exportExcel" href="" tabindex="-1"
role="menuitem">Export to excel</a></li></ul></div></div>';
$($custome_search_filter).appendTo("#demo_account_listing_length > label");
$("#demo_account_listing_length").parent().removeClass('col-md-6').addClass('col-md-10');
$("#demo_account_listing_filter").parent().removeClass('col-md-6').removeClass('col-sm-12');
$(function () {
$(document).on('change', '.days_filter', function (event) {
event.preventDefault();
oAllLinksTable.fnFilter(this.value, 12);
$(this).find('#filter_group_radio input:radio').prop('checked', true);
});
});
$(document).on('click', '.checkallbox', function () {
if ($('#select_all_').is(':checked')) {
$(".check_").prop("checked", "checked");
} else {
$(".check_").removeAttr('checked');
}
});
$(document).on('click', '.check_', function () {
var $this = $(this);
// $this will contain a reference to the checkbox
var numberOfChecked = $('.check_:checked').length;
var totalCheckboxes = $('.check_').length;
var numberNotChecked = totalCheckboxes - numberOfChecked;
if (numberOfChecked == totalCheckboxes) {
$('#select_all_').prop('checked', 'checked');
}
else {
$('#select_all_').removeAttr('checked');
}
});
$(document).on('click', '.deleteRecord', function (e) {
e.preventDefault();
var allVals = [];
$('.check_').each(function () {
if ($(this).is(':checked')) {
allVals.push($(this).val());
}
});
if ($('.check_').is(':checked')) {
bootbox.confirm("Are you sure you want to delete?", function (result) {
if (result == true) {
$.ajax({
url: baseurl + 'accounts/removeAccounts?time=' + timestamp,
type: 'post',
data: 'atype=la&rids=' + allVals,
dataType: 'json',
success: function (response) {
$("#showmessage_jscbf").html("");
switch (response.Mstatus) {
case 'success':
$.each(response.idds.split(','), function () {
$("#tr_" + this).hide();
});
$(".trigger_reload").trigger('click');
break;
case 'error':
$("#showmessage_jscbf").html(response.msg);
break;
}
}
});
}
});
return false;
} else {
alert("Please select one record to delete.")
}
});
$(document).on('click', '.exportExcel', function (e) {
e.preventDefault();
var allVals = [];
$('.check_').each(function () {
if ($(this).is(':checked')) {
allVals.push($(this).val());
}
});
if ($('.check_').is(':checked')) {
$("#aidds").val(allVals);
document.Gexcel.submit();
} else {
alert("Please select one records to export.")
}
});
});
$(function () {
$(document).on('click', '.removeRcords', function (event) {
event.preventDefault();
var idss = $(this).attr('id');
var rid = idss.split("_")[1];
do_remove_ajax('account_type_remove', rid);
});
});
</script>

Notes:

I. Gexcel is a form where all selected ids will be store in hidden id aidds textbox.

II. demo_account_listing_length , you can just open your firebug and can get your id from there.you can see in above html that my id is demo_account_listing so datatable attaching his event to this id.

III. action_radio , $custome_search_filter , extra_action is the filter html , we have to inject to datatables.

IV. fnRowCallback function is a datatable function , i have to put s.no there also $("td:nth-child(1)", nRow) is checkbox which will inject with primary key of your table in mysql.

V. bootbox.confirm is alert box , you can replace simple confim alert box of javascript if you don't need.

VI. $.each(response.idds.split(','), function () { $("#tr_" + this).hide();}); from ajax response comma seperated ids will be revert back from your controller.

3. Your controller function will go like below

public function get_account_listing() {
$this->load->model('common');
$aColumns = array('id', 'id', 'account_no', 'regdate', 'daysleft', 'name', 'email', 
'country', 'mobile', 'leverage', 'balance', 'lead_source');
$aColumnsWhere = array('a.id', 'a.account_no', 'a.date_time', 'a.email', 'a.leverage', 'a.balance',
'da.firstname', 'da.lastname', 'da.regdate', 'da.mobile', 'c.country_name', 'da.lead_source');
$sIndexColumn = "id";
$sTable = "account";
//custome filter
$days_filter = (int) $this->input->get('days_filter', TRUE);
$iDisplayStart = $this->input->get('iDisplayStart', true);
$iDisplayLength = $this->input->get('iDisplayLength', true);
$iSortCol_0 = $this->input->get('iSortCol_0', true);
$iSortingCols = $this->input->get('iSortingCols', true);
$sLimit = "";
if (isset($iDisplayStart) && $iDisplayLength != '-1') {
$sLimit = "LIMIT " . $iDisplayStart . ", " .
$iDisplayLength;
}
if (isset($iSortCol_0)) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($iSortingCols); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
" . trim($_GET['sSortDir_' . $i]) . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
$sSearch = $this->input->get('sSearch', true);
$sWhere = "";
if ($sSearch != "") {
$sWhere = "AND (";
for ($i = 0; $i < count($aColumnsWhere); $i++) {
$sWhere .= $aColumnsWhere[$i] . " LIKE '%" . $sSearch . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
if ($days_filter != '') {
$sWhere .= "AND (";
$sWhere .= '' . $aColumnsWhere[2] . ' BETWEEN DATE_SUB(NOW(),INTERVAL ' . $days_filter . ' DAY) AND 
NOW()';
//$sWhere .= 'DATE('.$aColumnsWhere[12].')' . " BETWEEN 
'" . DATE('Y-m-d',strtotime($start_date)) . "' AND '".DATE('Y-m-d',strtotime($end_date))."'";
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = " WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . trim($_GET['sSearch_' . $i]) . "%'";
}
}
$sEcho = $this->input->get('sEcho');
$result = $this->common->demo_account_listing($sTable, $sWhere, $sOrder, $sLimit, 
$aColumns, $sIndexColumn, $sEcho);
echo json_encode($result);
}

4. Your model code goes like below

function demo_account_listing($sTable, $sWhere, $sOrder,$sLimit, $aColumns, $sIndexColumn, $sEcho){
$sJoin_q_fields = "SELECT SQL_CALC_FOUND_ROWS a.id,a.account_no,a.date_time,
a.email,a.leverage,a.balance, da.regdate, da.firstname as name,
da.mobile, c.country_name as country, da.lead_source";
$sJoin_q = " FROM (account a)";
$sJoin_q .= ' INNER JOIN demo_register da ON a.email = da.email';
$sJoin_q .= ' INNER JOIN country c ON da.country = c.id';
$sJoin_q .= ' WHERE a.act_type = "ABC" ';
$qJoin = $sJoin_q_fields . $sJoin_q . ' ' . $sWhere . ' ' . $sOrder . ' ' . $sLimit;
$rResult = $this->db->query($qJoin);
$rResult_array = $rResult->result_array();
$iFilteredTotal = count($rResult_array);
/* Total data set length */
$sJoin_q_count = "SELECT COUNT(" . $sIndexColumn . ") AS TotalRecords";
$sQuery_TR = $sJoin_q_count . $sJoin_q.$sWhere;
$rResult_TR = $this->db->query($sQuery_TR);
$rResult_array_TR = $rResult_TR->result_array();
$iTotal = $rResult_array_TR[0]['TotalRecords'];
$output = array(
"sEcho" => intval($sEcho),
"iTotalRecords" => intval($iTotal),
"iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
"aaData" => array()
);
$i = 0;
$last = intval($iTotal);
$seclast = intval($iTotal) - 1;
foreach ($rResult_array as $aRow) {
$row = array();
foreach ($aColumns as $col) {
if ($col == 'daysleft') {
$rem = $this->ref_time - strtotime($aRow['regdate']);
$row[] = 30 - floor($rem / 86400);
} else if ($aRow[$col] == 'D') {
$row[] = '<a href="javascript:void(0)" id="E_' . $aRow['id'] . '" name=""
class="btn btn-success btn-sm "><i class="entypo-check"></i> Enable</a>';
} else if ($aRow[$col] == 'E') {
$row[] = '<a href="javascript:void(0)" id="D_' . $aRow['id'] . '"
name="broker_account_status_request_ed" class="btn btn-danger btn-sm ">
<i class="entypo-minus"></i> Disable</a>';
} else {
$row[] = $aRow[$col];
}
}
$i++;
if ($i == $last || $i == $seclast) {
$classaction = 'dropup';
} else {
$classaction = '';
}
array_push($row, '<div class="btn-group ' . $classaction . ' ">
<button data-toggle="dropdown" class="btn red dropdown-toggle" aria-expanded="false">Action
<i class="fa fa-angle-down"></i>
</button>
<ul role="menu" class="dropdown-menu pull-right">
<li role="presentation">
<a href="' . base_url() . 'accounts/change_mt4_pw/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Change Password
</a>
</li>
<li role="presentation">
<a href="' . base_url() . 'accounts/delete_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Delete Account
</a>
</li>
<li role="presentation">
<a href="' . base_url() . 'accounts/link_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Link Account
</a>
</li>
</ul>
</div>');
$output['aaData'][] = $row;
}
return $output;
}

5. Your view load function will goes like below

public function index() {
$data['meta_title'] = 'Accounts';
$data['meta_keywords'] = 'Accounts';
$data['meta_desc'] = 'Accounts';
$data['top_mobile_menu'] = 'admin/dashboard/top_mobile_menu';
$data['top_menu'] = 'admin/dashboard/top_menu';
$data['main'] = 'admin/dashboard/accounts/account_listing';
$data['footer'] = 'admin/dashboard/footer';
$this->load->vars($data);
$this->load->view($this->admin_dashboard);
}

6. Your function for exporting i am using dompdf library for codeigniter. you can get it after googling.

public function create_document() {
$this->load->helper('dompdf');
$this->load->helper('file');
$this->load->model('prints');
$account_ids = $this->input->post('aidds');
$account_type = $this->input->post('atype');
$result = $this->prints->print_account_listing(format_in_clause($account_ids));
if (!empty($result)) {
$this->load->library('excel');
$this->excel->to_excel($result, "Aaccount-Listing");
exit;
} else {
$this->session->set_flashdata('msg',
alertmessage($message_type = 'alert-danger', $message = 'Unable to process your requiest.'));
redirect(base_url() . 'accounts');
}
}

7. Your function for removing records.

public function removeAccounts() {
$this->load->model('common');
$id = trim($this->input->post('rids'));
$account_type = trim($this->input->post('atype'));
if (isset($id)) {
$thread_ids = explode(",", $id);
foreach ($thread_ids as $values) {
$this->common->do_remove_records('account', array('id' => $values, 'act_type' => 'DEMO'));
}
echo json_encode(array('Mstatus' => 'success', 'idds' => $id,
'msg' => alertmessage($message_type = 'alert-success', $message = 'Accounts has been removed.')));
} else {
echo json_encode(array('Mstatus' => 'error',
'msg' => alertmessage($message_type = 'alert-danger', $message = 'Error in process. Try later.')));
}
}
8. alertmessage is a helper function for generating bootstrap message.
function alertmessage($message_type, $message) {
$output = '';
if (!empty($message_type) && !empty($message)) {
$output.="<div class='alert showmessage_jscbf " . $message_type . "'>" . $message . "</div>";
return $output;
} else {
return $output;
}
}

9. format_in_clause is a helper function.

function format_in_clause($string) {
if (isset($string)) {
$realArray = explode(',', $string);
if (is_array($realArray)) {
$stringForIn = "'" . implode("','", $realArray) . "'";
return $stringForIn;
} else {
return $string;
}
} else {
return NULL;
}
}