PostgreSQL - id with auto sequence is not being set

mowax

I try to populate a very simple table (as an example):

locations
* id
* name
* city

The "id" field is the primary key with an locations_id_seq sequence on it, so this id field is usually auto populated with a unique number.

I can insert data to this table with a standard SQL query like this:

insert into locations (name, city) values ('TestLocation', 'TestCity')

This just works fine, and the id will be raised with every entry to 1,2,3.. etc.

Now, when i try this with the server side PHP framework from datatables editor, it just is not raising the id when new entries are created. It just sets it to 0. And then every new entry afterwards just overwrites the data in the row with the id 0. Everything else (reading an displaying the table data and edit some fields) works fine.

I have tried it also with the "basic initialisation" example provided. I filled the database with the example tables and data. Again: reading and editing existing rows works fine. When adding new entries, also, the id field is set to 0 for the new entry.

This is the html for my example table:

<table id="location_table" class="display">
  <thead>
    <tr>
      <th>Name</th>
      <th>City</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>Name</th>
      <th>City</th>
    </tr>
  </tfoot>
</table>

The JS:

location_editor = new $.fn.dataTable.Editor( {
        "ajax": {
          url: ajax.ajax_url,
          "data": function ( d ) {
            d.action = "location_list_handler";
          }
        },~~~~
        table: "#location_table",
        fields: [ 
          {
            label: "Name:",
            name: "name"
          }, {
              label: "City:",
              name: "city"
          } ] 
    } );

    $('#location_table').DataTable( {
        dom : "Bfrtip",
        "ajax": {
          url: ajax.ajax_url,

          "data": function ( d ) {
            d.action = "location_list_handler";

          }
        },
        columns: [
            { data: "name" },
            { data: "city" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: location_editor },
            { extend: "edit",   editor: location_editor },
            { extend: "remove", editor: location_editor }
        ],
        
   } );

And this is the server side PHP:

<?php

include (plugin_dir_path( __FILE__ ) . 'lib/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 = Editor::inst( $db, 'locations')
    ->fields(
           Field::inst( 'name' ),
           Field::inst( 'city' )
        )

    ->process( $_POST )
    ->json();

Any ideas? I also read this one: https://datatables.net/forums/discussion/34500
But this is what i did - just not having a field specified for the id (same procedure like in a normal sql query)____

allan

Weird! Could you do:

$editor = Editor::inst( $db, 'locations')
    ->fields(
           Field::inst( 'name' ),
           Field::inst( 'city' )
        )
 
    ->debug( true )
    ->process( $_POST )
    ->json();

and then show me the JSON response from the server when you send the create form? It will show the SQL that Editor is generating and attempting to run.

Also, is your id column a primary key? It shouldn't actually make any difference I think, but it would be worth knowing if it is or isn't.

Thanks,
Allan

mowax

Hallo Allan,

i got it done. I forgot to say, that i'm using datatables/editor with wordpress. So, like a lot of other users already mentioned, datatables 'action' is clashing with wordpress' 'action', when you try it the proper wordpress way.

So what is described in this blog post is not to be prefered when you really want to integrate datatables in wordpress - like i've read here.

In case others struggling with the same problems:

I renamed in editor.php all ajax 'action' to 'tbl_action'.
And the JS looks like:

editor = new $.fn.dataTable.Editor( {
        ajax: {
          url: my_ajax.ajax_url,
        },
        table: "#location_table",
        fields: [ {
              label: "Name",
              name: "name"
          }, {
              label: "City",
              name: "city"
          }
        ]
      } );
      editor.on( 'preSubmit', function ( e, data, action ) {

          data.tbl_action = action;
          data.action = "location_table_handler";
          data.security = my_ajax.security;
      } );

This is copying the 'action' from datatables to the 'tbl_action' data field, and then i can manually fill the 'action' field with the wordpress related stuff (the function that should be called) and added the security nonce as well.

Server side php:

wp_enqueue_script ('location-table-script');
add_action ( 'wp_ajax_location_table_handler', 'LocationTableHandling');

wp_localize_script ( 'location-table-script', 'my_ajax', array( 'ajax_url' => admin_url('admin-ajax.php'), 'security' => wp_create_nonce ( 'nonce' )) );

function LocationTableHandling () {
   //handle ajax post here
}

Now it works great and i'm free to use datatables php framework or my own server side. Both works.
Thanks for your help anyway!

allan

datatables 'action' is clashing with wordpress' 'action', when you try it the proper wordpress way.

Argh! Okay thanks. I will have a look at providing a config option for that.

Allan