Problem displaying data in Datatable using a Stored Procedure

sebastianslz

I have the following problem when I want to visualize data in the famous Datatable library using a Store Procedure made in SQLServer, I use the Store Procedure to display my information in the table, the problem is that my data is not being reflected in the table, so this I use PHP and AJAX.

To see the information in my Datatable I use its API to attach secondary rows better known as Child Rows, the following is the AJAX code with which I build my table and its parameters:

/* Formatting function for row details - modify as you need */
function format ( d ) {
    // `d` is the original data object for the row
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Currency</td>'+
            '<td>'+d.Currency+'</td>'+
        '</tr>'+

    '</table>';
}

$(document).ready(function() {
    var table = $('#example').DataTable( {
       // "processing": true,
        "serverSide": true,
        "ajax": {
            url :"../utileria.php",
            type: "POST",
            data: {
                param: 1,               
            },
        },
        columns: [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "data" : "Order_Buy" },        
            { "data" : "Currency" },           

        ],
        "order": [[1, 'asc']]
    } );

    // Add event listener for opening and closing details
    $('#example tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    } );
} );

It is important to note that my parent row will contain the Order_Buy field and my child row the Currency field

The following is my PHP code where I call my Stored Procedure and the class conectar.php

utileria.php

<?php
    header('Content-Type: text/html; charset=utf-8');   
    $param = $_POST['param'];   
    switch($param) {
        case '1':               
                $query = array();
                include 'conectar.php';
                $sql = "{call SPTest(?)}";          
                $stmt = sqlsrv_query($conn, $sql);
                if ( $stmt === false) {
                    die( print_r( sqlsrv_errors(), true) );
                }   
                while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
                    $record = array(
                        "F.ORDER"       => utf8_encode ($row['Order_Buy']), //Orden de compra
                        "F.CURRENCY"          => utf8_encode ($row['Currency']), //Moneda

                    );
                    array_push($query, $record);
                }
                echo json_encode($query);
                sqlsrv_free_stmt( $stmt);       
                sqlsrv_close($conn);

            break;

    }

?>

I do not know why my table does not show me data, it could be that I have something wrong in any of my codes.

colin

You're not clear here on where the problem lies. Is the correct data being sent by your PHP script? Is the table loading but the child rows aren't?

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

sebastianslz

@colin Hi Colin, thank you very much for your answer, now I will answer with a couple of questions

How can I validate that my PHP is sending the correct data?

The table is loaded empty and is being displayed in an HTML as it is in the Datatables.net documentation

The only secondary row I have for this case is the one found in the format function of the javascript file.

I would like to add a test case, but here it is difficult for me since the information comes from a stored procedure which is on my server.

If it serves as additional information, perform a test with a json file before trying to connect it to my stored procedure and this one if it showed me the information that it had in said file.

colin

How can I validate that my PHP is sending the correct data?

Either debug on the server, or you can check on the browser's network tab.

To progress this, it would help to see it.

Colin

jimomak

$stmt = $pdo->prepare("CALL foo()");
$stmt->execute();
do {
$data = $stmt->fetchAll();
var_dump($data);
} while ($stmt->nextRowset() && $stmt->columnCount());
However, as you can see here is another trick have to be used: remember that extra result set? It is so essentially empty that even an attempt to fetch from it will produce an error. So, we cannot use just while ($stmt->nextRowset()). Instead, we have to check also for empty result. For which purpose PDOStatement::columnCount() is just excellent.https://phpdelusions.net/pdo#call This could be the issue. After one call to a sp...without 'nextRowset' you won't get any data...its empty! Just a shot in the dark.

allan

We can't really help you debugging your PHP, but I would suggest that you remove the serverSide: true option in your DataTable initialisation. With server-side processing you have to implement all of the search, order and paging in your PHP script - that is only worth doing if you have tens of thousands or more records in the table.

Beyond that, if you need help with your DataTable configuration, please give us a link to the page and I'd be happy to have a look at it.

Allan