totalapi of footerCallback not working as intended. Search empty results give error.

edw

My Datatables is working fine and displaying the data. It is just when searching random words (does not match any results) gives invalid json error. But when searching data that exists in table, it gives results. I tried removing the **footerCallback **function, but still gives error.

dueTbl = $('#due_table').DataTable({
        dom: '<"row"B>flr<"py-2 my-2"t>ip',
        "processing": true,
        "serverSide": true,
        "ajax": {
            data: {data:selected_book_year},
            url: "bookkeeping/get_records.php",
            method: 'POST'
        },
        columns: [
            {
                data: 'full_name',
                className: 'py-1 px-1'
            },
            {
                data: 'January',
                orderable: false,
                className: 'text-center py-1 px-1',
                render: function(data, type, row, meta) {
                            return '<a class="edit_record" href="javascript:void(0)" data-id="' + (row.member_id) + '" data-bss-tooltip="" title="Edit Record"><div class="card bookkeepingbox"><div class="card-body bookkeepingbox2 bookkeepingbox-green"></div></div></a>';
            },
            // repeated until december///
            {
                data: 'year_total',
                orderable: false,
                className: 'text-center py-1 px-1',
                render: function(data, type, row, meta) {
                    return '₱'+(row.year_total)+'';
                }
            }
        ],
        "footerCallback": function ( row, data, start, end, display ) {
            var api = this.api(), data;
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\₱,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // Total over all pages
            var total = api
                .column( 13 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
            
            
 
            // Total over this page
             var pageTotal = api
                .column( 13, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
            
            
 
            // Update footer
            $( api.column( 13 ).footer() ).html(
                '₱'+pageTotal +' ( ₱'+ total +' total)'
            );
        },
        "columnDefs": [ {
            "targets": [1-12],
            "orderable": false
          } ],
        "lengthMenu": [ [5, 10, 20, -1], [5, 10, 20, "All"] ],
        "order": [
            [0, "asc"]
        ],
        "mark": true,
        initComplete: function(settings) {
            $('.paginate_button').addClass('p-1')
        }
    });

my get_records.php file

session_start();
include('../../includes/connection.inc.php');
extract($_POST);
$log_id = $_SESSION['id'];
$temp = 'verified';
$tyear = $_POST['data'];

$totalCount = $conn->query("SELECT * FROM year_rec INNER JOIN members ON year_rec.memb_id=members.member_id INNER JOIN users ON users.id=members.member_id WHERE users.user_status='$temp' AND year_rec.year='$tyear'")->num_rows;
$search_where = "";
if(!empty($search)){
    $search_where = " where ";
    $search_where .= " full_name LIKE '%{$search['value']}%' ";
}

$columns_arr = array("full_name");       
$query = $conn->query("SELECT * FROM year_rec INNER JOIN members ON year_rec.memb_id=members.member_id INNER JOIN users ON users.id=members.member_id AND users.user_status='$temp' AND year_rec.year='$tyear' {$search_where} ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']} limit {$length} offset {$start} ");
$recordsFilterCount = $conn->query("SELECT * FROM year_rec INNER JOIN members ON year_rec.memb_id=members.member_id INNER JOIN users ON users.id=members.member_id AND users.user_status='$temp' AND year_rec.year='$tyear' {$search_where} ")->num_rows or die($conn->error);
$showAll = $conn->query("SELECT * FROM year_rec INNER JOIN members ON year_rec.memb_id=members.member_id INNER JOIN users ON users.id=members.member_id AND users.user_status='$temp' AND year_rec.year='$tyear' {$search_where} ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']}");

$recordsTotal= $totalCount;
$recordsFiltered= $recordsFilterCount;
$data = array();
$i= 1 + $start;
if($length == -1) {
    while($row = $showAll->fetch_assoc()){
            $data[] = $row;
    }
} else {
while($row = $query->fetch_assoc()){
        $row['no'] = $i++;
        $data[] = $row;
}
}
echo json_encode(array('draw'=>$draw,
                       'recordsTotal'=>$recordsTotal,
                       'recordsFiltered'=>$recordsFiltered,
                       'data'=>$data
                       )
);

Also, the **totalapi ** (total over all pages) in the footerCallback is not working as intended. It gives the same results as (total over this page)

When search does not match any results, gives error. I checked the error on the "response" and says "This request has no response data available".

colin

Have you followed the steps in the technical notes linked to in the error? That'll be the place to start. If so, what did you find? What does the server return when there are no hits?

Colin

edw

Thank you colin. I actually solved the search no results error. I forgot to add ->num_rows; on the $recordsFilterCount line.

However, the total over all the pages on the footerCallback function still not working as intended. Still showing the same results as the total over this page.

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin