error when using serverside and searching, help please

javismiles

good day, I explain you my issue to see if you can help me im using one of the latest versions of your editor, 1.6.1

All is working perfect without server side, but I want server side.
So I activate this

    "bServerSide": true,
    "sServerMethod": "POST"

If i set it to false, all works perfect, but i want and need to use bserverside because my database is too large. So i set

    "bServerSide": true,
    "sServerMethod": "POST"

And then all works fine in terms of reading the table data, it displays all fine. So retrieving the data initially and displaying it works great, but it i try to search for anything then i get a json related error (only when doing search, pagination and listing works ok, and only with serverside activated, without it all works well including search)

I got into the debug mode and i find something like this there in the ajax call

"Fatal error: Call to private method DataTables\Editor::_ssp_field() from context '' in xxxxxxxxx/Editor/Editor.php on line 1444"

could you please help me make this work

as i say, listing and changing pages seems to work, what breaks is searching
which is very important for me having sooo many records

thank you

javismiles

I now upgraded to the very latest 1.6.5,
still getting an error but a different one, and again only when searching (listing and pagination are all ok):
"DataTables warning: table id=users - Unknown field: (index 0)"

{"fieldErrors":[],"error":"Unknown field: (index 0)","data":[],"ipOpts":[],"cancelled":[]}

I don't get it, it all works perfect without bserverside, it only fails with bserverside on, but also only searching fails, listing and pagination work all perfect, editing works fine as well, its only searching that fails

allan

Hi,

Rather than using the legacy bServerSide and sServerMethod options I would suggest you use:

        ajax: {
            url: "../php/staff.php",
            type: "POST"
        },
        serverSide: true,

as shown in this example.

The error relating to the "Unknown field" is what happens when server-side processing is enabled, but the column information sent from the client-side doesn't match up with that on the server-side.

Can you show me both your DataTables configuration and your PHP code?

Thanks,
Allan

javismiles

Thank you Allan.

Got you, but realize that no problem happens when listing, editing, deleting or changing pages, all works perfect. The error happens only and specifically when searching.
How could there be a mismatch when searching and not when listing, etc?

In my server.php I declare the tables like this

$dbJson='['.
'{ "table":"invalid","id":"id","tid":"invalid","fields":[{"t":"string","i":"id"},{"t":"string","i":"data"},{"t":"string","i":"date"},{"t":"string","i":"ip"},{"t":"string","i":"referrer"},{"t":"string","i":"uri"},{"t":"string","i":"browser"},{"t":"string","i":"platform"},{"t":"string","i":"version"},{"t":"string","i":"mobile"}]},'.
etc, etc it continues with all the other tables.

and then do this: (etype is a parameter i use to send the table name, as I use one server.php for multiple tables and it works great)

$dbArray=json_decode($dbJson, true);
$etype=$_REQUEST['etype'];
$GLOBALS['etype'] = $etype;

$findit=array_keys(array_filter($dbArray, function($item){return $item['table'] === $GLOBALS['etype'];}));
$pos=$findit[0];
$fields=$dbArray[$pos]['fields'];
$id=$dbArray[$pos]['id'];
$tid=$dbArray[$pos]['tid'];

$keys = array_keys($fields);
$editor = Editor::inst( $db, $tid,$id );
for($i = 0; $i < count($fields); $i++) {$editor->fields(Field::inst($fields[$i]['i']));}

$editor->process( $_POST );
$editor->json();

--- again the above works 100% perfect for listing, deleting, editing etc . It only fails for searching, that's what puzzles me?

javismiles

as you can see, both in javascript and php I am adding code to use 1 single file to deal with all the tables, and it works perfect without serverside and it works perfect with serverside except 1 single thing, except the searching functionality, only that , but that's crucial because my users table has 130000 records and I need to search!

javismiles

now will show you configuration

javismiles

I changed to use your suggestion but same error,
this is the configuration:

