Combobox dependents

jalape

Hello,
I am trying a functionality that I do not know if it is possible in Datatable Editor.
I have two fields that are edited with combobox: titulo_id and capitulo_id of the tb_tutorial table.
The editor is a template: '#customForm'

<div id="customForm">
    <fieldset>
        <editor-field name="tb_tutorial.titulo_id"></editor-field>
        <editor-field name="tb_tutorial.capitulo_id"></editor-field>
    </fieldset>
</div>

The data of the list that appears in the combobox:

    Field::inst( 'tb_tutorial.titulo_id' )
    ->options( Options::inst()
        ->table( 'tb_palabra' )
        ->value( 'id' )
        ->label( 'palabra' )
    ),
    Field::inst( 'tb_palabra.palabra' ),

    Field::inst( 'tb_tutorial.capitulo_id' )
    ->options( Options::inst()
        ->table( 'tb_capitulo' )
        ->value( 'id' )
        ->label( 'capitulo' )
    ),
    Field::inst( 'tb_capitulo.capitulo' ),
->leftJoin( 'tb_palabra', 'tb_palabra.id', '=', 'tb_tutorial.titulo_id' )
->leftJoin( 'tb_capitulo', 'tb_capitulo.id', '=', 'tb_tutorial.capitulo_id' )

THE QUESTION: Is it possible to make the combobox list for 'tb_tutorial.capitulo_id' filter based on the id of the previous combobox: 'tb_tutorial.titulo_id'.
Thank you

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

allan

Yes this is possible. We call it "cascading select". There is a detailed blog post about how to do it here.

Allan

jalape

Thanks Allan, it seems that is what I am looking for.
PS: thanks to Colin for editing the text and giving it a better format.

jalape

Do not adapt the example "Cascade lists in the editor" to my project with Datatable Php. Looked at the editor examples folder: examples / api but it is not included. Is it possible to have the complete example to study its operation?

colin

Hi @jalape ,

Yep, it's only on the forum as a blog post. You could look at the page source for that post, the client side code is all there for the example. The server-side code is explained in the post itself,

Cheers,

Colin

jalape

I have the two necessary elements, but I can not get it to work.
datatable_tutirial.js

    fields: [
        {
            label: "Capítulo:",
            name:  "tb_tutorial.capitulo_id",
            type: "select2",
            opts: {
                "placeholder": "Selecciona un capítulo",
                "allowClear": true
            }
        },
        {
            label: "Subcapítulo:",
            name:  "tb_tutorial.subcapitulo_id",
            type: "select2",
            opts: {
                "placeholder": "Selecciona un subcapítulo",
                "allowClear": true
            }
        }                       
        ]
    } );
    
    editor.dependent( 'tb_tutorial.capitulo_id', 'php/p_datos/datos_tutorial2.php' );

datos_tutorial2.php

    <?php
    include_once( $_SERVER['DOCUMENT_ROOT']."../DataTables.php" );
    
    
     
    $sub = $db
        ->select( 'tb_tutorial.subcapitulo_id', ['id as value', 'name as label'], ['tb_tutorial.capitulo_id' => $_REQUEST['values']['tb_tutorial.capitulo_id']] )
        ->fetchAll();
     
    echo json_encode( [
        'options' => [
            'tb_tutorial.subcapitulo_id' => $sub
        ]
    ] );

allan

Before trying to get the cascade to work with Select2, try it with just select (just to reduce the surface area for where things could be going wrong).

With that done, what happens when you run your code? Do you get any errors? It would be useful to see the page you are working on if you can link to it please.

Allan

jalape

It does not generate any errors, but the editor remains in a constant activation loop (see the image).
I am still working in local, but I have reduced the files of the app and the tables of the database as an example. You have to add the folders css and js in the folder:
cascading\plugin\editor (version Editor-PHP-1.9.0) to make it fully functional.
javierlasen.es/archivos/cascading.zip

allan

Are you able to give me a link to your page please? That should only happen if:

1. The server returns an error, or
2. If you use a callback function for dependent() and don't return a value (you aren't using it with a callback, so its not this).

Allan

jalape

http://www.javierlasen.es/recursos/login/admin_tutorial.php

