JSON not being parsed into a table

mmcnair80 · November 2016

I am running, as much as possible, what I have found on this site. I've made a few modifications to make it work for me, and it does return JSON.

Here's the Debug

But the response is not being turned into a table. what am I not doing?

Here's my ServerSide.php:

<?php
$Page = '';
if (isset($_GET['PageName']))
{
    $Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
    $Page = $_POST['PageName'];
}
if($Page == '')
{
    $Page = 'TableHeadings';
}

include 'DBConn.php';
$headings = array();
$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
    $headings[$row] = $rHeadings[$row]["Headings"];
    $tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}

foreach($headings as $index => $columnName)
{
    $ColumnHeader[] = array('db'=>$columnName,'dt'=>$index);
}

//DB table to use
$table = $Page;

//Table's primary key
$primaryKey = 'id';

//Array of database columns which should be read and sent back to DataTables
$columns = $headings;

//SQL server connection information
$sql_details = array(
    'user'=> 'User',
    'pass'=> 'Password',
    'db'=> 'DataBase',
    'host'=> 'Server');
$connectionInfo = array( "Database"=>$dbname, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect( $servername, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

// Get data to display 
$Query = "
    SELECT count($primaryKey) over() as Row_Count, ".str_replace(" , ", " ", implode(", ", $columns))."
    FROM   $table";

$rResult = sqlsrv_query( $conn, $Query );
if( $rResult === false) {
    die( print_r( sqlsrv_errors(), true) );
}

// Data set length after filtering
$iFilteredTotal = sqlsrv_num_rows( $rResult );

// Total data set length 
$sQuery = "SELECT COUNT($primaryKey)
    FROM $table";
$rResultTotal = sqlsrv_query( $conn, $sQuery );
$aResultTotal = sqlsrv_fetch_array( $rResultTotal, SQLSRV_FETCH_ASSOC);
$iTotal = $aResultTotal;

// Output
$output = array(
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "Data" => array());

while ( $aRow = sqlsrv_fetch_array( $rResult,SQLSRV_FETCH_ASSOC ) )
{
    $row = array();
    for ( $i=0 ; $i<count($columns) ; $i++ )
    {
        if ( $columns[$i] == "version" )
        {
            // Special output formatting for 'version' column 
            $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
        }
        else if ( $columns[$i] != ' ' )
        {
            // General output 
            $row[] = $aRow[ $columns[$i] ];
        }
    }
    $output['Data'][] = $row;
}
//http_response_code(606);

echo json_encode($output);

<?php
>
```
?>


Here's the RunningServerSide.php:

```php
<?php
$Page = '';
if (isset($_GET['PageName']))
{
    $Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
    $Page = $_POST['PageName'];
}
include 'DBConn.php';
$headings = array();
$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
    $headings[$row] = $rHeadings[$row]["Headings"];
    $tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}
$Edit = 0;

<?php
>
<!DOCTYPE html>

    
        
        
        <?php require 'StyleLinks.php'; ?>
?>


        <?php include 'DataTableLinks.php'; ?>
    </head>
    <body>
        <table class="NormalTable display nowrap"  style="width: 100%; border: 1px" id="DataTable">
            <thead>
                <tr><?php echo "\n";
                        if($Edit == 1)
                        {?>
                    <th class="cell">Edit</th><?php       echo "\n";                          
                        }
                        foreach($headings as $heading)
                        {?>
                    <th class="cell"><?php echo $heading; ?></th><?php echo "\n";
                        }?>
                </tr>
            </thead><?php echo "\n";?>
        </table>
    </body>
</html>

Then the DataTables are initialized here:

<script>
    $(document).ready(function ()
    {
        $('#DataTable').DataTable(
      {
          "lengthMenu": [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
          "ScrollX": true,
          "dom": '<"top"Biflp<"clear">>rt<"bottom"ip<"clear">>',
          buttons: [{ extend: 'collection', text: 'Selection', buttons: ['selectAll', 'selectNone'] }, { extend: 'collection', text: 'Export', buttons: ['excel', 'csv', 'pdf']}],
          fixedHeader: { header: true, footer: false },
          select: true,
          "processing": true,
          "serverSide": true,
          "ajax": { "url": "ServerSide.php", "dataType": "json", "success": function(data){$('#DataTable').append(data);},"error": function(){alert("This has failed");} }
      });
    });
</script>

But I cannot get anything to actually show up. I've checked in Developer Tools for my browser and fixed all the errors that were in the console. The page ServerSide.php returns successfully and has a response like this:

{"iTotalRecords":{"":280},"iTotalDisplayRecords":false,"Data":[[1,"TableUpdates","Id"],[2,"TableUpdates","TableName"],[3,"TableUpdates","UpdateDate"],[1,"ApprovedProjects","Id"],[2,"ApprovedProjects","Company"],[3,"ApprovedProjects","State"],[4,"ApprovedProjects","Project"],[5,"ApprovedProjects","SubProject"],[6,"ApprovedProjects","Division"],[7,"ApprovedProjects","PlantNum"],[8,"ApprovedProjects","Exchange"],[9,"ApprovedProjects","DeptNum"],[10,"ApprovedProjects","ClassOfPlant"],

There are a total of 280 rows for this table. Nothing shows except the 2 button collections and the table header. There is no data.

Why don't I get anything to show up?

allan · November 2016

Looking at the JSON response from the debug trace (thanks for that) there are serveral issues. You currently have it returning:

    "iTotalRecords": {
        "": 280
    },
    "iTotalDisplayRecords": false,
    "Data": [

Data should be data. Javascript is case sensitive.
iTotalRecords should be recordsTotal and it should be an integer, not an object
iTotalDisplayRecords should be recordsFiltered and it should be an integer, not a boolean.
Don't use ajax.success - as the ajax documentation notes DataTables uses that property itself and suppling your own would override it.
Likewise with the ajax.error property.

There are a total of 280 rows for this table.

For so few records I wouldn't suggest using server-side processing. All you are doing is adding complexity and latency. Only when you are working with tens of thousands of records is server-side processing useful.

This page documents the response parameters expected by DataTables.

Allan

mmcnair80 · November 2016

Thank you Allen, that helped a lot. I now have the table displaying. However I'm stilling missing something. I have it set so that it should be displaying only 25 rows, but it is displaying all 280 and there are 12 pages showing all 280 on each page. I have a new debug here. I re-worked my ServerSide.php so that it would output the appropriate json:

// Get data to display 
$Query = "SELECT  ".str_replace(" , ", " ", implode(", ", $columns))." FROM   $table";
//print_r($Query); echo "<br>";

$getQuery = $conn->query($Query);
$QueryResult = $getQuery->fetchALL(PDO::FETCH_ASSOC);
$countQuery = count($QueryResult);

// Output
$output = array(
    "recordsTotal" => $countQuery,
    "recordsFiltered" => $countQuery,
    "data" => array()
);
$row = array();

foreach($QueryResult as $value)
{
    //print_r($value); echo "<br>";
    $RowsFound = count($value);
    $count = 0;
    //print_r($value[$ColumnHeader[$count]['db']]);
    while($count < $RowsFound)
    {
        $row[$ColumnHeader[$count]['dt']] = $value[$ColumnHeader[$count]['db']];
        $count = $count + 1;
    }
    //print_r($row); echo "<br>";
    //echo $RowsFound; echo "<br>";
    $output['data'][] = $row;
}

//http_response_code(606);

echo json_encode($output);

I'm using this table just as testing this. The tables that this will work with are between 50 and 160 columns and 10,000+ rows. They were failing to work before, timing out usually, and it was suggested that I use server-side processing.

I now have everything working except figuring out how to display only a few rows at a time instead of all of them at once. I'm guessing that it has something to do with the recordsFiltered, but I'm not sure how to fix it since I thought that the initiliazation of the DataTable with the "lengthMenu": [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]], is what would set the display size of the table.

I have also noted that the sorting doesn't work either. I'm more worried about the display size though.

allan · November 2016

The problem is that your script isn't limiting the amount of data that it gets from the database. You would need to use LIMIT / OFFSET to do that.

Likewise you don't have an ORDER BY which is why the ordering isn't working.

The demo ssp.class.php file might be of some interest to see how I've done it.

Allan

mmcnair80 · November 2016

i must have done something wrong, I don't see where I can mark this as answered. I haven't completed my updates, but it is working and I've gotten further along than when I posted this question.

allan · November 2016

Only questions asked with "Ask a question" can be marked as answered. Threads started with "New discussion" don't have such an option.

Good to hear you've been able to make progress.

Allan

mmcnair80 · November 2016

Must have hit the wrong button then.

Yes, making much headway. I'm going through that ssp.class.php file now and pulling out the parts that I can use and converting them to use sqlsrv pdo instead of MySql. It'll be a while yet before I have that working the way I want, but I'm on the right track.

Thanks

JSON not being parsed into a table

JSON not being parsed into a table

Replies

Howdy, Stranger!

Categories

DataTables

JSON not being parsed into a table

JSON not being parsed into a table

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables