error when filtering records on update or inserting new records

Pliachas Paschalis

when i use "->where( $key = " in php file which returns the records to table, when i try to add or edit a record after updating or inserting the table doen't automatically reload and show the inserted or updated record.
the code of the php file:

<?php

/*
 * Editor server script for DB table Customers
 * Created by http://editor.datatables.net/generator
 */
$loggedid="";

if ( isset($_GET['loggedid']) ) $loggedid=$_GET['loggedid'];
//echo "hello " + $loggedid;
// 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;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `Customers` (
    `id` int(10) NOT NULL auto_increment,
    `custname` varchar(255),
    `custafm` varchar(9),
    `custusernameTax` varchar(255),
    `custpasswordTax` varchar(255),
    `custamka` varchar(255),
    `custusername` varchar(255),
    `custpassword` varchar(255),
    `loggedid` int(10) NOT NULL,
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'Customers', 'id' )
    ->fields(
        Field::inst( 'custname' ),
        Field::inst( 'custafm' ),
        Field::inst( 'custusernameTax' ),
        Field::inst( 'custpasswordTax' ),
        Field::inst( 'custamka' ),
        Field::inst( 'custusernameIKA' ),
        Field::inst( 'custpasswordIKA' ),
        Field::inst( 'loggedid' )
    )
    ->where( $key = "loggedid", $value =  $loggedid, $op = "=" )
    ->process( $_POST )
    ->json();

the code of the js file:

/*
 * Editor client script for DB table Customers
 * Created by http://editor.datatables.net/generator
 */
 var $loggedid;
 //$sendCodecust;

 $loggedid=loggedid;
 //alert("codecust: " + $loggedid);

(function($) {

    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'php/table.Customers.php',
            table: '#Customers',
            fields: [{
                    "label": "Ονοματεπώνυμο:",
                    "name": "custname"
                },
                {
                    "label": "ΑΦΜ:",
                    "name": "custafm"
                },
                {
                    "label": "UserName Taxis:",
                    "name": "custusernameTax"
                },
                {
                    "label": "Password Taxis:",
                    "name": "custpasswordTax"
                },
                {
                    "label": "ΑΜΚΑ:",
                    "name": "custamka"
                },
                {
                    "label": "UserName IKA:",
                    "name": "custusernameIKA"
                },
                {
                    "label": "Password IKA:",
                    "name": "custpasswordIKA"
                },
                {
                    "label": "Logged Id:",
                    "name": "loggedid",
                    def: $loggedid
                }
            ]

        });

        //
        editor.on( 'onPreSubmit', function ( e, data ) {
            data.loggedid = loggedid;
        } );

        
        $('#Customers tfoot th').each(function() {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Search ' + title + '" />');
        });


        //

        var table = $('#Customers').DataTable({
            ajax: 'php/table.Customers.php',
            "fnServerParams": function ( aoData ) {
                    aoData.push( { "name": "loggedid", "value": $loggedid } );
                    },
            columns: [{
                    "data": "custname"
                },
                {
                    "data": "custafm"
                },
                {
                    "data": "custusernameTax"
                },
                {
                    "data": "custpasswordTax"
                },
                {
                    "data": "custamka"
                },
                {
                    "data": "custusernameIKA"
                },
                {
                    "data": "custpasswordIKA"
                },
                {
                    "data": "loggedid"
                }
            ],
            select: true,
            lengthChange: false
                        
        });

        new $.fn.dataTable.Buttons(table, [{
                extend: "create",
                editor: editor
            },
            {
                extend: "edit",
                editor: editor
            },
            {
                extend: "remove",
                editor: editor,
                formMessage: function(e, dt) {
                    var rows = dt.rows(e.modifier()).data().pluck('custname');
                    return 'do you want to delete the record(s) ' +
                        'with values? <ul><li>' + rows.join('</li><li>') + '</li></ul>';
                }
            }
        ]);

        table.buttons().container()
            .appendTo($('.col-sm-6:eq(0)', table.table().container()));
        // Apply the search
        table.columns().every(function() {
            var that = this;

            $('input', this.footer()).on('keyup change', function() {
                if (that.search() !== this.value) {
                    that
                        .search(this.value)
                        .draw();
                }
            });
        });
    });

}(jQuery));

Pliachas Paschalis

In fact what really happens is that the edited or the newly inserted record doesn't appear on table and i have to refresh the page manually to see the changes on table

allan

Could you give me a link to the page please? Editor should automatically display the new row. If a link isn't possible, what is the JSON that is being returned from the server from the create new record Ajax request?

Allan

Pliachas Paschalis