datatable_tutorial.js

    var editor;
    $(document).ready(function() {
    
        editor = new $.fn.dataTable.Editor( {
            ajax: "php/p_datos/datos_tutorial.php",
            table: "#tutorial_tbl",
            template: '#customForm', //con esto controlamos el diseño del cuadro editor (sin contar los botones)
            
            fields: [
                {
                    label: "Capítulo:",
                    name:  "tb_tutorial.capitulo_id",
                    type: "select2",
                    opts: {
                        "placeholder": "Selecciona un capítulo",
                        "allowClear": true
                    }
                },
                {
                    label: "Subcapítulo:",
                    name:  "tb_tutorial.subcapitulo_id",
                    type: "select2",
                    opts: {
                        "placeholder": "Selecciona un subcapítulo",
                        "allowClear": true
                    }
        }                                                               
            ]
        } );
        editor.dependent( 'tb_tutorial.capitulo_id', 'php/p_datos/datos_tutorial2.php' );
        var table = $('#tutorial_tbl').DataTable( {
            dom: "Bfrtlip",
            ajax: {
                url: "php/p_datos/datos_tutorial.php",
                type: 'POST'
            },
            columns: [
    
                { data: "tb_capitulo.capitulo" },
                { data: "tb_capitulo_sub.subcapitulo" },    
    
            ],
    
            buttons: [
            {
                    extend:     'print',
                    text:       '<i class="fas fa-print"></i>',
                    exportOptions: {
                            columns: ':visible'
                    },                
                    titleAttr:  'Imprimir'
                }, 
                {  
                    extend:     'colvis',
                    text:       '<i class="fas fa-columns"></i>',
                    titleAttr:  'Mostrar columnas'
                },
                {
                    text:       '<i class="fa fa-search-plus"></i>',
                    titleAttr:  'Buscar avanzado',
                    action: function ( e, dt, node, config ) {
                        $('#busquedaAvanzada').slideToggle("slow");
                    }
                },
                {
                    extend:     'create', 
                    editor: editor,
                    text:       '<i class="fa fa-plus"></i>',
                    titleAttr:  'Nuevo'
                },
                {
                    extend:     'edit', 
                    editor: editor,
                    text:       '<i class="fa fa-pencil"></i>',
                    titleAttr:  'Editar'
                },
                {
                    extend:     'remove', 
                    editor: editor,
                    text:       '<i class="fa fa-times"></i>',
                    titleAttr:  'Borrar'
                }                     
            ]           
        } );
    } );

datos_tutorial.php

    <?php
    
    include( "../DataTables.php" );
    
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    Editor::inst( $db, 'tb_tutorial' )
    ->field(
        Field::inst( 'tb_tutorial.titulo_id' )
        ->options( Options::inst()
            ->table( 'tb_palabra' )
            ->value( 'id' )
            ->label( 'palabra' )
        ),
        Field::inst( 'tb_palabra.palabra' ),
        Field::inst( 'tb_tutorial.capitulo_id' )
        ->options( Options::inst()
            ->table( 'tb_capitulo' )
            ->value( 'id' )
            ->label( 'capitulo' )
        ),
        Field::inst( 'tb_capitulo.capitulo' ),
        Field::inst( 'tb_tutorial.subcapitulo_id' )
        ->options( Options::inst()
            ->table( 'tb_capitulo_sub' )
            ->value( 'id' )
            ->label( 'subcapitulo' )
        ),
        Field::inst( 'tb_capitulo_sub.subcapitulo' ),   
        Field::inst( 'tb_tutorial.disco_id' )
        ->options( Options::inst()
            ->table( 'tb_disco' )
            ->value( 'id' )
            ->label( 'disco' )
        ),
        Field::inst( 'tb_disco.disco' ),
        Field::inst( 'tb_tutorial.enunciado' ),
        Field::inst( 'tb_tutorial.href' ),
        Field::inst( 'tb_tutorial.notas_tutorial' ),
        Field::inst( 'tb_tutorial.version' ),
        Field::inst( 'tb_tutorial.class' ),
        Field::inst( 'tb_tutorial.tipo' ),
        Field::inst( 'tb_tutorial.data_lcl_poster' ),
        Field::inst( 'tb_tutorial.style' )          
    )
    ->leftJoin( 'tb_palabra', 'tb_palabra.id', '=', 'tb_tutorial.titulo_id' )
    ->leftJoin( 'tb_capitulo', 'tb_capitulo.id', '=', 'tb_tutorial.capitulo_id' )
    ->leftJoin( 'tb_capitulo_sub', 'tb_capitulo_sub.id', '=', 'tb_tutorial.subcapitulo_id' )
    ->leftJoin( 'tb_disco', 'tb_disco.id', '=', 'tb_tutorial.disco_id' )
    ->process($_POST)
    ->json();

