leftjoin does not update it makes an insert on edit ?

leftjoin does not update it makes an insert on edit ?

AlfredAlfred Posts: 11Questions: 0Answers: 0
edited September 2014 in Editor

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

Replies

  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin

    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

  • AlfredAlfred Posts: 11Questions: 0Answers: 0

    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

  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin

    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

  • AlfredAlfred Posts: 11Questions: 0Answers: 0
    edited 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();
    ?>
    

    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

  • AlfredAlfred Posts: 11Questions: 0Answers: 0

    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)
    
  • AlfredAlfred Posts: 11Questions: 0Answers: 0
    edited 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 !

  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin
    edited 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

  • AlfredAlfred Posts: 11Questions: 0Answers: 0

    Hi allan,

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

    so close it.

    Alfred

This discussion has been closed.