jQuery + PHP Datatables Server Side Processing with Column Filtering

Question

I have implemented SSP with jQuery Datatables and global filter but after a review I wanted to implement specific column filtering but I am having issues. This is the code I have so far:

Javascript:

$(document).ready(function() {
   initAdultTable();
});

function initAdultTable () {
  return tablessp = $('#members').DataTable({
    url: '<?php echo site_url("members"); ?>',
    processing: true,
    serverSide: true,
    paging: true,
    searching: true,
    pageLength: 25,
    lengthMenu: [[10, 25, 50, 100, 500, 1000,5000], [10, 25, 50, 100, 500, 1000, "All"]],
    deferRender: true,
    dom: 'flBrtip',
    retrieve: true,
    ordering: true,
    order: [[0, "desc"]],
    language: {
        "lengthMenu": "Display _MENU_ records per page",
        "zeroRecords": "Nothing found - sorry",
        "info": "Showing _MAX_ entries",
        "infoEmpty": "No records available",
        "infoFiltered": "( from _TOTAL_ total records)"
    },
    scrollx: true, 
    sScrollX: "100%",

    columns: [{data: "id"}, {data: "first_name"}, {data: "middle_name"},{data: "last_name"} , {data: "phone_number"} , {data: "company_location"} ,{data: "parent"} ,{data: "agency_location"} , {data: "Age"} , {data: "gender"} , {data: "education_level"} , {data: "marital_status"} , {data: "office_pos"} , {data: "email"}, {data: "membership_status"}, {data: "active_status"}, {data: "street_address"}, {data: "postcode"}, {data: "action"} ]
});
}

function searchAdultData(){

    var tablessp = initAdultTable();

    office_position_id = $('#office_position_id').val();
    membership_status_id = $('#membership_status_id').val();
    agency_id = $('#agency_id').val();
    company_id = $('#company_id').val();

    tablessp
    .columns(14).search(office_position_id)
    .columns(16).search(membership_status_id)
    .columns(7).search(agency_id)
    .columns(5).search(company_id)
    .draw(); 

}

function resetAdultData(){
    var tablessp = initAdultTable();
    $('#membersForm')[0].reset();

    tablessp.search('');
    tablessp.draw();
}

HTML:

<div class="col-sm-12"> 

                <div class="row">
                    <div class="col-lg-12">
                        <button type="button" class="btn btn-primary btn-brand--icon" id="kt_search" onClick="searchAdultData();" style="width:100%;">
                            <span>
                                <i class="la la-search"></i>
                                <span>Search</span>
                            </span>
                        </button>

                        <button type="button" class="btn btn-warning btn-warning--icon" id="kt_reset" onClick="resetAdultData();" style="width:100%;">
                            <span>
                                <i class="la la-close"></i>
                                <span>Reset</span>
                            </span>
                        </button>
                    </div>
                </div>

</div>

After trying many implementations this is the one that got me closest to what I wanted to do. Initially it was giving me an error that tablessp was not defined, so I put the initializing of the table into a function and returned its value. On load the datatable renders as expected but when I search It gives me this error:

Cannot set property 'data' of null

PHP:

