Update a table automatically

Mike-motiv8

I have two Datatables on the same page, #table1 and #table2.
DataTable one passes row line records to dataTable 2.
Is it possible to automatically update dataTable twos mysql table, (the actual tables where the information in is held?)
each time a record is copy and pasted to dataTable two?

Any pointers in the right direction would be appreciated.

Mike-motiv8

This is what I'm doing. But would like the SQL table to also be up dated.
Data is held in separate tables within the same database. Maybe a join?

http://live.datatables.net/weyodufi/1/edit

kthorngren

If you use the Editor and use Editor API's to create a new row in the destination table then the Editor will automatically send the new record to your server.

If you don't use Editor then as I explained in this thread you will need to create your own ajax request.

Kevin

Mike-motiv8

I'm actually being cauious, and making sure what i want to do is suported by the editor before buying.
Are there any examples I can look at, that show how you would point to the 2nd sql table to write to and .draw (update) the 2nd datatable.

kthorngren

I'm actually being cauious, and making sure what i want to do is suported by the editor before buying.

You can download Editor and try it for a period of time before buying it.

Are there any examples I can look at, that show how you would point to the 2nd sql table to write to and .draw (update) the 2nd datatable.

I don't know of any examples specific to this. You can look at the third example in the Editor Create API doc. It shows how to create a new record.

Instead of using table2.row.add(data).draw(); to add the data to the second table you would use the Editor Create API. The Editor will submit the row to your server and your server script will insert it into the DB. The server is expected to respond with the inserted row(s) and at that time the Editor will update the second Datatable. This page shows the expected data exchanges between the Editor and your server:
https://editor.datatables.net/manual/server#Example-data-exchanges

Kevin

colin

Hi @Mike-motiv8 ,

Are you using Editor? If so, you would just use create(). If not, you'll need to send the new data back to your server via Ajax and insert it there.

Cheers,

Colin

Mike-motiv8

What I not getting is, how does the create () API reference the 2nd mysql table inorder to write the new row record?
That's what i'm missing.

colin

I think I'm getting confused here. You said there were two tables on the page, which I assumed were linked to two tables on the back end. If you're using Editor, then the create() would right through that table to the SQL table on the backend.

Mike-motiv8

Yes I am Colin. 2 Datatables on the same page, linked to two different SQL tables but within the same database.

How does editor reference the 2nd sql table which is having data pasted from table 1?
I can't find any sample code anywhere. The examples on this site don't show the code.

colin

Both tables will be separate Editor instances. I've knocked out an example, see here. This could be used as a template for you.

Hope that helps,

Colin

Mike-motiv8

Thank you so much Colin, working on it now.

But where does #example2 table reference the sql database table. You're using a html list?
I have a mysql database with two tables, table one is is called music1, and table two is called music2. They are identical.

How does datatables reference music2 table so that the data copied to #example2 table is written to the backend music2 table?

My ajax php (responce) file pulls data from music1, and populates datatable 1.

kthorngren

Datatables/Editor doesn't reference the specific sql databases. That is up to your server script. There are lots of ways that this could be done. One way is to use a different AJAX URL for example2's Datatables and Editor. This would execute a different PHP script that handles the second database.

Kevin

Mike-motiv8

Yes Kevin i have tried the two different script approach, however the script i use to pull the data in, will not write the data.
Could you point me to such a script? I have tried the server-side editor example, but it does not work after adjusting the script fields to suit.

The script that pulls data into table two is here on a non editor page:
https://datatables.net/examples/data_sources/server_side.html
However this script will not write. Even after adjusting the query from SELECT to INSERT INTO

What am i doing wrong?
I'm keen to learn, but just need to get a handle these concepts. I think my issue may have been the server side script all a long???

kthorngren

Its a bit difficult to write a functioning script example as I don't have access to write to a backend database that you can test with. Colin's example provides everything you should need except that ajax to call the server.

Colin created a "Duplicate" button for table1 that will use the Editor instance for table2 to update the table. The example works and will behave the same when using ajax.

Here is an Editor example using server side processing:
https://editor.datatables.net/examples/simple/server-side-processing.html

Its a matter of extending Colin's example to use the proper ajax url configuration for each Datatable/Editor combination to send the requests to the proper PHP scripts in your server.

Without a link to your page its difficult to point you in the right direction. My suggestion would be to get the Editor working first so you can create and update the data in your tables. Once this is working then add the "Duplicate" functionality.

