Tablesorter: Filtering and Ajax Pager Issues

Question

I am noticing odd behavior with tablesorter when the filter widget and pager addon are enabled. Here's the scenario:

I have a table with a total of 366 entries that I have applied the filter widget and pager addon to. Below is a snippet of the table data set that I am operating on.

Say for example that I filter the results by Register BIN_d_USE_RBLKS\s (where \s is simply denoting that I have included a white space char in the search)...

As seen in the screen shot below, the filtered results that are displayed do not change from the previously displayed results. Yet the pager was accurately updated (as seen in the upper left hand corner) with the Ajax query indicating that there were no results matching those search parameters.

Ideally the filtered search would return an empty set, yet it is not. How can I fix this? Is there a setting I am missing?

Update 1

I understand that the pager sets both the core config.serverSideSorting option and the filter_serversideFiltering widget option to true. This means that server is responsible for sorting the data based on the filter criteria that pager passes to the server. Below is a snippet of the PHP server side code that is being used to generate the Ajax response.

$page = $_GET['page'];
$size = $_GET['size'];
$design_filter = stripslashes($_GET['filter'][0]);
$reg_filter = stripslashes($_GET['filter'][1]);

// row entry offset.
$offset = $page*$size;

try {
    $con = new PDO( "mysql:host=$host;dbname=$dbname", $username, $passwd);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // If the filter is surrounded in quotes, treat it literally. Otherwise,
    // match it as a substring. 
    $quote_regex = '/^([\"\']).*\1$/m';
    $literal_design = preg_match($quote_regex, $design_filter);
    $literal_reg = preg_match($quote_regex, $reg_filter);

// both filters specify literal interpretation
if ($literal_design && $literal_reg)
{
    SELECT SOMETHING
}

// only treat design filter as a literal
if ($literal_design && !$literal_reg)
{
    SELECT SOMETHING
}

// only treat register filter as a literal
if (!$literal_design && $literal_reg)
{
    SELECT SOMETHING
}

// no filters are literals
if (!$literal_design && !$literal_reg)
{
    SELECT SOMETHING
}

// get the page and size limited entries
$stmt = $con->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

$result = $con->query("SELECT FOUND_ROWS()")->fetch();
$total_rows = $result['FOUND_ROWS()'];


$data_payload = array("total_rows" => $total_rows, "headers" => array("Design", "Register"), "rows" => array());
foreach ($rows as $row) {
    $reg_rule = array("Design" => $row['design_id'], "Register" => $row['data_register']);
    array_push($data_payload['rows'], $reg_rule);
}

$json_payload = json_encode($data_payload);
echo $json_payload;

I have also verified with manual inspection of the SQL queries that the filters limit the returned response from the database appropriately. I have filtering working well, all except the case that I present above.

UPDATE 2

If no data matches a search filter, the server should not respond with a rows field in the JSON.

// get the page and size limited entries
$stmt = $con->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

$result = $con->query("SELECT FOUND_ROWS()")->fetch();
$total_rows = $result['FOUND_ROWS()'];

if ($total_rows == 0)
{
    $data_payload = array("total_rows" => $total_rows, "headers" => array("Design", "Register"));
    echo json_encode($data_payload);
} else {
    $data_payload = array("total_rows" => $total_rows, "headers" => array("Design", "Register"), "rows" => array());
    foreach ($rows as $row) {
        $reg_rule = array("Design" => $row['design_id'], "Register" => $row['data_register']);
        array_push($data_payload['rows'], $reg_rule);
    }

    $json_payload = json_encode($data_payload);
    echo $json_payload;
}

This ensures that the filter widget will clear the table when a search doesn't have a matching entry in the database server side. I didn't see this anywhere in the documentation, and I highly recommend adding this somewhere. This would have saved me hours of my time.

Answer 1

If no data matches a search filter, the server should not respond with a rows field in the JSON.

// get the page and size limited entries
$stmt = $con->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

$result = $con->query("SELECT FOUND_ROWS()")->fetch();
$total_rows = $result['FOUND_ROWS()'];

if ($total_rows == 0)
{
    $data_payload = array("total_rows" => $total_rows, "headers" => array("Design", "Register"));
    echo json_encode($data_payload);
} else {
    $data_payload = array("total_rows" => $total_rows, "headers" => array("Design", "Register"), "rows" => array());
    foreach ($rows as $row) {
        $reg_rule = array("Design" => $row['design_id'], "Register" => $row['data_register']);
        array_push($data_payload['rows'], $reg_rule);
    }

    $json_payload = json_encode($data_payload);
    echo $json_payload;
}

This ensures that the filter widget will clear the table when a search doesn't have a matching entry in the database server side. I didn't see this anywhere in the documentation, and I highly recommend adding this somewhere. This would have saved me hours of my time.

Answer 2

When using the pager with ajax, the pager sets both the core config.serverSideSorting option and the filter_serversideFiltering widget option to true (src) because there is no way for the client side to perform these functions without output from the server, or having the entire database on the client side, but then you wouldn't need ajax.

The server should be sorting the database and using the filter(s) provided by the ajax request and return the results to the pager ajax functions for display.