<?php 
       if ($this->input->is_ajax_request()) {
            /** this will handle datatable js ajax call * */
            /** get all datatable parameters * */

            $search = $this->input->get('search') ;/** search value for datatable  * */
            $offset = $this->input->get('start') ;/** offset value * */
            $limit = $this->input->get('length') ;/** limits for datatable (show entries) * */
            $order = $this->input->get('order') ;/** order by (column sorted ) * */
            $column = array('members.id', 'first_name', 'middle_name','last_name','phone_number','company_location','parent','agency_location','Age','gender','education_level','marital_status','office_pos','email', 'membership_status','active_status','street_address','postcode', 'action');/**  set your data base column name here for sorting* */

            //$column = array('id');/**  set your data base column name here for sorting* */
            $type = '';
            $orderColumn = isset($order[0]['column']) ? $column[$order[0]['column']] : 'members.id';
            $orderDirection = isset($order[0]['dir']) ? $order[0]['dir'] : 'desc';
            $ordrBy = $orderColumn . " " . $orderDirection;

            if (!empty($search['value'])) {
                $type="search";

                // RESULT SET WHEN QUERYING
                $sql = "SELECT members.`id`,`first_name`, `middle_name`,`last_name`, picture `Photo`, TIMESTAMPDIFF(YEAR, date_birth, CURDATE()) as `Age`, gender, marital_status, members.phone_number, members.email, setting_company.name as `company_name`, setting_office_position.title `office_pos`, setting_company_group._description `company_location`, setting_agency_parent.description `parent`, setting_agency_group.description `agency_location`, street_address , postcode, setting_occupation.title `job`, setting_membership_status.title `membership_status` , active_status, education_level FROM members
                ** VARIOUS_JOINS_GOES HERE **
                WHERE members.company_id = '$company_id' AND (members.membership_status != 10) AND members.age_group = 'Adults'
                AND (CONCAT(members.first_name, ' ' , members.middle_name, ' ', members.last_name  ) LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_company_group.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_office_position.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_membership_status.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_agency_group.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')

                order by " . $ordrBy . " limit $offset,$limit";

                // QUERY FOR COUNT OF ROWS
                $sql2 = "SELECT count(*) as `filtered` FROM members
                ** VARIOUS_JOINS_GOES HERE **
                WHERE members.company_id = '$company_id' AND (members.membership_status != 10) AND members.age_group = 'Adults'
                AND (CONCAT(members.first_name, ' ' , members.middle_name, ' ', members.last_name  ) LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_company_group.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_office_position.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_membership_status.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')
                OR (setting_agency_group.id LIKE '%" . $search['value'] . "%' AND members.company_id = '$company_id')

                order by " . $ordrBy;

                $result = $this->db->query($sql);
                $result2 = $this->db->query($sql2)->row();
                $count = $result2->filtered;
            } else {
                DEFAULT DATA WITHOUT FILTER HERE 
            }

I removed some of the columns and parts of the queries to make it easier and more practical for someone to review.

Not sure what I am doing wrong here, I can see that the data i'm filtering is being added to the request under column[x][search][value] = 'search value', I can assume I am doing something wrong on the PHP side?

Side Note: Before anyone points out I know some security work needs to be done here.

Have you checked the network tab of browser debug tools? Please check the response of the server when you search. is the data attribute filled with an array of objects with needed attributes? — finder2
– finder2, Commented Nov 1, 2019 at 9:25
When the request goes to the server, the correct columns are populated in the json as I said above. But when the response is returned it just returns the full dataset without filtering. Some other implementations I have done just returned an error in console saying null data — Enoch
– Enoch, Commented Nov 1, 2019 at 9:52
I assume that all request fields, primary $search['value'], are filled. Please check that $sql is what you expect and that the correct branch of the if is executed. Is public demo of the code (with sample data) possible? — finder2
– finder2, Commented Nov 1, 2019 at 10:33

Nawed Khan · Accepted Answer · 2019-11-06 20:24:24Z

0

In your function: initAdultTable() you are not returning the Datatable API instead you are always returning true. return a = whatever; will not return value of a, it will return boolean: true because it successfully assigned the value to a.

It works the first time because you called initAdultTable() on document ready. It gets the data and fills the table.

Second time you are expecting it to return the dataset that you wish to sort/filter but it just fills all the data just like the first time and returns true. You are trying to sort a boolean, hence the error.

Change the function initAdultTable() like this...

function initAdultTable() {
  return $('#members').DataTable({
     //......
  });
}

answered Nov 6, 2019 at 20:24

Nawed Khan

4,3911 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Divyesh Prajapati · Accepted Answer · 2019-11-07 09:19:09Z

You need to assign data table to global variable tablessp so it can be accessed inside other functions as and when required.

Here is code that might be helpful to you!

<script type="text/javascript">
// define variable for table globally
var tablessp;
$(document).ready(function() {
   initAdultTable();
});

function initAdultTable () {
    // do not return anything here
    tablessp = $('#members').DataTable({
        // Your code
    });
}

function searchAdultData() {
    office_position_id = $('#office_position_id').val();
    membership_status_id = $('#membership_status_id').val();
    agency_id = $('#agency_id').val();
    company_id = $('#company_id').val();

    tablessp
        .columns(14).search(office_position_id)
        .columns(16).search(membership_status_id)
        .columns(7).search(agency_id)
        .columns(5).search(company_id)
        .draw(); 
}

function resetAdultData() {
    $('#membersForm')[0].reset();

    tablessp.search('');
    tablessp.draw();    
}
</script>

Collectives™ on Stack Overflow

jQuery + PHP Datatables Server Side Processing with Column Filtering

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related