Problem json with joins Datatable 1.10.15

Problem json with joins Datatable 1.10.15

amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0
edited July 2017 in DataTables 1.10

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.

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0
    edited July 2017

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

    @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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0
    edited July 2017

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

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

    PHP version is 7.0.21

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

    Debugger: Failed to load response data.

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

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

    Thanks,
    Allan

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    "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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0
    edited July 2017

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    You need to put the tryCatch before the process statement:

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

    Allan

  • amazeingideasamazeingideas Posts: 11Questions: 1Answers: 0

    Ouch, my bad.

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

    Returns 1.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

This discussion has been closed.