How do I deal with the database returning NULL to dataTables?

How do I deal with the database returning NULL to dataTables?

Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0
edited February 16 in DataTables 1.10

Debugger code (debug.datatables.net):
Warning: Undefined variable $dataArray in C:\Users\mhiebing\Documents\GitHub_Repos\Utah_OG_Website\monthlyProductionReports_backend.php on line 44
null

Error messages shown:
DataTables warning: table id=clickedTable_10_2021 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

Description of problem:

Dear dataTables,

I am using dataTables to show some data from a SQL Server db using PHP as the backend and PHP/Jquery as the frontend. Everything is working but when a query is sent to the database and no data is available, it returns "Null". DataTables is saying its getting an invalid json response. Is there an easy method for dealing with something like this through dataTables? Ideally, I'd like the table to display "No data available", or something similar inside the the dataTables table area. If I can't do that, I could try preventing the table from being created if I get a null back from the SQL server but with the way things are ordered, I'd have to reconfigure quite a bit of code.

Answers

  • colincolin Posts: 14,449Questions: 1Answers: 2,462

    null, if that's what is being sent, wouldn't be valid JSON. This thread should help, it's discussing the same thing,

    Colin

  • Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0

    It looks like I could use{"data":[],"draw":1,"recordsFiltered":0,"recordsTotal":0} or

    dataSrc: function (json) {
        if (!json.data) {
            return [];
        } else {
            return json.data;
        }
    

    but I'm a little confused on how I'd integrate those pieces in my code.

    Here's my dataTables function:

    $(`#clickedTable_${month}_${year}`).dataTable({
        "paging": false,
        "ordering": false,
        "info": false,
        "searching": false,
        "destroy": true,
        "ajax": {
            "url": "monthlyProductionReports_backend.php",
            "type": "POST",
            "data": {"monthYearTerm": monthYearTermArray}
        },
        "columns": [
            { "data": "DataGroup", "visible": false},
            { "data": "ProductType"},
            { "data": "MthlyTot"},
            { "data": "YearToDateTot"},
            { "data": "CumTot"},
            { "data": "ActiveWells"},
        ],
        order: [[0, 'asc']],
        rowGroup: {
            dataSrc: 'DataGroup'
        }
    });
    
  • kthorngrenkthorngren Posts: 16,799Questions: 25Answers: 3,982

    Ideally your server script would return an empty array, ie, {"data":[]} if you have no results. The other properties, like draw, aren't needed since you don't have serverSide processing enabled.

    I haven't tried ajax.dataSrc to solve this situation so not sure its the correct solution. Looks like it should work though. That code would be added to your ajax option, like this:

        "ajax": {
            "url": "monthlyProductionReports_backend.php",
            "type": "POST",
            "data": {"monthYearTerm": monthYearTermArray},
            dataSrc: function (json) {
                if (!json.data) {
                    return [];
                } else {
                    return json.data;
                }
        },
    

    Kevin

  • Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0
    edited February 18

    No dice :( I get the same error message if I try incorporating the dataSrc: function(json).

    Here's my php backend code if that helps:

    <?php
        include 'oilgas/includes/MSSQLTestServerConnection.php';
        include 'oilgas/includes/printErrors.php';
    
        if (empty($_POST["monthYearTerm"])) {
            echo "Nothing was entered in the input field";
        } else {
            $month['startMonth'] = $_POST["monthYearTerm"][0];
            $year['startYear'] = $_POST["monthYearTerm"][1];
            $term['rptType'] = $_POST["monthYearTerm"][2];
        }
    
        // Parameters passed into SQL SP.
        $sql = "EXEC dbo.spOG_GetMonthlyProductionReports @startMonth = ?, @startYear = ?, @rptType = ?";
        $params = array(
            array($month['startMonth'], SQLSRV_PARAM_IN),
            array($year['startYear'], SQLSRV_PARAM_IN),
            array($term['rptType'], SQLSRV_PARAM_IN)
        );
        $stmt = sqlsrv_prepare($conn, $sql, $params);
    
        // Check if any errors occur during the preparation and execution of the SQL.
        if ($stmt === false) {
            echo "Statement Error"."<br>";
            PrintErrors();
        } elseif ( sqlsrv_execute( $stmt ) === false ) {
            echo "Execution Error"."<br>";
            PrintErrors();
        } else {
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)) {
                $dataArray['data'][] = array(
                    "DataGroup" => (string)$row['DataGroup'],
                    "RptKey" => (string)$row['RptKey'],
                    "RptTypeKey" => (string)$row['RptTypeKey'],
                    "SumItemTypeKey" => (string)$row['SumItemTypeKey'],
                    "MthlyTot" => (string)$row['MthlyTot'],
                    "YearToDateTot" => (string)$row['YearToDateTot'],
                    "CumTot" => (string)$row['CumTot'],
                    "ActiveWells" => (string)$row['ActiveWells'],
                    "ProductType" => (string)$row['ProductType'],
                    "JoinKey" => (string)$row['JoinKey']
                );
            }
            $jsonData = json_encode($dataArray, JSON_PRETTY_PRINT);
            // echo "<pre>$jsonData</pre>";
            echo $jsonData;
        }
    ?>
    
    

    Here's a picture of the table that I'm trying to build and the "null" that's being returned from the server:

  • kthorngrenkthorngren Posts: 16,799Questions: 25Answers: 3,982

    I don't use PHP but maybe you can place another elseif clause before this:

        } else {
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)) {
    

    Check to see if there are results and if not set $jsonData to {"data": []]}. The error you are getting is indicating that $dataArray is undefined in this line:

    $jsonData = json_encode($dataArray, JSON_PRETTY_PRINT);
    

    Kevin

  • Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0

    On the server-side, I could do something like this but it seems like a lot of code just to tell the front end there aren't any results.

    <?php
        include 'oilgas/includes/MSSQLTestServerConnection.php';
        include 'oilgas/includes/printErrors.php';
    
        if (empty($_POST["monthYearTerm"])) {
            echo "Nothing was entered in the input field";
        } else {
            $month['startMonth'] = $_POST["monthYearTerm"][0];
            $year['startYear'] = $_POST["monthYearTerm"][1];
            $term['rptType'] = $_POST["monthYearTerm"][2];
        }
    
        // Parameters passed into SQL SP.
        $sql = "EXEC dbo.spOG_GetMonthlyProductionReports @startMonth = ?, @startYear = ?, @rptType = ?";
        $params = array(
            array($month['startMonth'], SQLSRV_PARAM_IN),
            array($year['startYear'], SQLSRV_PARAM_IN),
            array($term['rptType'], SQLSRV_PARAM_IN)
        );
        $stmt = sqlsrv_prepare($conn, $sql, $params);
    
        // Check if any errors occur during the preparation and execution of the SQL.
        if ($stmt === false) {
            echo "Statement Error"."<br>";
            PrintErrors();
        } elseif ( sqlsrv_execute( $stmt ) === false ) {
            echo "Execution Error"."<br>";
            PrintErrors();
        } else {
            if (is_null($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH))) {
                $dataArray['data'][] = array(
                    "DataGroup" => ["No Data"],
                    "RptKey" => [],
                    "RptTypeKey" => [],
                    "SumItemTypeKey" => [],
                    "MthlyTot" => [],
                    "YearToDateTot" => [],
                    "CumTot" => [],
                    "ActiveWells" => [],
                    "ProductType" => [],
                    "JoinKey" => []
                );
                $jsonData = json_encode($dataArray, JSON_PRETTY_PRINT);
                echo $jsonData;
            } else {
            while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)) {
                $dataArray['data'][] = array(
                    "DataGroup" => (string)$row['DataGroup'],
                    "RptKey" => (string)$row['RptKey'],
                    "RptTypeKey" => (string)$row['RptTypeKey'],
                    "SumItemTypeKey" => (string)$row['SumItemTypeKey'],
                    "MthlyTot" => (string)$row['MthlyTot'],
                    "YearToDateTot" => (string)$row['YearToDateTot'],
                    "CumTot" => (string)$row['CumTot'],
                    "ActiveWells" => (string)$row['ActiveWells'],
                    "ProductType" => (string)$row['ProductType'],
                    "JoinKey" => (string)$row['JoinKey']
                );
            }
            $jsonData = json_encode($dataArray, JSON_PRETTY_PRINT);
            // echo "<pre>$jsonData</pre>";
            echo $jsonData;
            }
        }
    ?>
    
    

  • kthorngrenkthorngren Posts: 16,799Questions: 25Answers: 3,982
    edited February 18

    ll you need is to return an empty array, ie, {data: []}. But something like that should work.

    **EDIT:* Maybe someone versed in PHP can give recommendations on a better way to structure the code so you don't perform two SQL queries.

    Kevin

  • Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0
    edited February 18

    I think I have to specifically tell dataTables that each column is empty. I haven't figured out a way to send a single statement like, {data: []} and have it work but at least I've got something that tells the user there isn't any data for their selection.

    I'm surprised there's not a default option for dealing with null data in dataTables.

  • Hiebs915Hiebs915 Posts: 9Questions: 2Answers: 0
    edited February 18

    Appreciate your help @kthorngren and @colin. I'll ty chatting with a few guys in a PHP slack I'm part of.

Sign In or Register to comment.