The search functionality of dataTable is not working.

modihimanshu

$('.dataTable').dataTable({
"bAutoWidth": false,
"bProcessing": true,
"bServerSide": true,
"bEscapeRegex": true,
"iDisplayLength": 25,
"searching": false,
"sPaginationType": "full_numbers",
"sAjaxSource": <?php echo "\"$ajaxUrl\"" ?>
});

$ajaxParams = array('cluster' => $cluster, 'job' => $job, 'module' => $module, 'product' => $product, 'arch' => $arch, 'level' => $level);
$ajaxUrl = "job_query.php?" . fnEncodeParameterList($ajaxParams);

This is how i am rendering the table. The search funtionality is not working.

allan

"bServerSide": true,

Does your server-side script implement server-side processing? You've enabled server-side processing, so that script will need to do all the search and ordering required.

Out of interest, why are you using the legacy Hungarian notation (e.g. bAutoWidth rather than autoWidth)? I haven't used the legacy notation in the documentation for this site for a long time now.

Allan

modihimanshu

Hey Allan,
What i an doing here is a bug fix, i.e. trying to make the search functionality work. This is a part of a very old website that was used by very few people(which is why the legacy notations), but all of a sudden has become useful to a lot more.

This was my server side script earlier:

<?php

include("connect.php");
include("functions.php");

// Fetch query parameters.
$cluster = $_GET["cluster"];
$job = $_GET["job"];

// Set LIMIT clause.
$limitClause = fnGetLimitClause();

// Set ORDER BY clause. 
$orderByClause = fnGetOrderByClause();

// Set WHERE clause.
$whereClause = "WHERE cluster.name = '$cluster' AND job.name = '$job' ";

// Set HAVING filter clause.
$filterClause = fnGetFilterClause(fnGetFilterColumns());

// Set GROUP BY clause.
$groupByClause = fnGetGroupByClause(fnGetGroupByColumns());

// Fetch the base query string.
$baseQuery = fnGetBaseQuery();

// Submit the query without the filter to get the total row count.
$totalRowCount = fnGetRowCount("$baseQuery $whereClause $groupByClause");

// Submit the query with the filter to get the filtered row count.
$filteredRowCount = fnGetRowCount("$baseQuery $whereClause $groupByClause $filterClause");

// Submit the complete query one last time to fetch the data.
$result = fnGetQueryResult("$baseQuery $whereClause $groupByClause $filterClause $orderByClause $limitClause");

// Create the Ajax response message.
$output = fnCreateAjaxResponse($totalRowCount, $filteredRowCount, $result);
echo $output;

// Release mySQL resources.
mysqli_free_result($result);
mysqli_close($conn);


function fnCreateResponseRow($aRow)
{
    $output = array();

    $useCompilationId = ($_GET["module"] == 'on' &&
        $_GET["product"] == 'on' &&
        $_GET["arch"] == 'on' &&
        $_GET["level"] == 'on');

    if ($useCompilationId) {
        // We can use the 'compilationwarning' ID to specify a specific
        // module/product/architecture/level.  This is how Mani's reports
        // access warnings.php as well.
        $params = array('compilationId' => $aRow['compilationId']);
    } else {
        $params = array(
            'cluster' => $_GET["cluster"],
            'job' => $_GET["job"],
            'source' => $aRow['sourceFile']
        );
    }

    if ($_GET["module"] == 'on') {
        array_push($output, $aRow['module']);
        if ($useCompilationId == false) {
            $params['module'] = $aRow['module'];
        }
    }

    if ($_GET["product"] == 'on') {
        array_push($output, $aRow['product']);
        if ($useCompilationId == false) {
            $params['product'] = $aRow['product'];
        }
    }

    if ($_GET["arch"] == 'on') {
        array_push($output, $aRow['architecture']);
        if ($useCompilationId == false) {
            $params['arch'] = $aRow['architecture'];
        }
    }

    if ($_GET["level"] == 'on') {
        array_push($output, $aRow['level']);
        if ($useCompilationId == false) {
            $params['level'] = $aRow['level'];
        }
    }

    array_push($output, fnBuildReferenceLink("warnings_view.php", $params, $aRow['sourceFile']));
    array_push($output, $aRow['count']);
    array_push($output, $aRow['tmw']);
    array_push($output, $aRow['3rdparty']);

    return ($output);
}

// Convert column number to field name for column sorting.
function fnColumnToField($i)
{
    $tableColumns = fnGetFilterColumns();
    return ($tableColumns[$i]);
}

function fnGetGroupByColumns()
{
    $tableColumns = array();

    if ($_GET["module"] == 'on') {
        array_push($tableColumns, 'module.name');
    }

    if ($_GET["product"] == 'on') {
        array_push($tableColumns, 'product.name');
    }

    if ($_GET["arch"] == 'on') {
        array_push($tableColumns, 'architecture.name');
    }

    if ($_GET["level"] == 'on') {
        array_push($tableColumns, 'level.name');
    }

    array_push($tableColumns, 'srcFile.name');
    return ($tableColumns);
}

