leftjoin does not update it makes an insert on edit ?

Alfred · September 2014

Hi,

have two tables.

$out = Editor::inst( $db, 'users','id' )
    ->fields(
        Field::inst( 'users.name' ),
        Field::inst( 'users.email' ),
        Field::inst( 'forewardings.id' ),
    Field::inst( 'forewardings.source')->validator( 'Validate::email')
      )
  ->leftJoin( 'forewardings', 'users.id', '=', 'forewardings.uid' ) ;

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

Table looks fine.

Using an editor with 'forewardings.source' an editing, process is making an insert not an update... how can i force update instead insert?

Can i prevent insert/update and use my own SQL-Code?

Thanks.
Alfred

allan · September 2014

Hi,

So on edit, there is always something being INSERTed into the forewardings table. Is that correct?

I don't quite understand the structure of the data, could you possibly explain what you are trying to do? I don't understand why the forewardings table would be edited at all when updating a user.

Can i prevent insert/update and use my own SQL-Code?

Yes, but you wouldn't be able to use the Editor PHP libraries. You would need to create your own using the Editor client / server communication protocol.

Regards,
Allan

Alfred · September 2014

Hi allan,

yes, there is allways a insert.

Well the structure is:

"Usertable" contains the Userdata also an e-mail adress.
"Forewardings has a couple of other adresses which are forewarded to the mail-adress of the user.

eg:

Users
id: 1
User: allan
Mail: foo@faa.com

Forewardings:

id:1
uid:1
source: 1.foo@faa.com
destination:foo@faa.com

id:2
uid:1
source: 2.foo@faa.com
destination:foo@faa.com

In the Table i show:

users.user, forewardings.source, users.Mail

up to here all right.

Now i want to edit just forewardings.source (only one filed in editor).

Pressing the save button there is allways an insert not an update...

Is there no way to prevent sql writing like:

if (isset($_POST['action']) && $_POST['action']=='edit'){

     ->process($_POST, DO_NOT_WRITE)

     here i do my own SQL-STUFF getting data from $_POST['data']
   }

Thanks Alfred

allan · September 2014

Is there no way to prevent sql writing

Yes, you can use the set() method for a field (docs). Set the option to false and it will prevent Editor writing to the that field.

However, there might be something else going on. Are you able to show me your full PHP and Javascript?

Allan

Alfred · September 2014

Hi allan,

thanks for the replies.

Well, i already tried the set(false)... maybe i missed the doc ...

is it: $out ->process($_POST) ->set(false); ?

have a look at the sources:

PHP:

<?php
include( "lib/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;
            
   $out = Editor::inst( $db, 'mailuser','id' )
    ->fields(
        Field::inst( 'mailuser.user' ),
        Field::inst( 'mailuser.email' ),
        Field::inst( 'weiter.id' ),
    Field::inst( 'weiter.source')->validator( 'Validate::email')
    )
    ->leftJoin( 'weiter', 'mailuser.id', '=', 'weiter.uid' ) ; 
      $out  ->process($_POST);
      $out  ->data();
      $out  ->json();

<?php
>
```
?>


JS:

```js
var editor; 
(function($){

$(document).ready(function() {
                editor = new $.fn.dataTable.Editor( {
                    ajax: "table.mailadressen.php",
                    table: "#mailadressen",
                    "fields": [ 
                             {"label": "Mailadresse:",  "name": "weiter.source"}
                    ],
                                    "i18n": {
            "create": {
                "button": "Neu <span class='icon-plus'></span>",
                "title":  "Benutzer anlegen",
                "submit": "Speichern",
                "class": "btn btn-inverse"
            },
            "edit": {
                "button": "Ändern <span class='icon-pencil'></span>",
                "title":  "Mailadresse ändern",
                "submit": "Speichern"
            },
                "remove": {
                                    "button": "Löschen <span class='icon-trash'></span>",
                                    "title":  "Mailadresse löschen",
                                    "submit": "Löschen",
                                    "confirm": {
                    "_": "",
                    "1": "Soll die Mailadresse gelöscht werden?"
                }
    },
          "error": {
                "system": "Fehler! Bitte Support kontaktieren."
            }
        }
                }
                );

$('#mailadressen').dataTable( {
    dom: "Tfrtip",
    ajax: {
   url: "table.mailadressen.php",
   type: 'POST'
  },
 "aaSorting": [[ 0, "asc" ]],
    "aoColumns": [
        { "mData": "mailuser.user", "sTitle": "Benutzer", "sClass": "left", "sWidth": "33%"},                   
        { "mData": "weiter.source" , "sTitle": "Weiterleitung", "sClass": "left", "sWidth": "33%"},
        { "mData": "mailuser.email", "sTitle": "Ziel", "sClass": "left", "sWidth": "33%"}                   
        ],
    "bServerSide": true,
    "sServerMethod": 'POST',
    
    tableTools: {
      sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        },
        
    "oLanguage": {
        "sProcessing":   "Bitte warten...",
        "sLengthMenu":   "_MENU_ Einträge anzeigen",
        "sZeroRecords":  "Keine Einträge vorhanden.",
        "sInfo":         "_START_ bis _END_ von insgesamt _TOTAL_ Einträgen",
        "sInfoEmpty":    "0 bis 0 von 0 Einträgen",
        "sInfoFiltered": "(gefiltert von insgesamt _MAX_  Einträgen)",
        "sInfoPostFix":  "",
        "sSearch":       "Suchen&nbsp;&nbsp;",
        "sUrl":          "",
        "oPaginate": {
            "sFirst":    "Erster",
            "sPrevious": "Zurück",
            "sNext":     "Nächster",
            "sLast":     "Letzter"
    }
    
}
                } );
 
} );

}(jQuery));

Thanks
Alfred

Alfred · September 2014

got it..

   Field::inst( 'mailuser.user' )->set(false),
   Field::inst( 'mailuser.email' )->set(false),
   Field::inst( 'weiter.id' )->set(false),
   Field::inst( 'weiter.source')->validator( 'Validate::email')->set(false)

Alfred · September 2014

im on the way for a workaround:

if (isset($_POST['action'])){
        switch($_POST['action']){
            case 'edit':
            $id=$_POST['data']['weiter']['id'];
            $source=$_POST['data']['weiter']['source'];
            mysqli_query($db_connect,"update weiter set source='$source' where id='$id'");
            break;
            default:
            //var_dump($_POST);

            break;
        }
    
    }

edit: works

new: i got error :
Call to a member function insertId() on a non-object in /....../lib/Editor/Editor.php on line 996

delete: fatal!

no error, but all records in weiter and in mailuser are deleted !

allan · September 2014

It looks like you have set( false ) on every field - is that right? In which case no set would ever occur (and possibly why you are getting an error).

edit - I think I understand now - simply don't call the process() method on the edit action if you want to provide your own SQL update. Call it for the read, delete and insert (assuming you want the default Editor actions for that), but not if you don't want the update.

Having said that - there is still something odd going on. It shouldn't do an insert when you are doing an edit unless there is no data matching in the join table.

Allan

Alfred · September 2014

Hi allan,

i did a workaround without join. Thanks for the help.

so close it.

Alfred

leftjoin does not update it makes an insert on edit ?

leftjoin does not update it makes an insert on edit ?

Replies

Howdy, Stranger!

Categories

DataTables

leftjoin does not update it makes an insert on edit ?

leftjoin does not update it makes an insert on edit ?

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables