Problem with simple SSP page

gcac

Have a very simple page / datatable I'm trying to get going. Basically no editor functions, and just want to grab some data from a table and display within a datatable. I've done more complex ones with Editor involved but this is stumping me (has to be something easy)

Ideally, I'd want to use something in the ssp script to generate the table headers dynamically like I do with columns and specifying the title option, but this is the next problem to solve. For now I can make a static <thead> and <th> entries.

No matter what I try, I get the invalid JSON error.

When I use debugging tools as mentioned in DataTables Technotes I don't see what you'd expect. In most of my pages with DT/Editor, when I do the XHR and look I see the request to ajax_*.php for that page and can click the response and see the JSON.

In this example, when I narrow to XHR requests, I see main.php?_=1512509393433 and when looking at the response i see the actual source for main.php.

This is my Server Side script:

ajax_swData.php

<?php

//error_reporting(E_ALL);
//ini_set("display_errors", 1);

// DataTables PHP library
include( "../../php/DataTables.php" );
require_once("../_includes/functions_global.php");
sec_session_start();

// set the timezone
date_default_timezone_set('America/Chicago');

// DB table to use
$table = 'licenses2';

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

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'submitted_by', 'dt' => 'Name' ),
    array( 'db' => 'department',  'dt' => 'Dept'    ),
    array( 'db' => 'software_name',   'dt' => 'Software' ),
    array( 'db' => 'machines',     'dt' => 'UM Property #' )
    );

$sql_details = array(
    'user' => $globalConfig['sw_sqluser'],
    'pass' => $globalConfig['sw_sqlpass'],
    'db'   => $globalConfig['sw_sqldb'],
    'host' => $globalConfig['sw_sqlhost']
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require_once ( '../_includes/ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );


?>

The HTML is main.php

<?php
require_once ("../_includes/global_header.php");
// GCAC Admin Template page built on Bootstrap


?>

<!-- Page Content -->
<div id="page-wrapper">

    <div class="container-fluid">
        <div class="row">
            <div class="col-lg-12">
                <h3 class="page-header">TESTING</h3>
                <div class="panel panel-primary">

                    <div class="panel-heading">Software Information</div>
                    <div class="panel-body">

                        <div class="dataTable_wrapper">
                            <table width="100%"
                                class="table table-striped table-bordered table-hover" 
                                id="swData">
                                <thead>
                                    <tr>
                                        <th>Name</th>
                                        <th>Dept.</th>
                                        <th>Software</th>
                                        <th>UM Property #</th>
                                    </tr>
                                </thead>
                            </table>
                        </div>
                    </div>

                </div>
                <!-- /.col-lg-12 -->
            </div>
            <!-- /.row -->
        </div>
        <!-- /.container-fluid -->
    </div>
    <!-- /#page-wrapper -->

<?php require_once("../_includes/global_footer.php"); ?>

<script type="text/javascript">


$(document).ready(function() {

    $('#swData').DataTable( {
        dom: "lfrt",
        ajax: {
            "processing": true,
            "serverSide": true,
            "ajax": "ajax_swData.php"
        },
        "lengthMenu": [ [20, 25, 50, -1], [20, 25, 50, "All"] ],
    } );
    
} );
</script>

All in all, pretty simple, you'd think. I am including Datatables.php form my `global_header.php' file so it's available everywhere.

Any ideas much appreciated.

N

gcac

Update: if I navigate to ajax_swData.php directly, I get JSON output.

It starts like this:

{"draw":0,"recordsTotal":12747,"recordsFiltered":12747,"data":[{"Name":"Customer Name","Dept":"AAS","Software":"Symantec Antivirus Renewal","UM Property #":""},

The JSON data does have some \r\n and individual \r and \n in some fields of the data, if that matters. I'm def no JSON expert.

Am I missing something on the draw part? The actual JSON output is 12k rows, so I grabbed the first few and then the JSON terminating characters, and it validated ok.

gcac

And I think I found the answer to my table headers part of this here:

Datatables Automatic Row ID

gcac

I also found a similar problem here: No Matching Records found but there he wasn't getting the JSON error, rather no records.

In my case I wonder if the fact that I am returning no data (ie all records filtered) is why the JSON error is triggered?

Also in my case (ajax_swData.php) above, I am clearly not doing any filtering, just using the simple example provided.

gcac

UPDATE 2:

I changed this:

    $('#swData').DataTable( {
        dom: "lfrt",
        ajax: {
            "processing": true,
            "serverSide": true,
            "ajax": "ajax_swData.php"
        },
        "lengthMenu": [ [20, 25, 50, -1], [20, 25, 50, "All"] ],
    } );

to this:

    $('#swData').DataTable( {
        dom: "lfrt",
        ajax: {
            "processing": true,
            "serverSide": true,
            "url": "ajax_swData.php"
        },
        "lengthMenu": [ [20, 25, 50, -1], [20, 25, 50, "All"] ],
    } );

and it seemed to make a difference. I then started getting errors because the first field was blank in some rows. When I added a column at the beginning, id, that is a key and therefore never null, the table rendered without error.

I must be confused about how the difference between the url and ajax options in ajax.

Any pointers to more in-depth reading on the subject or examples greatly appreciated.

N

allan

The first thing to question is if you actually need server-side processing? Do you have ten of thousands or more rows? If so, fine, stay with server-side processing and we'll get it running, but if not, don't bother with it. Just return the entire dataset as JSON and let DataTables sort it out.

Next thing is that you have Editor, so you could just use the Editor libraries as a read only option to get the data from the database - it will automatically detect server-side processing requests as shown here.

Finally, could you run the debugger on your table so I can see what the server is returning.

Allan

gcac

@allan : Thanks so much. You are probably correct, SSP is probably unnecessary for this particular page. Currently, it's 13k rows.

I did get it going with SSP. The change from "ajax": to "url": seemed to be the catalyst that helped me figure it out.

I did have one question regarding returning an entire table (this would be another case where I could also just do my own SQL and not use SSP, but just as an example):

• Is there a way to have SSP return the entire table without specifying the columns? What I am after is to grab a table in its entirety, and display the whole table using the column names as the table headers, and thereby not having to do any HTML <th> tags. I have figured out that if you just include the <thead> and </thead> and leave it empty between them (ie. no <th> tags), you can specify the title in the data part of columns and that works well too. My case would be a table that has columns changing often and I wouldn't want to have to edit the code every time a new field / column was added.

The Editor idea is where I had initially headed with setting it up, but since I hadn't done one with SSP (and this particular module required no edit capability) I decided to give an alternative method a shot.

On returning the dataset and letting DT handle it, basically there I would write my own SQL and just use PHP's json_encode to send the data, right? I know I read in the docs there are some additional fields that must be sent, which I'm sure I can do. I could return the dataset as a big array and that would probably work.

The Debugger is a great tool! I hadn't used it much before recently and it has helped me through a few problems recently.

As always, thanks for your great support and thanks for a great set of tools. Using your stuff is eliminating thousands of lines of code for me and many hours of work.

Nate

allan

This FAQ will be of interest on that topic.

You could use something like SHOW COLUMNS against the SQL server and then derive the column information based on that, allowing it to be fully dynamic.

Allan

pagolina

Have being battling with this for days, my ssp return fine if the record in my table is within 10, but for a table of 500 and above the following line return blank

SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )

I suspected the post parameters but i return it and they where completely ok, pls i need help, what could be responsible?

colin

@pagolina - this is a duplicate comment to your other one here. We'll respond to that other link.