function fnGetFilterColumns()
{
    $tableColumns = array();

    if ($_GET["module"] == 'on') {
        array_push($tableColumns, 'module');
    }

    if ($_GET["product"] == 'on') {
        array_push($tableColumns, 'product');
    }

    if ($_GET["arch"] == 'on') {
        array_push($tableColumns, 'architecture');
    }

    if ($_GET["level"] == 'on') {
        array_push($tableColumns, 'level');
    }

    array_push($tableColumns, 'sourceFile', 'count', 'tmw', '3rdparty');
    return ($tableColumns);
}

function fnGetBaseQuery()
{
    // NOTE: The column order in the result set below must match the display order
    // for column sorting to work correctly.
    return ("
        SELECT module.name AS module, product.name AS product, architecture.name AS architecture, 
           level.name AS level, srcFile.name AS sourceFile, COUNT(*) AS count,
               SUM(IF(CONCAT(warnPath.name, warnFile.name) NOT LIKE '%/hub/3rdparty/%' AND CONCAT(warnPath.name, warnFile.name) NOT LIKE '%/3p_mirror/%',1,0)) AS tmw,
               SUM(IF(CONCAT(warnPath.name, warnFile.name) LIKE '%/hub/3rdparty/%' OR CONCAT(warnPath.name, warnFile.name) LIKE '%/3p_mirror/%',1,0)) AS 3rdparty,
               compilationwarning.id AS compilationId
        FROM cluster
        JOIN job ON cluster.id = job.cluster_id
        JOIN modulelevel ON job.id = modulelevel.job_id 
        JOIN compilationwarning ON modulelevel.id = compilationwarning.modulelevel_id
        JOIN sourcefile AS srcFile ON srcFile.id = compilationwarning.sourcefile_id
        JOIN path AS srcPath ON srcPath.id = srcFile.path_id
        JOIN warning ON compilationwarning.id = warning.compilation_id
        JOIN sourcefile AS warnFile ON warnFile.id = warning.sourcefile_id
        JOIN path AS warnPath ON warnPath.id = warnFile.path_id
        JOIN architecture ON architecture.id = modulelevel.architecture_id
        JOIN level ON level.id = modulelevel.level_id
        JOIN module ON module.id = modulelevel.module_id
        JOIN product ON product.id = module.product_id ");
}


<?php
>
```
?>


I modified the whereclause to make the search work, but that results in my table showing no data. here is the modified code(new code enclosed in ** **):

<?php

include("connect.php");
include("functions.php");

// Fetch query parameters.
$cluster = $_GET["cluster"];
$job = $_GET["job"];

// Set LIMIT clause.
$limitClause = fnGetLimitClause();

// Set ORDER BY clause.
$orderByClause = fnGetOrderByClause();

// Set WHERE clause.
$whereClause = "WHERE cluster.name = '$cluster' AND job.name = '$job' ";

// Set HAVING filter clause.
$filterClause = fnGetFilterClause(fnGetFilterColumns());

// Set GROUP BY clause.
$groupByClause = fnGetGroupByClause(fnGetGroupByColumns());

// Receive and process search parameter.
**$searchValue = $_GET['search']['value'];
$searchCondition = "";
if (!empty($searchValue)) {
// Add conditions to match against relevant columns for search.
$searchCondition = "AND (module.name LIKE '%$searchValue%' OR product.name LIKE '%$searchValue%' OR architecture.name LIKE '%$searchValue%' OR level.name LIKE '%$searchValue%' OR srcFile.name LIKE '%$searchValue%')";
}

// Modify the WHERE clause to include the search condition.
$whereClause .= $searchCondition;**

// Fetch the base query string.
$baseQuery = fnGetBaseQuery();

// Submit the query without the filter to get the total row count.
$totalRowCount = fnGetRowCount("$baseQuery $whereClause $groupByClause");

// Submit the query with the filter to get the filtered row count.
$filteredRowCount = fnGetRowCount("$baseQuery $whereClause $groupByClause $filterClause");

// Submit the complete query one last time to fetch the data.
$result = fnGetQueryResult("$baseQuery $whereClause $groupByClause $filterClause $orderByClause $limitClause");

// Create the Ajax response message.
$output = fnCreateAjaxResponse($totalRowCount, $filteredRowCount, $result);
echo $output;

// Release mySQL resources.
mysqli_free_result($result);
mysqli_close($conn);

// ... (rest of your functions remain unchanged)
```

cant figure out whats wrong with it. any sort of help is much appreciated.

allan

What is the response from the server? Does it include any error messages?

I'd suggest outputting the generated SQL statement and seeing if you can run that directly against the database. My guess is that is results in invalid SQL (although I'm not immediately seeing where).

Allan