Editor with RowReorder - Undefined Index when adding a new record

JonathanTolhurst

I am trying to use the Rowreorder plugin with Editor but am getting a "A system error has occurred (More information)." error when adding a new record. The error message is:

 Notice: Undefined index: command.readingOrder in /pathto/controllers/command.php on line 65
{"data":[{"DT_RowId":"row_95","command":{"id":"95","COMMAND_NAME":"Test 46","SEQUENCE":"0","COMMAND_DESCRIPTION":"","CMD_CATEGORY_ID":"3","TYPE_ID":"1","DESCRIPTION":"","SCAN_CODE":"","DELAY":"60","ACTIVE":"1","FLAG_IFTTT":"1","readingOrder":"46"},"command_category":{"CMD_CATEGORY_NAME":"Space Exploration (Play)"},"command_type":{"TYPE_NAME":"Broadcast"}}]}

Line 65 is:

            ->where( 'command.readingOrder', $values['command.readingOrder'], '>=' )

The basic example worked fine? If you refresh the page the record has added. Row reordering works fine otherwise.

<script type="text/javascript" language="javascript" class="init">
var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {  
    editor = new $.fn.dataTable.Editor( {
        ajax: "controllers/command.php",
        table: "#command",
        fields: [ 
            {
                label: 'Order:',
                name: 'command.readingOrder',
                fieldInfo: 'This field can only be edited via click and drag row reordering.'
            },
            {
                label: "The Command",
                name: "command.COMMAND_NAME",
                type: "textarea"
            },
            {
                label: "Category",
                name: "command.CMD_CATEGORY_ID",
                type: "chosen",
                placeholder: "Please Select"
            },
            {
                label: "Type",
                name: "command.TYPE_ID",
                type: "select",
                placeholder: "Please Select"
            },
            {
            label: "IFTTT",
            name:  "command.FLAG_IFTTT",
            type:   "checkbox",
            separator: "|",
                options:   [
                    { label: '', value: 1 }
                ],
            def:1
            },
            {
                label: "Command Description",
                name: "command.DESCRIPTION",
                type: "textarea"
            },
            {
                label: "Delay (Seconds)",
                name: "command.DELAY",
                type: "text"
            },
            {
                label: "Scancode",
                name: "command.SCAN_CODE",
                type: "text"
            },
            {
                label: "Order:",
                name: "command.SEQUENCE",
                type: "text",
            },
            {
            label: "Active",
            name:  "command.ACTIVE",
            type:   "checkbox",
            separator: "|",
                options:   [
                    { label: '', value: 1 }
                ],
            def:1
            }
        ]
    } );

    var table = $('#command').DataTable( {
        lengthChange: false,
        ajax: {
            url: "controllers/command.php",
            type: "POST"
        },
        columns: [
            { data: 'command.readingOrder', className: 'reorder' },
            { data: "command.COMMAND_NAME" },
            { data: "command_category.CMD_CATEGORY_NAME" },
            { data: "command.DESCRIPTION" },
            { data: "command_type.TYPE_NAME" },
            { data: "command.FLAG_IFTTT" },
            { data: "command.DELAY" },
            { data: "command.SCAN_CODE" },
            { data: null, defaultContent: "<button id='oADD'>Send</button>" },
            { data: "command.SEQUENCE" },
            { data: "command.ACTIVE" }
        ],
        columnDefs: [
         { orderable: false, targets: [ 1,2,3,4,5,6,7,8,9,10 ] },
         { width: "20%", "targets": [1,2] }
        ],
        rowReorder: {
            dataSrc: 'command.readingOrder',
            editor:  editor
        },
        select: true,
        initComplete: function () {
            this.api().columns('2').every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        }
    } );

    // Display the buttons
    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor },
        { extend: "pageLength" },
        { extend: "excel" },
        { extend: "print" },
        { extend: "colvis" },
        { extend: "copy" }
    
    ] );
    
        
       $('#command tbody').on( 'click', '#oADD', function () {
        var data = table.row( $(this).parents("tr") ).data();
        $.ajax({ url: 'commander-process.php',
         data: "CommandID="+data.command.id,
         type: 'post',
         success: function(output) {
                       $("#result").html(output);
                  }
    }); 
    });

    table.buttons().container()
        .prependTo( $("div.fg-toolbar:eq(0)", table.table().container() ) );
        
        
            editor
        .on( 'postCreate postRemove', function () {
            // After create or edit, a number of other rows might have been effected -
            // so we need to reload the table, keeping the paging in the current position
            table.ajax.reload( null, false );
        } )
        .on( 'initCreate', function () {
            // Enable order for create
            editor.field( 'command.readingOrder' ).enable();
        } )
        .on( 'initEdit', function () {
            // Disable for edit (re-ordering is performed by click and drag)
            editor.field( 'command.readingOrder' ).disable();
        } );
} );
</script>

Controller:

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../lib/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,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'command' )
    ->fields(
        Field::inst( 'command.id' ),
        Field::inst( 'command.COMMAND_NAME' )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A command name is required' )
            ) ),
        Field::inst( 'command.SEQUENCE' )->validator( 'Validate::numeric' ),    
        Field::inst( 'command.COMMAND_DESCRIPTION' ),   
        Field::inst( 'command.CMD_CATEGORY_ID' )
        ->options( Options::inst()
                ->table( 'command_category' )
                ->value( 'id' )
                ->label( 'CMD_CATEGORY_NAME' )
            ),
        Field::inst( 'command_category.CMD_CATEGORY_NAME' ),
        Field::inst( 'command.TYPE_ID' )
        ->options( Options::inst()
                ->table( 'command_type' )
                ->value( 'id' )
                ->label( 'TYPE_NAME' )
            ),
        Field::inst( 'command_type.TYPE_NAME' ),
        Field::inst( 'command.DESCRIPTION' ),
        Field::inst( 'command.SCAN_CODE' ),
        Field::inst( 'command.DELAY' ),
        Field::inst( 'command.ACTIVE' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'command.FLAG_IFTTT' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'command.readingOrder' )->validator( 'Validate::numeric' ) 
    )
    ->leftJoin( 'command_type', 'command.TYPE_ID', '=', 'command_type.id'   )
    ->leftJoin( 'command_category', 'command.CMD_CATEGORY_ID', '=', 'command_category.id'   )
    ->on( 'preCreate', function ( $editor, $values ) {
        // On create update all the other records to make room for our new one
        $editor->db()
            ->query( 'update', 'command' )
            ->set( 'command.readingOrder', 'command.readingOrder+1', false )
            ->where( 'command.readingOrder', $values['command.readingOrder'], '>=' )
            ->exec();
    } )
    ->on( 'preRemove', function ( $editor, $id, $values ) {
        // On remove, the sequence needs to be updated to decrement all rows
        // beyond the deleted row. Get the current reading order by id (don't
        // use the submitted value in case of a multi-row delete).
        $order = $editor->db()
            ->select( 'command', 'command.readingOrder', array('command.id' => $id) )
            ->fetch();
 
        $editor->db()
            ->query( 'update', 'command' )
            ->set( 'command.readingOrder', 'command.readingOrder-1', false )
            ->where( 'command.readingOrder', $order['command.readingOrder'], '>' )
            ->exec();
    } )
    ->process( $_POST )
    ->json();
?>

allan

Change $values['command.readingOrder'] to be:

$values['command']['readingOrder']

Allan

JonathanTolhurst

Thank you, that worked perfectly