datos_tutorial2.php

    <?php
    include_once( $_SERVER['DOCUMENT_ROOT']."../DataTables.php" );
     
     
      
    $sub = $db
        ->select( 'tb_tutorial.subcapitulo_id', ['id as value', 'name as label'], ['tb_tutorial.capitulo_id' => $_REQUEST['values']['tb_tutorial.capitulo_id']] )
        ->fetchAll();
      
    echo json_encode( [
        'options' => [
            'tb_tutorial.subcapitulo_id' => $sub
        ]
    ] );

colin

Hi @jalape ,

It does not generate any errors, but the editor remains in a constant activation loop (see the image).

I'm seeing an error on that page. When you edit an item, the console shows this error:

http://www.javierlasen.es/recursos/login/php/p_datos/datos_tutorial2.php 500 (Internal Server Error)

This is the reason why the activation loop is constant, it's waiting for dependent() to get that successful response.

To debug, it would be worth checking the server script to see why it's failing.

Cheers,

Colin

allan

Add this at the top of your PHP file (inside the <?php of course) to have it dump any errors:

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

Allan

jalape

Thanks Colin and Allan for your time:
Colin: The error in the console is caused by a badly deactivated line, it is solved and it no longer gives an error, but it is not related to the problem with the use of the dependent()
Allan: I have added the error line:

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

At the beginning of the PHP file, both in "datos_tutorial.php" and in "datos_tutorial2.php", but nothing happens.
You can see it in the link:
http://www.javierlasen.es/recursos/login/admin_tutorial.php

colin

Hi @jalape ,

The 500 error is still being returned by the server - if you look at the network tab, you'll see the request is failing. I do think this is related to the issue - dependent() is calling that script (php/p_datos/datos_tutorial2.php) which is failing.

Cheers,

Colin

jalape

Thanks Colin,
Effectively the file data_tutorial2.php gives an error of type 500. I think it is related to the server, since localhost does not give that error. In any case it is not the cause of the malfunction of dependent (). In local does not work either.
It would be possible to have the files on the page: “Cascading lists in Editor”. https://datatables.net/blog/2017-09-01. Comparing the two structures, it would be easier to check where the error is.

allan

Sure - this is the code used:

<?php

include_once( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );

$sql_details['db'] = 'datatables_ajax';
include_once( $_SERVER['DOCUMENT_ROOT']."/editor/php/DataTables.php" );

$countries = $db
    ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
    ->fetchAll();

echo json_encode( [
    'options' => [
        'country' => $countries
    ]
] );

Odd that adding the two lines I suggested didn't show anything on the output. What version of PHP is your server running? Also, have you checked the server's error logs to see if there is anything reported there?

Allan

jalape

I understand that datatables/mysql.php is the connection to the Database. In my case it is called conexion.php and it has this structure:

conexion.php

    <?php
        function conexion(){ 
            global $DB_HOST; 
            global $DB_USER; 
            global $DB_PASSWORD; 
            global $DB_NAME; 
            $DB_HOST = 'localhost';
            $DB_USER = 'root';
            $DB_PASSWORD = '';
            $DB_NAME = 'qaan327';
            $mysqli = @new mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_NAME); 
            if (mysqli_connect_errno()) {
                printf(error_db_connect());
                exit();
            }
    
            $mysqli -> query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
    
            return $mysqli;
        }
    ?>

$sql_details I guess it's the variable of the connection, in my case $mysqli.
I also have doubts with data_ajax (line 5) and with the variable $ db (line 8) that is not defined.
It still does not work.

How is the file that contains the table and configure the editor box of the example?

jalape

I can not make it work. Would it be possible to have the example files cascaded in the Editor?
javierlasen@hotmail.com
Thank you.

allan

$sql_details['db'] = 'datatables_ajax';

datatables_ajax is the table name to read the data from.

$ db (line 8) that is not defined.

That's the database resource. In your case that's the return from your conexion function.

Allan

jalape

Thank you Allan,
With: $sub['db'] = 'tb_tutorial'; it still does not work.
Any change in "datos_tutorial2.php" does not change anything, it can be a problem with the call:

editor.dependent( 'tb_tutorial.capitulo_id', 'php/p_datos/datos_tutorial2.php' );

I have changed its position in the structure of the file, but it does not work either.

allan

When it "does not work" do you get any errors? If you could link to the page, that would help in my debugging of the problem.

Allan

jalape

Thanks Allan,
The address is:
http://www.javierlasen.es/recursos/login/admin_tutorial.php
When opening the editor box, a type 500 error occurs in the data_tutorial2.php file.

kthorngren

You are getting 500 Internal Server Error. This error is coming from your server. You will need to look at your server logs to see why this error is occurring.

Kevin

jalape

Thank you kthorngren,
Using Xampp as a local server, the error is the same.

tangerine

Kevin already told you

You will need to look at your server logs to see why this error is occurring.