http://176.32.230.42/pliachaspaschalis.com/amkagsis/Customers.php

Log in using:
test@test.com
op

allan

Thanks.

This is the response from the server when a new row is created:

{"data":[]}

The fact that it doesn't have the data for the new row explains why it isn't being shown in the table.

My guess is that it is caused by this:

->where( $key = "loggedid", $value =  $loggedid, $op = "=" )

Could you try removing that for the moment and see if it then works please? Looking at the code, I think it should work, but let's eliminate that as a possibility first.

Allan

Pliachas Paschalis

Yes it works. But i needed this to show only the records of the customer logged on.

allan

Okay - so that suggests that loggedid isn't being set immediately - even although it looks like it is being sent to the server! Is it being set anywhere else, but a trigger or anything like that?

Could you enable the server-side debug mode by adding ->debug( true ) immediately before the process() call please/

Allan

Pliachas Paschalis

i did it. Is it the only thing you want me to change? or do you want me to enable back the:
->where( $key = "loggedid", $value = $loggedid, $op = "=" )

Pliachas Paschalis

I enabled it.

allan

Thank you - I see the issue now.

The problem is that while you have configured DataTables to send the loggedid property (using the legacy fnServerParams rather than the current ajax.data) that parameter isn't being sent with the Editor requests, so the WHERE condition is attempting to get loggedid = NULL, which is why it is failing.

So to make it work - change:

ajax: 'php/table.Customers.php',

To be

ajax: {
  url: 'php/table.Customers.php',
  data: function ( d ) {
    d.loggedid = $loggedid;
  }
}

And it should work correctly.

Allan

Pliachas Paschalis

this is almost the code now in js file (i removed the fields name here).

/*
 * Editor client script for DB table Customers
 * Created by http://editor.datatables.net/generator
 */
var $loggedid;
//$sendCodecust;

$loggedid = loggedid;
//alert("codecust: " + $loggedid);

(function($) {

    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'php/table.Customers.php',
            table: '#Customers',
            fields: [{
...
                   
            ]

        });

        //
        editor.on('onPreSubmit', function(e, data) {
            data.loggedid = loggedid;
        });

        //φίλτρο υποσέλιδου
        $('#Customers tfoot th').each(function() {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Αναζήτηση ' + title + '" />');
        });


        //

        var table = $('#Customers').DataTable({
            // ajax: 'php/table.Customers.php',

            ajax: {
                url: 'php/table.Customers.php',
                data: function(d) {
                    d.loggedid = $loggedid;
                }
            },
            // "fnServerParams": function ( aoData ) {
            //  aoData.push( { "name": "loggedid", "value": $loggedid } );
            //  },
            columns: [{
                    "data": ...
                }
            ],
            select: true,
            lengthChange: false
            }
        });

        new $.fn.dataTable.Buttons(table, [{
                extend: "create",
                editor: editor
            },
            {
                extend: "edit",
                editor: editor
            },
            {
                extend: "remove",
                editor: editor
            }
        ]);

        table.buttons().container()
            .appendTo($('.col-sm-6:eq(0)', table.table().container()));
        // Apply the search
        table.columns().every(function() {
            var that = this;

            $('input', this.footer()).on('keyup change', function() {
                if (that.search() !== this.value) {
                    that
                        .search(this.value)
                        .draw();
                }
            });
        });
    });

}(jQuery));

but it still doesn't work.
Am i missing something;

allan

It appears you haven't updated the line ajax: 'php/table.Customers.php', in the Editor configuration like I suggested above.

Allan

Pliachas Paschalis

Correct me if i am wrong the Editor configuration is at the js file, correct?
the code there is:

(function($) {

    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            table: '#Customers',
            // ajax: 'php/table.Customers.php',
            ajax: {
                url: 'php/table.Customers.php',
                data: function(d) {
                    d.loggedid = $loggedid;
                    //alert("codecust: " + $loggedid);
                }
            },
            fields: [{...

and also the code for DataTable ( i replaced it also there because it didn't work when i made the previous change in Editor):

 var table = $('#Customers').DataTable({
            // ajax: 'php/table.Customers.php',
            ajax: {
                url: 'php/table.Customers.php',
                data: function(d) {
                    d.loggedid = $loggedid;
                    //alert("codecust: " + $loggedid);
                }
            },

allan

We are nearly there! I see the update now and also see the issue that is now present.

Your PHP is looking for $_GET['loggedid'] and DataTables is sending a GET, but Editor is sending a POST.

I would suggest you add type: 'POST' to your DataTables' ajax object and update your PHP code to use $_POST['loggedid'].

Allan

Pliachas Paschalis

Thanks. It's working now