$.map(dbArray, function(obj, index) {

  thetable=obj.table;
  var fieldobj = []; // main object
  var colobj=[];

  colobj[0]={data: null,defaultContent: '',className: 'select-checkbox',orderable: false};

  toadd='<table id="'+obj.table+'" class="display tdb hide" cellspacing="0" width="100%"><thead><tr><th></th>';
  toadd+=$.map(obj.fields, function(obj, index) {return '<th>'+obj.i+'</th>';}).join("");
  toadd+='</tr></thead></table>';$("#tables").append(toadd);

  $.map(obj.fields, function(obj, index) {
       fieldobj[index]  = { 'label':obj.i+":", 'name':obj.i };        
       colobj[index+1]  = { 'data':obj.i };           
    });

    eArray[index] = new $.fn.dataTable.Editor( {
    "ajax": {"url": "xxxxxxxxxx/server.php","type": "POST","data": {"etype": obj.table}},
    table: "#"+obj.table,
    fields: fieldobj});

    $('#'+obj.table).on( 'click', 'tbody td:not(:first-child)', function (e) {
    eArray[index].inline( this, {onBlur: 'submit'})});

    $('#'+obj.table).DataTable( {
    //"sDom": "Tfprtip",
    //"oSearch": {"sSearch": searchinit},
    //"aaSorting": [[0,'username']],
    "iDisplayLength": 15,
    //"bServerSide": true,
    //"sServerMethod": "POST",  
    serverSide: true,
    "Processing": true,
    responsive: true,   
    "sPaginationType": "full_numbers",      
    "aLengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]],       
    dom: "Bfrtip",
    "ajax": {
        "url": "xxxxxxxxxxxxx/server.php",
        "data": {"etype": thetable},
        "type": "post"
        },
    columns: colobj,
    order: [ 1, 'desc' ],
    select: {
        style:    'os',
        selector: 'td:first-child'
    },
    buttons: [
        { extend: "create", editor: eArray[index] },
        { extend: "edit",   editor: eArray[index] },
        { extend: "remove", editor: eArray[index] }
    ]
} );

$("#"+obj.table+"_wrapper").hide();

});

javismiles

that was the javascript configuration and here my entire php: (etype is a parameter I send that specifies which table I'm working on)

include( "res/php/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

$dbJson='['.
'{ "table":"invalid","id":"id","tid":"invalid","fields":[{"t":"string","i":"id"},{"t":"string","i":"data"},{"t":"string","i":"date"},{"t":"string","i":"ip"},{"t":"string","i":"referrer"},{"t":"string","i":"uri"},{"t":"string","i":"browser"},{"t":"string","i":"platform"},{"t":"string","i":"version"},{"t":"string","i":"mobile"}]},'.etc, etc, etc, etc, etc with other tables, I have all the tables in this variable, all of them.
']';

$dbArray=json_decode($dbJson, true);

$etype=$_REQUEST['etype'];
$GLOBALS['etype'] = $etype;

$findit=array_keys(array_filter($dbArray, function($item){return $item['table'] === $GLOBALS['etype'];}));
$pos=$findit[0];
$fields=$dbArray[$pos]['fields'];
$id=$dbArray[$pos]['id'];
$tid=$dbArray[$pos]['tid'];

$keys = array_keys($fields);

$editor = Editor::inst( $db, $tid,$id );

for($i = 0; $i < count($fields); $i++) {$editor->fields(Field::inst($fields[$i]['i']));}

$editor->process( $_POST )
$editor->json();

allan

Add searchable: false to your object on line 5. You already have orderable: false, the same applies to search.

Allan

javismiles

y las tablas estan definidas exactamente igual en el javascript y en el php (con diferente sintaxis claro pero identicas)

En javascript:

dbJson='['+

'{ "table":"invalid","id":"id","tid":"invalid","fields":[{"t":"string","i":"id"},{"t":"string","i":"data"},{"t":"string","i":"date"},{"t":"string","i":"ip"},{"t":"string","i":"referrer"},{"t":"string","i":"uri"},{"t":"string","i":"browser"},{"t":"string","i":"platform"},{"t":"string","i":"version"},{"t":"string","i":"mobile"}]},'+ etc etc the rest of the tables 

']';

En PHP:

$dbJson='['.

'{ "table":"invalid","id":"id","tid":"invalid","fields":[{"t":"string","i":"id"},{"t":"string","i":"data"},{"t":"string","i":"date"},{"t":"string","i":"ip"},{"t":"string","i":"referrer"},{"t":"string","i":"uri"},{"t":"string","i":"browser"},{"t":"string","i":"platform"},{"t":"string","i":"version"},{"t":"string","i":"mobile"}]},'.  etc etc the rest of the tables

']';

So as they are defined identical in both PHP and javascript I don't get why that error. Which again happens only when searching, no errors when listing, retrieving initially, editing, deleting, pagination

javismiles

wow, like wow,
I added searchable: false,
and now it seems to work!

thank you Allan, could you explain me why did it need to have
searchable: false,
in order to work?

thank you

allan

Because the column is a client-side generated column. The server-side has no knowledge of that column (note how you explicitly set data:null for it). So searching for data on that column, on the server-side would result in an error.

Allan

javismiles

btw how can I delete the debug data from a
https://debug.datatables.net link?

thanks a lot Allan

javismiles

thank you for the explanation Allan, got it now, glad its now all sorted out, thanks very much again,

to delete the debug data at https://debug.datatables.net , how can I do it?

all best and thank u

allan

What was the debug code and I'll delete it directly?

Allan

javismiles

thank you Allan, just emailed them (I guess if i put them here anybody can go to those pages right?)
thanks a lot for your help have a great day great forum