Dependency Issue - A select based on a second select not functioning as expected

nitel

Hi all,

I am having an issue with dependency.

I have a database that contains all my clients modems. I am using Datatables and Editor to display the modems and allow editing.

Each modem has a Make and a Model, with the Model being dependent on which Make is selected. The Datatable displays the modem name (text), the make (select), the model (select) and whether I have remote access (checkbox).

The problem:

I am losing my Model whenever I edit a modem that does not have the same Model as the previous edit. (* Edit can be: using the button or inline editing)

For example (two modems):

I have Modem 1 (which is a Netcomm NB604 with remote access) and Modem 2 (which is a TP-LINK TD-8840T with remote access).

I open my DataTables web page. I can see both modems listed in the editable DataTable.

1. I will firstly try to set Modem 1 to no remote access (I am using an Always Show Checkbox in the Remote Access column to simply toggle yes or no)
2. I un-check the remote access box for Modem 1 and the system updates no problems. I now have Modem 1 with no remote access and Modem 2 with remote access. They both
   still have their make and model displayed.
3. I will now try to do the same for Modem 2.
4. I un-check the remote access box for Modem 2 and the system updates and the model of Modem 2 disappears (if I were to have inline editing on this field enabled then it would
   have changed to my placeholder of Please Select).
5. I finally decide to check remote access back on for Modem 1 and I also lose the model of Modem 1.

The same happens if using the Edit button: (reset original values for this example; Modem 1 - Netcomm NB604 with remote access and Modem 2 - TP-LINK TD-8840T with remote access)

I open my DataTables web page. I can see both modems listed in the editable DataTable.