Kevin

Mike-motiv8

Kevin,

Almost there.
Not getting duplicate to work:
see: https://www.motiv-8.co.uk/music/editor/edit-table.php

Also how would i get the search to be case insensitive?

Thank you again for your assistance

kthorngren

Thats a good start. It looks like you need to add the ajax config to Editor2 so the editor2.create().set(data).submit(); gets sent to the server.

Also how would i get the search to be case insensitive?

I'm not familiar with PHP so wouldn't be much help there. That is where the case insensitive searching takes place. Are you using the scripts supplied by Datatables? What database are you using?

Someone else familiar with the PHP scripts should be able to help with setting up case insensitive searching.

Kevin

colin

The case sensitivity would either be a setting in your database, or something in your script. Could you post the script here, please, and give some more details about your DB setup.

Mike-motiv8

Hi Colin,
This is the code in my php file for search case insensitive:

   use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
     
     $requestData= $_REQUEST;
     $sql = "SELECT * FROM music1";
    $query=mysqli_query($db, $sql)
     $searchKeyWord = htmlspecialchars($requestData['search']['value']);
    if( !empty($searchKeyWord) ) { 
            $sql .=" WHERE ID  LIKE '".$searchKeyWord."%' ";  
            $sql .=" OR LOWER (Artist) LIKE '".$searchKeyWord."%' ";
            $sql .=" OR Artist LIKE '".$searchKeyWord."%' ";
            $sql .=" OR LOWER (Title) LIKE '".$searchKeyWord."%' ";
            $sql .=" OR Title LIKE '".$searchKeyWord."%' ";
    
        }
    
        
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'music1')
        ->fields(
            Field::inst( 'ID' ),
            Field::inst( 'Artist' )
        ->getFormatter( 'Format::CapFirst' )
        ->setFormatter( 'Format::CapFirst' ),
            Field::inst( 'Title' )
        ->getFormatter( 'Format::CapFirst' )
        ->setFormatter( 'Format::CapFirst' ),
        )
        ->process( $_POST )
        ->json();

if i do: serverSide: false
and

 "search": {
            "caseInsensitive": true,
             "smart":true
                },

it will work, but is slow.

Also, is it possible to add setFormatter to the editor fields so i can use ucwords (client side)

allan

Are you using a MySQL database? And if so, is the collation for it case sensitive or insensitive? The caseInsensitive option only effects client-side processing. With server-side processing its down to how the database performs its actions, as Colin suggests.

Also, is it possible to add setFormatter to the editor fields so i can use ucwords (client side)

setFormatter is a server-side function. If you want to change the case on the client-side use columns.render.

Allan

Mike-motiv8

Hi Allan,
I'm using serverside off for the yime being. As i don't want to waste trial time.

Two other Q's.
1. How can i give a edit field the focus? I have tried this method, with out success:

editor1.on('open', function() {
editor1.field( 'Artist' ).focus();
});

2. How do I omit the ID field from being copied to table two? I have tried:

columns: [ select: [1,2] ],

I would like the sql database to assign the ID, which it will as AUTO INCREMENT

colin

Hi @Mike-motiv8 ,

I'm using serverside off for the yime being. As i don't want to waste trial time.

The page we've been looking at has serverSide set to true - so that's why we're saying it's a DB issue or a PHP script issue.

How can i give a edit field the focus?

I'll get back to you on this one.

How do I omit the ID field from being copied to table two? I have tried:

Look at my example above, I'm deleting the field out of the object.

Cheers,

Colin

Mike-motiv8

Colin, i will switch off serverside soon, and work on table one search insensitive once i have bought editor.

I couldn't see a link to your example above.

colin

The link I was referring to was the example I sent before : http://live.datatables.net/yitoyenu/1/edit

And this example here shows how to give a field focus when the form options.

Mike-motiv8

I don't see it Colin.
6 fields in the html list, and 6 in each table.
Which field is being omitted?

colin

It's in the action for the button:

      {
        extend: "selected",
        text: "Duplicate",
        action: function(e, dt, node, config) {
          var data = table1.row({selected: true}).data();
          delete data.DT_RowId;
          editor2.create().set(data).submit();
        }
      }

Mike-motiv8

it it delete data.DT_RowId?
my table id is written as ID

therefore, i need to use, delete data.DT_RowID?