The search functionality of dataTable is not working.

The search functionality of dataTable is not working.

modihimanshumodihimanshu Posts: 4Questions: 2Answers: 0

$('.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.

Answers

  • allanallan Posts: 62,211Questions: 1Answers: 10,205 Site admin

    "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

  • modihimanshumodihimanshu Posts: 4Questions: 2Answers: 0

    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.

  • allanallan Posts: 62,211Questions: 1Answers: 10,205 Site admin

    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

Sign In or Register to comment.