1. I select Modem 1 and click edit. The form appears and I decide not to make any changes so I hit the escape key.
2. I then select Modem 2 and click edit. The form appears and I can see that The correct make is selected in the Make select box, however in the Model select box,
   my placeholder of "Please Select" is selected (yet in the DataTable and indeed the database, it does have a model assigned.
3. So I do not want to make any changes so I hit the escape key again.
4. I decide to try clicking edit again on the already selected Modem 2 and the model is now selected.

I'm really stuck here. Here is the structure/data of the database and all the code:

Database:

adsl_modems

modem_id, modem_name, make_id, model_id,remote_access
1, "Modem 1", 1, 1,1
2, "Modem 2", 2, 2,1

make

make_id, make
1, "Netcomm"
2, "TP-LINK"

model

model_id, make_id, model
1, 1, "NB604"
2, 2, "TD-8840T"

HTML:

<table id="modems" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th></th>
            <th>Modem Name</th>
            <th>Make</th>
            <th>Model</th>
            <th>Remote Access</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th></th>
            <th>Modem Name</th>
            <th>Make</th>
            <th>Model</th>
            <th>Remote Access</th>
        </tr>
    </tfoot>
</table>

JS:

var editor; // use a global for the submit and return data rendering

editor = new $.fn.dataTable.Editor( {
    ajax: "./php/adsl_modems.php",
    table: "#modems",
    idSrc: "adsl_modems.modem_id",
    fields: [
        { label: "Modem Name:", name: "adsl_modems.modem_name" },
        { label: "Make:", name: "adsl_modems.make_id", type: "select", placeholder: "Please Select"},
        { label: "Model:", name: "adsl_modems.model_id", type: "select", placeholder: "Please Select" },
        {
            label:     "Remote Access:",
            name:      "adsl_modems.remote_access",
            type:      "checkbox",
            separator: "|",
            def:        1,
            options:   [
                { label: \'\', value: 1 }
            ]
        }
    ]
} );

// Activate an inline edit on click of a table cell
$("#modems").on( "click", "tbody td.editable", function (e) {
    editor.inline( this, {
        submit: "allIfChanged",
        buttons: { label: ">", fn: function () { this.submit(); } }
    } );
} );

var dt_modems = $("#modems").DataTable( {
    dom: "Bfrtip",
    ajax: "./php/adsl_modems.php",
    columns: [
        {
            data: null,
            defaultContent: "",
            className: "select-checkbox",
            orderable: false
        },
        { className: "dt-center editable", data: "adsl_modems.modem_name" },
        { className: "dt-center editable", data: "make.make", editField: "adsl_modems.make_id" },
        { className: "dt-center editable", data: "model.model", editField: "adsl_modems.model_id" },
        {
            data:   "adsl_modems.remote_access",
            render: function ( data, type, row ) {
                if ( type === "display" ) {
                    return \'<input type="checkbox" class="editor-active-remote">\';
                }
                return data;
            },
            className: "dt-body-center"
        }
    ],
    select: {
        style:    "os",
        selector: "td:first-child"
    },
    buttons: [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ],
    rowCallback: function ( row, data ) {
        // Set the checked state of the checkbox in the table
        $("input.editor-active-remote", row).prop( "checked", data.adsl_modems.remote_access == 1 );
    }   
    
} );

$("#modems").on( "change", "input.editor-active-remote", function () {
    editor
        .edit( $(this).closest("tr"), false )
        .set( "adsl_modems.remote_access", $(this).prop( "checked" ) ? 1 : 0 )
        .submit();
} );

editor.dependent( "adsl_modems.make_id", function ( val, data, callback  ) {

    $.ajax( {
        url: "./ajax_scripts/ajax_select_models.php",
        data: { "make_id": val },
        dataType: "json",
        success: function ( json ) {
            callback( json );
        }
    } );

} );

PHP

adsl_modems.php

// DataTables PHP library
include( "../Editor-PHP-1.5.5/php/DataTables.php" );

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

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'adsl_modems', 'adsl_modems.modem_id' )
    ->fields(
        Field::inst( 'adsl_modems.modem_id' )->set(false),
        Field::inst( 'adsl_modems.modem_name' ),
        Field::inst( 'adsl_modems.make_id' )
            ->options( 'make', 'make_id', 'make' ),
        Field::inst( 'adsl_modems.model_id' )
            ->options( 'model', 'model_id', 'model' ),
        Field::inst( 'adsl_modems.remote_access' ),
        Field::inst( 'make.make' ),
        Field::inst( 'model.model' )
    )
    ->leftJoin( 'make', 'make.make_id', '=', 'adsl_modems.make_id' )
    ->leftJoin( 'model', 'model.model_id', '=', 'adsl_modems.model_id' )
    ->process( $_POST )
    ->json();

ajax_select_models.php

$make_id = $_GET['make_id'];
$model_arr = array();
$model_arr_inner = array();
$return_arr = array();

//--------------------------------------------------------------------------
// Get Make Details
//--------------------------------------------------------------------------
$query = "SELECT model_id, model FROM model WHERE make_id = $make_id ORDER BY model ASC";
$result = $mysqli->query($query);
$row_cnt = $result->num_rows;

if ($row_cnt > 0) {

    while ($row = $result->fetch_array(MYSQLI_BOTH)) {

        $model_id = $row['model_id'];
        $model = $row['model'];
        $model_arr_inner['label'] = $model;
        $model_arr_inner['value'] = $model_id;

        $model_arr[] = $model_arr_inner;

    }

} else {

    $model_arr_inner['label'] = 'Please Select';
    $model_arr_inner['value'] = "0";
    $model_arr[] = $model_arr_inner;

}

$result->free();

$options_arr['adsl_modems.model_id'] = $model_arr;

$return_arr['options'] = $options_arr;

print json_encode($return_arr);

allan

Hi,

Are you able to give me a link to the page you are working on so I can take a look and debug it directly? Two level dependent options should work well - indeed, I recently put this example together for a three level select.

Other than that, what version of Editor are you using?

Thanks,
Allan

nitel

Hi Allan,

Thanks for the prompt response.

I am using Editor version 1.5.5.

Here is a live example:

http://104.131.60.140/index.php

Thanks.

Jason

allan

Hi Jason,

Thanks for the link. I think the issue is being caused by the placeholder. Can I send you a dev version of Editor that includes the required fix to the e-mail address that you registered your account with?

Thanks,
Allan

nitel

Hi Allan,

Yes, absolutely you can send this to my email address. I'll test it out as soon as I receive it.

Thanks once again.

Jason

allan

Hi Jason,

Sounds great - I've just dropped the e-mail across.

Regards,
Allan

nitel

Hi Allan,

Fantastic. I have tested the dev version you sent me and can confirm the 'make' select is now selecting the correct option instead of the 'Please Select' placeholder option. Thank you very much!

There does however still seem to be an issue when checking and unchecking the 'Remote Access' checkbox (Always Show Checkbox) in the table. When checking it, the 'make' is cleared (and submitted that way). This is only the case from the Always Show Checkbox in the DataTable. When checking and unchecking from the Edit form, there is no problem.

I have updated my live example for you to have a look at.

Thanks very much.

Jason

allan

I'm afraid I don't seem to have the e-mail (I probably do, but can't find it!). Could you send it again or PM me by clicking my name above and then "Send message".

Thanks,
Allan

raulgonz

Could you please publish and share the solution to the problem of nitel ?. I would appreciate very much. Thank you