Server Side Processing Results in Nothing

GµårÐïåñ

@allan we previously worked out some of the kinks in the DOM loading (http://datatables.net/forums/discussion/30965/the-datatable-renders-empty-from-jquery-generated-dom-table#latest) but I am facing a new issue which I am hoping is a simple user error.

Once I had it the way I wanted, I decided to move on to making it load dynamically from the database instead. However, its resulting in nothing which is a head scratcher. The test case is at the same location as before (http://res.myguardian.net/NS/) and I was hoping you could take a look at the source and tell me what's wrong with the initialization, if anything.

The code inside the ssp_data.php you see under ajax is as follows:

<?php

// DB table to use
$table = 'ns_prefs';
 
// Table's primary key
$primaryKey = 'pid';
 
// 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
pref_name, pref_value
$columns = array(
    array( 'pref_name' => 'settings', 'dt' => 0 ),
    array( 'pref_value' => 'values', 'dt' => 1 ),
    array( 'pref_comment' => 'comments', 'dt' => 2 ),
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'redacted',
    'pass' => 'redacted',
    'db'   => 'redacted',
    'host' => 'redacted'
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 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( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

and the ssp.class.php is direct from the github page, minus the block that was for DataTables dev testing. I won't repost it here because it serves no purpose.

GµårÐïåñ

@allan, hoping you had a great holiday and the new year has been good. I was wondering if I can follow up and see if you have had a chance to help me with this please?

allan

I've just had a look at the link given above, thanks for providing it. The page doesn't appear to show anything other than a version number and that's because //loadJSON(); is commented out. Can you allow it to execute please.

Allan

GµårÐïåñ

Because as you know, the server side data loading uses the PHP files that won't show in the source code and the data is loaded using the file I posted above, so the only thing that has changed is the source of the data, the rest, I commented out and left there, they are not necessary anymore.

SO unless I am missing something, it should work as configured right now but as you can see, there is NO DATA parsed from the db or returned by the server side file above. I followed the example closely to ensure it would work but something is wrong and I don't know what it might be.

allan

I'm afraid I don't understand. Your page has a loadJSON function which is not currently being executed. It is the function that would insert the <table> element. So at the moment, when the $('#nsp') selector is run, it finds no table, since it isn't in the DOM, and thus nothing happens.

There currently isn't even an Ajax request being made before of that.

Allan

GµårÐïåñ

Ok, you are focusing on things that are not even consistent with the documentation. I left those as an artifact of work in progress. They are NOT relevant.

The simple facts, in accordance with the documentation is that this block, "ajax": "ssp_data.php", is the part that matters and is initiated in the block:

$('#nsp').DataTable( {
                "processing": true,
                "serverSide": true,
                "ajax": "ssp_data.php",
                responsive: true,
                "pageLength": 15,
                "order": [[ 0, "asc" ]],
                "info": true,
                "paging": true,
                "pagingType": "full_numbers",
                "aoColumns": [ 
                  { sWidth: "20%", bSearchable: true, bSortable: true }, 
                  { sWidth: "40%", bSearchable: true, bSortable: true },
                  { sWidth: "40%", bSearchable: true, bSortable: false }
                ],
                buttons: true,
                //"sDom": 'lfBrtip',
                buttons: [
                    { extend: 'copy', text: 'Copy to Clipboard' },
                    { extend: 'excel', text: 'Export to Excel' },
                    { extend: 'csv', text: 'Export as CSV' },
                    { extend: 'pdf', text: 'Print to PDF', orientation: 'landscape', pageSize: 'legal', 
                            customize: function ( doc ) { doc.content[1].table.widths = [ '33.33%', '33.33%', '33.33%' ] }                              
                    },
                    { extend: 'print', text: 'Print', autoPrint: false }
                ]
            });

and the processing is suppose to be done by the ssp_data.php so there is nothing that requires any other function like loadJSON or anything to work.

allan

I agree. However, in the page you linked to there is no $('#nsp') element on the page.

You can check yourself - simply open your browser's console and run: $('#nsp').length. You will get 0.

Therefore there is no table to initialise and thus no Ajax request is made.

Allan

GµårÐïåñ

Ok, I did make a slight overlook, there IS a container, <div id="nsp"></div> but the name was wrong (nsp-container) instead of (nsp) that has been corrected, thank you and sorry I missed it.

However, now it is generating this error in a popup, any ideas? I wish there was a way to load an inline image here, but your forum doesn't support it, so here is a link to the screenshot of the error. ( http://www.screencast.com/t/7Zow33fv0q2 ) in this one it takes you to [ http://datatables.net/manual/tech-notes/2 ] where it explains that I can't use a DIV to load it and must have a TABLE structure.

SO I added a barebone template of a TABLE and gave it the ID nsp and then tried it and this time it gets further but gives the following error instead: ( http://www.screencast.com/t/xtiHuMQWFZr ) in this one it takes you to [ http://datatables.net/manual/tech-notes/1 ] where it seems we need to check the response for errors. I checked and found a typo in the ssp_data.php and so I corrected it and hoped for the best.

Then I found out that now it throws an error saying: ( http://www.screencast.com/t/pUSggjK5YS ) and so I took a look at the error console and it shows the response: {"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'field list'"} but so far I am having trouble finding what its complaining about. Here is the ssp_data.php file now:

<?php

// DB table to use
$table = 'ns_prefs';
 
// Table's primary key
$primaryKey = 'pid';
 
$columns = array(
    array( 'pref_name' => 'settings', 'dt' => 0 ),
    array( 'pref_value' => 'values', 'dt' => 1 ),
    array( 'pref_comment' => 'comments', 'dt' => 2 )
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'X',
    'pass' => 'X',
    'db'   => 'X',
    'host' => 'X'
);
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

The SQL credentials are valid, I just removed them for posting here. So any ideas where we are hitting a block now? BTW, thanks for helping move this along.

allan

Could you modify the SSP class to print out the SELECT statement that is being generated please. You would need to do that around this point.

echo "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit";

Obviously that will cause the returned data to be invalid JSON, but it will at least allow us to see what the SQL being generated is.

Allan

GµårÐïåñ

In theory I get what you are doing but it seems it didn't like it, it produced this:

<br />
<b>Parse error</b>:  syntax error, unexpected 'echo' (T_ECHO) in <b>/ssp.class.php</b> on line <b>227</b><br />

Unless I goofed your code somehow, this is what it looks like for now:

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
/*          "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit" */
echo "SELECT SQL_CALC_FOUND_ROWS <code>".implode("`, `", self::pluck($columns, 'db'))."</code>              FROM <code>$table</code>              $where
             $order
             $limit";
        );

allan

Remove the <code> tags. That are an artifact of the forum. You just basically want to copy the SQL statement that is executed and echo it out.

GµårÐïåñ

I figured as much but I didn't want to mess with what you provided because I wanted you to get what you wanted and I didn't want to interfere with it, but I will make the change.

It now produces this, a json error in the alert window linked earlier, to be expected but we are not getting the echo result we need, did I not correct the code enough?:

<br />
<b>Parse error</b>:  syntax error, unexpected 'echo' (T_ECHO) in <b>ssp.class.php</b> on line <b>227</b><br />

this is how I cleaned up the code, did I overlook something? If so can you make the edit and let me know so I can put it in please.

// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
    echo "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`            
        FROM $table            
        $where
        $order
        $limit";
);

so I made a small tweak and the code becomes:

// Main query to actually get the data
//$data = self::sql_exec( $db, $bindings,
    echo "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`            
        FROM $table            
        $where
        $order
        $limit";
//);

and it produces this outcome:

SELECT SQL_CALC_FOUND_ROWS ``, ``, ``            
                FROM ns_prefs            
                
                ORDER BY `` ASC
                LIMIT 0, 15{"draw":1,"recordsTotal":358,"recordsFiltered":0,"data":[]}

I am assuming that's what we wanted? give you any clues as to what's wrong? It seems to add the <code> artifact which you don't see if you look at it raw, so maybe if I made it this way it would show right:

SELECT SQL_CALC_FOUND_ROWS ,, FROM ns_prefs ORDER BY ASC
LIMIT 0, 15{"draw":1,"recordsTotal":358,"recordsFiltered":0,"data":[]}

allan

SELECT SQL_CALC_FOUND_ROWS '', '', ''

That doesn't look good.

It appears to be caused by the fact that you don't have a db field in the columns arrays like in the examples. Fixing that should address the issue.

Allan

GµårÐïåñ

Yeah I saw that, wanted to share while I figured out why its coming back like it is, was hoping your insights on it might speed things up.

So why do we have no $db? This value is generated by the db function and should be getting it's conn values from the ssp_data.php so where is it going missing?

If you look at how its being called, the code is posted already in this topic history, can you take a look and see if somehow its not properly interacting with ssp.class.php and if so, how to resolve it?

allan

You have no db parameter because you haven't specified one:

array( 'pref_name' => 'settings', 'dt' => 0 ),

It is not the $db variable that is the issue, but you need to a db parameter in the array - like in the examples.

Allan

GµårÐïåñ

Yes my friend, I came to that conclusion while I was reviewing the code further. I actually managed to get it to work already. Take a look and thank you again for all your help. Now next step would be to integrate editing generated table fields on the fly.