// HTML Code
<div class="alphabet">Search:
<span class="clear active alphasearch open" id="all">All</span>
<span>
<span class="alphasearch" id="A">A</span>
</span>
<span>
<span class="alphasearch" id="B">B</span>
</span>
<span>
<span class="alphasearch" id="C">C</span>
</span>
<span>
<span class="alphasearch" id="D">D</span>
</span>
<span>
<span class="alphasearch" id="E">E</span>
</span>
<span>
<span class="alphasearch" id="F">F</span>
</span>
<span>
<span class="alphasearch" id="G">G</span>
</span>
<span>
<span class="alphasearch" id="H">H</span>
</span>
<span>
<span class="alphasearch" id="I">I</span>
</span>
<span>
<span class="alphasearch" id="J">J</span>
</span>
<span>
<span class="alphasearch" id="K">K</span>
</span>
<span>
<span class="alphasearch" id="L">L</span>
</span>
<span>
<span class="alphasearch" id="M">M</span>
</span>
<span>
<span class="alphasearch" id="N">N</span>
</span>
<span>
<span class="alphasearch" id="O">O</span>
</span>
<span>
<span class="alphasearch" id="P">P</span>
</span>
<span>
<span class="alphasearch" id="Q">Q</span>
</span>
<span>
<span class="alphasearch" id="R">R</span>
</span>
<span>
<span class="alphasearch" id="S">S</span>
</span>
<span>
<span class="alphasearch" id="T">T</span>
</span>
<span>
<span class="alphasearch" id="U">U</span>
</span>
<span>
<span class="alphasearch" id="V">V</span>
</span>
<span>
<span class="alphasearch" id="W">W</span>
</span>
<span>
<span class="alphasearch" id="X">X</span>
</span>
<span>
<span class="alphasearch" id="Y">Y</span>
</span>
<span>
<span class="alphasearch" id="Z">Z</span>
</span>
</div>
<div class="portlet-body">
<table class="table table-striped table-bordered table-hover table-checkable order-column" id="enquirylist">
<thead>
<tr>
<th>Quote Received</th>
<th>Service Date</th>
<th>First Name</th>
<th>Last Name</th>
<th>Phone</th>
<th>State</th>
<th>Move Type</th>
</tr>
</thead>
</table>
</div>
<?php
// This code add in controller file
public function ajaxData() {
// Load here model
$this->load->model('enquiry_model');
if (isset($_POST)) {
$this->enquiry_model->getAjaxData();
}
}
?>
<?php
// This code add in model file
/* Get Ajax Data */
function getAjaxData() {
/* IF Query comes from DataTables do the following */
if (!empty($_POST)) {
/* echo "<pre>";
print_r($_POST); */
define("enquiry", "enquiry");
define("move_type", "move_type");
/* Useful $_POST Variables coming from the plugin */
$draw = $_POST["draw"]; //counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
$orderByColumnIndex = $_POST['order'][0]['column']; // index of the sorting column (0 index based - i.e. 0 is the first record)
$orderBy = $_POST['columns'][$orderByColumnIndex]['data']; //Get name of the sorting column from its index
/* if($orderBy == 'edit_link')
{
$orderBy = 'enquiry_id';
} */
$orderType = $_POST['order'][0]['dir']; // ASC or DESC
$start = $_POST["start"]; //Paging first record indicator.
$length = $_POST['length']; //Number of records that the table can display in the current draw
/* END of POST variables */
$sql = "SELECT * FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id";
$query = $this->db->query($sql);
$recordsTotal = $query->num_rows();
/* SEARCH CASE : Using Alphabest Wise */
/* */
/* SEARCH CASE : Filtered data */
if (!empty($_POST['search']['value'])) {
if (!empty($_POST['alphabet']) && $_POST['alphabet'] != 'all') {
/* WHERE Clause for searching */
for ($i = 0; $i < count($_POST['columns']); $i++) {
/*if($_POST['columns'][$i]['data'] !="en_fname")
{*/
$column = $_POST['columns'][$i]['data']; //we get the name of each column using its index from POST request
$where[] = "$column like '%" . $_POST['search']['value'] . "%'";
/* }*/
}
$where = "WHERE ( " . implode(" OR ", $where). " )"; // id like '%searchValue%' or name like '%searchValue%' ....
$where .= " AND ( en_fname like '" . $_POST['alphabet'] . "%' )";
/* End WHERE */
}else
{
/* WHERE Clause for searching */
for ($i = 0; $i < count($_POST['columns']); $i++) {
/* if($_POST['columns'][$i]['data'] !="edit_link")
{ */
$column = $_POST['columns'][$i]['data']; //we get the name of each column using its index from POST request
$where[] = "$column like '%" . $_POST['search']['value'] . "%'";
/* } */
}
$where = "WHERE " . implode(" OR ", $where); // id like '%searchValue%' or name like '%searchValue%' ....
/* End WHERE */
}
// echo $where;die;
$sql = sprintf("SELECT *,DATE_FORMAT(e.en_date, '%%d/%%m/%%Y %%h:%%s %%p') as en_date,DATE_FORMAT(e.en_servicedate, '%%d/%%m/%%Y') as en_servicedate FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id %s", $where); //Search query without limit clause (No pagination)
$query = $this->db->query($sql);
$recordsFiltered = $query->num_rows(); //count(getData($sql));//Count of search result
/* SQL Query for search with limit and orderBy clauses */
$sql = sprintf("SELECT *,DATE_FORMAT(e.en_date, '%%d/%%m/%%Y %%h:%%s %%p') as en_date,DATE_FORMAT(e.en_servicedate, '%%d/%%m/%%Y') as en_servicedate FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id %s ORDER BY %s %s limit %d , %d ", $where, $orderBy, $orderType, $start, $length);
$query = $this->db->query($sql);
$query = $query->result_array();
$data = array();
foreach ($query as $row) {
$row['en_date'] = '<a href="/enquiriesdetails/' . $row['enquiry_id'] . '">' . $row['en_date'] . '</a>';
$data[] = $row;
}
} else if (!empty($_POST['alphabet']) && $_POST['alphabet'] != 'all') {
$where[] = "en_fname like '" . $_POST['alphabet'] . "%'";
$where = "WHERE " . implode(" OR ", $where); // id like '%searchValue%' or name like '%searchValue%' ....
//$sql = sprintf("SELECT *,CONCAT_WS('', 'en_fname', 'en_lname', NULL) = 'en_fname' FROM %s %s", enquiry , $where);//Search query without limit clause (No pagination)
$sql = sprintf("SELECT *,DATE_FORMAT(e.en_date, '%%d/%%m/%%Y %%h:%%s %%p') as en_date,DATE_FORMAT(e.en_servicedate, '%%d/%%m/%%Y') as en_servicedate FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id %s", $where); //Search query without limit clause (No pagination)
$query = $this->db->query($sql);
$recordsFiltered = $query->num_rows(); //count(getData($sql));//Count of search result
/* SQL Query for search with limit and orderBy clauses */
$sql = sprintf("SELECT *,DATE_FORMAT(e.en_date, '%%d/%%m/%%Y %%h:%%s %%p') as en_date,DATE_FORMAT(e.en_servicedate, '%%d/%%m/%%Y') as en_servicedate FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id %s ORDER BY %s %s limit %d , %d ", $where, $orderBy, $orderType, $start, $length);
$query = $this->db->query($sql);
$query = $query->result_array();
$data = array();
foreach ($query as $row) {
$row['en_date'] = '<a href="' . base_url('/enquiries/view/' . $row['en_unique_id']) . '">' . $row['en_date'] . '</a>';
$data[] = $row;
}
}
/* END SEARCH */ else {
$sql = sprintf("SELECT *,DATE_FORMAT(e.en_date, '%%d/%%m/%%Y %%h:%%s %%p') as en_date,DATE_FORMAT(e.en_servicedate, '%%d/%%m/%%Y') as en_servicedate FROM " . enquiry . " as e INNER JOIN " . move_type . " as m ON e.en_movetype=m.movetype_id ORDER BY %s %s limit %d , %d ", $orderBy, $orderType, $start, $length);
$query = $this->db->query($sql);
$query = $query->result_array();
$data = array();
foreach ($query as $row) {
$row['en_date'] = '<a href="' . base_url('/enquiries/view/' . $row['en_unique_id']) . '">' . $row['en_date'] . '</a>';
$data[] = $row;
}
$recordsFiltered = $recordsTotal;
}
// echo $sql;die;
/* Response to client before JSON encoding */
$response = array(
"draw" => intval($draw),
"recordsTotal" => $recordsTotal,
"recordsFiltered" => $recordsFiltered,
"data" => $data
);
echo json_encode($response);
} else {
echo "NO POST Query from DataTable";
}
}
?>
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script type="text/javascript" src="global/scripts/datatable.js"></script>
<script type="text/javascript" src="global/plugins/datatables/datatables.min.js"></script>
<script>
/////// script code ///////
$(document).ready(function () {
var myData ={};
var table = $('#enquirylist').DataTable({
"columns": [
{"data": "en_date"},
{"data": "en_servicedate"},
{"data": "en_fname"},
{"data": "en_lname"},
{"data": "en_phone"},
{"data": "en_movingfrom_state"},
{"data": "movetype_name"},
],
"columnDefs": [
/*{
"targets": [6],
"orderable": false,
},*/
{ "width": "15%", "targets": 0 },
],
"processing": true,
//"autoWidth": false,
"serverSide": true,
"bStateSave": true,
"stateSave": true,
"ajax": {
url: 'enquirieslist/ajaxData',
"data": function ( d ) {
return $.extend(d, myData);
},
type: 'POST'
}
});
$( ".alphasearch" ).click(function() {
$(".alphasearch").removeClass("open");
$(this).toggleClass('open');
var alpha = $(".open").attr("id");
myData.alphabet = alpha;
table.ajax.reload();
});
});
</script>
