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

Hiebs915

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.

colin

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

Colin

Hiebs915

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'
    }
});

kthorngren

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

Hiebs915

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;
    }

<?php
>
?>

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

kthorngren

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

Hiebs915

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;
        }
    }

<?php
>
?>

kthorngren

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

Hiebs915

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.

Hiebs915

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