Home

Tuesday, September 26, 2017

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

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

No comments:

Post a Comment

rathoddhirendra.blogspot.com-Google pagerank and Worth