Problem json with joins Datatable 1.10.15

amazeingideas

Hello,
when I active serverSide mode, iI get this error:
DataTables warning: table id=productos-table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

My editor and table code:

 editor = new $.fn.dataTable.Editor( {
                "ajax": "../php/mod_productos.php",
                "table": "#productos-table",
                "fields": [{
                    "label": "Titulo en español:",
                    "name": "dt_productos.titulo_es_ES"
                }, {
                    "label": "Titulo en inglés:",
                    "name": "dt_productos.titulo_en_US"
                }, {
                    "label": "Tienda:",
                    "name": "dt_productos.fk_tienda",
                    "dataProb": "1",
                    "type": "select"
                }, {
                    "label": "Sinonimos:",
                    "name": "dt_rel_productos_sinonimos.fk_sino",
                    "type": "select"
                }, {
                    "label": "Subsubcategoria:",
                    "name": "dt_rel_productos_subsubcategorias.fk_subsubcategoria",
                    "type": "select"
                }, {
                    "label": "Tags:",
                    "name": "dt_productos.tags",
                    "type": "textarea"
                }, {
                    "label": "Prioridad:",
                    "name": "dt_productos.prioridad"
                }, {
                    "label": "Precio:",
                    "name": "dt_productos.precio"
                }, {
                    "label": "Precio anterior:",
                    "name": "dt_productos.precio_ant"
                }, {
                    "label": "Url:",
                    "name": "dt_productos.url"
                }, {
                    "label": "Activo:",
                    "name": "dt_productos.activo",
                    "type": "select",
                    "ipOpts": [{ "label": "Si", "value": "1" },
                        { "label": "No", "value": "0"}]
                }, {
                    label: "Imagen:",
                    name: "dt_files[].id",
                    type: "uploadMany",
                    display: function ( fileId, counter ) {
                        return '<img src="'+table.file( 'dt_files', fileId ).web_path+'"/>';
                    },
                    noImageText: "No images"
                }
                ]
            } );

            $('#productos-table').DataTable( {
                dom: "Bfrtip",
                ajax: {
                    url: "../php/mod_productos.php",
                    type: "POST"
                },
                serverSide: true,
                columns: [
                    {data: "dt_productos.id_producto"},
                    {"data": "dt_productos.titulo_es_ES"},
                    {"data": "dt_productos.titulo_en_US"},
                    {"data": "dt_tiendas.titulo"},
                    {"data": "dt_sinonimos.titulo_es_ES",
                        defaultContent: "Sin sinonimo",
                        editField: "dt_rel_productos_sinonimos.fk_sino"
                    },
                    {"data": "dt_categorias_terciarias.titulo_es_ES",
                        defaultContent: "Sin subsubcategoria",
                        editField: "dt_rel_productos_subsubcategorias.fk_subsubcategoria"
                    },
                    {"data": "dt_productos.prioridad", editField: "dt_productos.prioridad"},
                    {"data": "dt_productos.precio", render: $.fn.dataTable.render.number(',', '.', 2, '', '&euro;')},
                    {"data": "dt_productos.precio_ant", render: $.fn.dataTable.render.number(',', '.', 2, '', '&euro;')},
                    {"data": "dt_productos.tags"},
                    {"data": "dt_productos.activo"},
                    {
                        data: "dt_files",
                        render: function ( file_id ) {
                            if (file_id.length>0) {
                                return file_id[0].id ? '<img src="'+table.file( 'dt_files', file_id[0].id ).web_path+'" style="width: 100px;"/>' :
                                    'No tiene imagen';
                            }else{
                                return null;
                            }
                        },
                        defaultContent: "No tiene imagen",
                        editField: "dt_files[].id"
                    }

                ],
                order: [1, 'desc'],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: [
                    {extend: "create", editor: editor},
                    {extend: "edit", editor: editor},
                    {extend: "remove", editor: editor}
                ]
            } );

My PHP code:

<?php



// DataTables PHP library
include( "../../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dt_productos','id_producto' )
    ->fields(
        Field::inst( 'dt_productos.id_producto' ),
        Field::inst( 'dt_productos.titulo_es_ES' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'dt_productos.titulo_en_US' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'dt_productos.prioridad' ),
        Field::inst( 'dt_productos.fk_tienda' )
            ->options( 'dt_tiendas', 'id_tienda', 'titulo' ),
        Field::inst( 'dt_productos.precio' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
        Field::inst( 'dt_productos.precio_ant' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
        Field::inst( 'dt_productos.activo' )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'dt_tiendas.titulo' ),
        Field::inst( 'dt_productos.tags' ),
        Field::inst( 'dt_productos.url' ),
        Field::inst( 'dt_rel_productos_sinonimos.fk_sino' )
            ->options( 'dt_sinonimos', 'id_sino', 'titulo_es_ES' ),
        Field::inst( 'dt_sinonimos.titulo_es_ES' ),
        Field::inst( 'dt_rel_productos_subsubcategorias.fk_subsubcategoria' )
            ->options( 'dt_categorias_terciarias', 'id_subsubcat', 'titulo_es_ES' ),
        Field::inst( 'dt_categorias_terciarias.titulo_es_ES' )
    )
    ->join(
        Mjoin::inst( 'dt_files' )
            ->link( 'dt_productos.id_producto', 'dt_imagenes_productos.fk_producto' )
            ->link( 'dt_files.id', 'dt_imagenes_productos.fk_file' )
            ->fields(
                Field::inst( 'id' )
                    ->upload(Upload::inst($_SERVER['DOCUMENT_ROOT'].'/photos/products/__ID__.__EXTN__')
                        ->db( 'dt_files', 'id', array(
                            'filename'    => Upload::DB_FILE_NAME,
                            'filesize'    => Upload::DB_FILE_SIZE,
                            'web_path'    => Upload::DB_WEB_PATH,
                            'system_path' => Upload::DB_SYSTEM_PATH
                        ) )
                        ->dbClean( function ( $data ) {
                            // Remove the files from the file system
                            for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
                                unlink( $data[$i]['system_path'] );
                            }

                            // Have Editor remove the rows from the database
                            return true;
                        } )
                        ->validator( function ( $file ) {
                            return$file['size'] >= 100000 ?
                                "Files must be smaller than 100K" :
                                null;
                        } )
                        ->allowedExtensions( [ 'png', 'jpg' ], "Please upload an image" )
                    )
            )
    )

    ->leftJoin( 'dt_rel_productos_sinonimos', 'dt_productos.id_producto', '=', 'dt_rel_productos_sinonimos.fk_producto' )
    ->leftJoin( 'dt_sinonimos','dt_rel_productos_sinonimos.fk_sino', '=', 'dt_sinonimos.id_sino' )
    ->leftJoin( 'dt_rel_productos_subsubcategorias', 'dt_productos.id_producto', '=', 'dt_rel_productos_subsubcategorias.fk_producto' )
    ->leftJoin( 'dt_categorias_terciarias','dt_rel_productos_subsubcategorias.fk_subsubcategoria', '=', 'dt_categorias_terciarias.id_subsubcat' )
    ->leftJoin( 'dt_tiendas','dt_productos.fk_tienda', '=', 'dt_tiendas.id_tienda' )
    ->where( 'dt_productos.activo', 1 , '=')


    ->on('postCreate', function ($editor, $id, $values, $row){
        //thumbnails($db, $values);
        /*$urlencrip = generateRandomString() . ($id * 5);
        //resize(340, 440, $uploadPath, $fi);
        $editor->db()->insert( 'dt_rel_productos_urls', array(
            'fk_producto'   => $id,
            'url' => $urlencrip
        ));*/

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

Thank you.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

tangerine

Did you follow the diagnostics at the /tn/1 link to see what the server is returning?

amazeingideas

yes, response show: "This request has no response data available"

allan

That's the issue as far as the client-side is concerned in that case - no data is not valid JSON.

The question is, why is the server not returning any data. To answer that, have a look in your server's error logs - it should give information as to what error occurred.

Allan

amazeingideas

Hello Allan,
i found on access_log:

"GET /editor/examples/php/test.php HTTP/1.0" 200 8297 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36"
"POST /editor/examples/php/mod_productos.php HTTP/1.0" 200 181 "/editor/examples/php/test.php" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36"

error_log doesn't have nothing about Datatables

tangerine

Are you looking at the correct error log (probably Apache)?

allan

Worth checking that you've got all PHP logging enabled as well.

Try putting this at the very top of your PHP file:

error_reporting(E_ALL);
ini_set('display_errors', '1');

Allan

amazeingideas

@tangerine yes, I checked it from plesk logs/error_log
@allan I did it and nothing happen.

200 GET /editor/examples/php/test.php HTTP/1.0
200 POST /editor/examples/php/mod_productos.php HTTP/1.0

allan

The next step is to put: echo 1; at the top of the file and just make sure that gets sent back.

Its also possible that the error log is in a domain specific file, but really it depends upon the server and its configuration.

Allan

amazeingideas

@allan, file returns 1.
if I force a PHP error it shows:
AH01071: Got error 'PHP message: PHP Parse error: syntax error, unexpected 'include' (T_INCLUDE) in /editor/examples/php/mod_productos.php on line 9\n', referer: /editor/examples/php/test.php

Problem is I never get any error, just an empty file.

allan

What PHP version are you using?

You might need to move the echo statement around until you find where the response is cutting out.

Allan

amazeingideas

I put echo 1 in last line on PHP file and still returns 1.

PHP version is 7.0.21

allan

Very very odd.

Let's try replacing:

Editor::inst( $db, 'dt_productos','id_producto' )
    ....
    ->process( $_POST )
    ->json();

with:

$editor = Editor::inst( $db, 'dt_productos','id_producto' )
    ....
    ->process( $_POST );

var_dump( $editor->data() );

and then could you show me what the server is returning?

Thanks,
Allan

amazeingideas

Debugger: Failed to load response data.

But i think load time is correct.
Waiting (TTFB) 524.18ms
Content Download 2.01s

allan

Could you use the instructions here to show me the data that is being returned by the server?

Thanks,
Allan

amazeingideas

I checked if I open on new tab the file, it shows all data.

allan

"Failed to load response data" - but that doesn't happen if you put the echo 1; in?

10MB is a huge about of data to load into the browser - but that alone shouldn't be causing the issue (unless your PHP server is running out of memory, but then that should be shown in its error log).

Allan

amazeingideas

When I put the echo 1 on start or end , file returns 1.

memory limit is 128MB

Allan, i checked on Firefox, it displays data but it returns the same error

allan

So if you have:

<?php
 
 
 
// DataTables PHP library
include( "../../php/DataTables.php" );
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dt_productos','id_producto' )
    ->fields(
        Field::inst( 'dt_productos.id_producto' ),
        Field::inst( 'dt_productos.titulo_es_ES' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'dt_productos.titulo_en_US' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'dt_productos.prioridad' ),
        Field::inst( 'dt_productos.fk_tienda' )
            ->options( 'dt_tiendas', 'id_tienda', 'titulo' ),
        Field::inst( 'dt_productos.precio' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
        Field::inst( 'dt_productos.precio_ant' )
            ->validator( 'Validate::numeric' )
            ->setFormatter( 'Format::ifEmpty', null ),
        Field::inst( 'dt_productos.activo' )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'dt_tiendas.titulo' ),
        Field::inst( 'dt_productos.tags' ),
        Field::inst( 'dt_productos.url' ),
        Field::inst( 'dt_rel_productos_sinonimos.fk_sino' )
            ->options( 'dt_sinonimos', 'id_sino', 'titulo_es_ES' ),
        Field::inst( 'dt_sinonimos.titulo_es_ES' ),
        Field::inst( 'dt_rel_productos_subsubcategorias.fk_subsubcategoria' )
            ->options( 'dt_categorias_terciarias', 'id_subsubcat', 'titulo_es_ES' ),
        Field::inst( 'dt_categorias_terciarias.titulo_es_ES' )
    )
    ->join(
        Mjoin::inst( 'dt_files' )
            ->link( 'dt_productos.id_producto', 'dt_imagenes_productos.fk_producto' )
            ->link( 'dt_files.id', 'dt_imagenes_productos.fk_file' )
            ->fields(
                Field::inst( 'id' )
                    ->upload(Upload::inst($_SERVER['DOCUMENT_ROOT'].'/photos/products/__ID__.__EXTN__')
                        ->db( 'dt_files', 'id', array(
                            'filename'    => Upload::DB_FILE_NAME,
                            'filesize'    => Upload::DB_FILE_SIZE,
                            'web_path'    => Upload::DB_WEB_PATH,
                            'system_path' => Upload::DB_SYSTEM_PATH
                        ) )
                        ->dbClean( function ( $data ) {
                            // Remove the files from the file system
                            for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
                                unlink( $data[$i]['system_path'] );
                            }
 
                            // Have Editor remove the rows from the database
                            return true;
                        } )
                        ->validator( function ( $file ) {
                            return$file['size'] >= 100000 ?
                                "Files must be smaller than 100K" :
                                null;
                        } )
                        ->allowedExtensions( [ 'png', 'jpg' ], "Please upload an image" )
                    )
            )
    )
 
    ->leftJoin( 'dt_rel_productos_sinonimos', 'dt_productos.id_producto', '=', 'dt_rel_productos_sinonimos.fk_producto' )
    ->leftJoin( 'dt_sinonimos','dt_rel_productos_sinonimos.fk_sino', '=', 'dt_sinonimos.id_sino' )
    ->leftJoin( 'dt_rel_productos_subsubcategorias', 'dt_productos.id_producto', '=', 'dt_rel_productos_subsubcategorias.fk_producto' )
    ->leftJoin( 'dt_categorias_terciarias','dt_rel_productos_subsubcategorias.fk_subsubcategoria', '=', 'dt_categorias_terciarias.id_subsubcat' )
    ->leftJoin( 'dt_tiendas','dt_productos.fk_tienda', '=', 'dt_tiendas.id_tienda' )
    ->where( 'dt_productos.activo', 1 , '=')
 
 
    ->on('postCreate', function ($editor, $id, $values, $row){
        //thumbnails($db, $values);
        /*$urlencrip = generateRandomString() . ($id * 5);
        //resize(340, 440, $uploadPath, $fi);
        $editor->db()->insert( 'dt_rel_productos_urls', array(
            'fk_producto'   => $id,
            'url' => $urlencrip
        ));*/
 
    })
    ->process( $_POST )
    ->json();

echo 1;

it shows 1 and only 1?!

I honestly don't know what would cause that I'm afraid. There is no way that it should be able to get past the json statement without outputing some JSON or throwing an error.

Perhaps try adding ->tryCatch( false ) immediately before the ->process() method call.

Allan

amazeingideas

I'm gonna cry.

->process( $_POST )
    ->tryCatch( false )
    ->json();

Nothing

->process( $_POST )
    ->tryCatch( false )
    ->json();

echo 1;

Returns 1

->process( $_POST )
    ->tryCatch( false );

echo 1;

Returns 1

allan

You need to put the tryCatch before the process statement:

    ->tryCatch( false )
    ->process( $_POST )
    ->json();
 
echo 1;

Allan

amazeingideas

Ouch, my bad.

->tryCatch( false )
    ->process( $_POST )

    ->json();

echo 1;

Returns 1.

allan

I must confess, that makes no sense . I can't understand what would cause that.

Are you able to send me a PM with SFTP details for the server and a web login? I'm going to have to debug it directly. I can't understand how the json aspect would give nothing and then the echo would show. I honestly can't think of any way that could possibly be!

Allan