Help Full PHP + JavaScript: How load PHP & Mysql data using Ajax in DataTables Server-side Processing ?

// HTML Code
<div class="alphabet">Search:
All

A


B


C


D


E


F


G


H


I


J


K


L


M


N


O


P


Q


R


S


T


U


V


W


X


Y


Z

</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>

Help Full PHP + JavaScript

Home

Tuesday, September 26, 2017

How load PHP & Mysql data using Ajax in DataTables Server-side Processing ?

No comments:

Post a Comment

Miscellaneous