Possibility to save 2 different values in Database with 1 checkbox

karasu

Hi there,
Has one of you already experience with the following situation?

I would like to save a value with a checkbox in two different DB tables, e.g.
1. DB table bookings -> value status = "new" or "canceled"
2. DB table rooms -> value storno = 0 or 1

I can save in table rooms the value storno = 0 or 1 with the following code but
how can I additionally store the value status = "new" or "canceled" in the bookings table?

JS

var editor; // use a global for the submit and return data rendering in the examples
(function($) {
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor({
            ajax: '/php/table.bookings_test.php',
            table: '#bookings_test',
            fields: [
            {
                label:     "Storno:",
                name:      "rooms.ca_storno",
                type:      "checkbox",
                separator: "|",
                options:   [
                    { label: '', value: 1 }
                ]
            }]
        });
        
        var table = $('#bookings_test').DataTable({
            ajax: '/php/table.bookings_test.php',
            Processing: true,
            ServerSide: true,
            "lengthMenu": [[10, 20, 50, -1], [10, 20, 50, "alle"]],
            "pageLength": 20,
            columns: [
                {
                    data:   "rooms.ca_storno",
                    render: function ( data, type, row ) {
                        if ( type === 'display' ) {
                            return '<input type="checkbox" class="storno">';
                        }
                        return data;
                    },
                    className: "dt-body-center"
                }
            ],
            rowCallback: function ( row, data ) {
                // Set the checked state of the checkbox in the table
                $('input.storno', row).prop( 'checked', data.rooms.ca_storno == 1 );
            },
            responsive: true,
            columnDefs: [
                { type: 'date-eu', targets: 0 },
                { type: 'date-eu', targets: 10 }
            ]
        });
        $('#bookings_test').on( 'change', 'input.storno', function () {
            editor
                .edit( $(this).closest('tr'), false )
                .set( 'rooms.ca_storno', $(this).prop( 'checked' ) ? 1 : 0 )
                .submit();
        } );
    });
}(jQuery));

PHP

// DataTables PHP library and database connection
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;

Editor::inst( $db, 'rooms', 'uid' )
    ->field( 
        Field::inst( 'rooms.ca_storno' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } )
    )
    ->process($_POST)
    ->json();

BR

allan

I would use an event handler in the server-side code for this. Basically you want to have the checkbox submit just a single value, but have your event handler check that value, and if the conditional logic you need applies, then set the value of its partner field. See specifically the "Modifying Fields" section of that documentation page.

Allan

karasu

Dear Allan,

my problem is that I would like to update a value in 2nd table after I update the value in the 1st table. e.g.
1. DB table rooms -> value storno = 0 or 1
2. DB table bookings -> value status = "new" or "canceled"

Have you an example code for me, where I can get a solution for my issue ?

BR

karasu

Dear Allan,
Have you an example code for me, where I can get a solution for my issue ?
BR

rf1234

Hi, you can use a server event for this. In this case I would use postEdit:
https://editor.datatables.net/manual/php/events

Your code could look something like this. You can also use the Editor db handler like in the example below.

->on( 'postEdit', function ( $editor, $id, $values, $row ) {
    
    //you can use your field ca_storno like this:
    //$row["rooms"]["ca_storno"]
    
    $editor->db()
        ->raw()
        ->bind( ':fk',        $id        )
        ->bind( ':status',    $row["rooms"]["ca_storno"] )
        ->exec( 'UPDATE bookings   
                    SET status = :status   
                  WHERE fk = :fk' );
} )

Link to raw() documentation:
https://editor.datatables.net/docs/1.8.1/php/class-DataTables.Database.html#_raw
Click on '#' in the upper right corner of the descriptions and you'll see a code example for using the method as well.

karasu

Thank you for your feedback. I will try this and let you know.

karasu

Hi rf1234,
your suggestion was exactly what I need. It works perfect.
Thank you very much.
BR