Manipulate DOM - Change values in database

mgeyer · July 2019

Hello,

I want to display a value to a read only textfield, this value is combined through the values of different table values e.g.

Key = Customer + ID + Bezeichnung

my question is how can I read values from the table to concatenate them to a variable which is displayed in a read only field and how can I increase the value of a field (e.g. ID++ and save this to the table)?

please ask if something isn't clear to you.

thanks,
max

mgeyer · July 2019

for example

if I click the button new, a modal appears.

inside this modal I want to create the key, concatened from the values loaded (this values are loaded inside the modal).

for example id is loaded in a read only textfield,
customer is loaded in a select drop down (so I have to selected the active one),
and bezeichnung/name can be changed by the user (so the key value has to update if the bezeichnung/name changes)

-> when I click save the ID should be increased, so when I click the next time new, there has to be a higher value.

allan · July 2019

Assuming you have the two pieces of data in the row's data object (how you build that will depend on how you are reading the database), then you could use a renderer to do the combining.

Allan

mgeyer · July 2019

@allan thank you for your answer!

would it be visible for the user in real time?

is there an option to use normal javascript in fields:

fields: [
 {
    label: "Projektschlüssel:",
    name: "Projekte_Kundenprojekte.PMSchluessel",       
     def: function () {
        document.getElementById("DTE_Field_Projekte_Kundenprojekte-PMSchluessel").value = "Johnny Bravo";
}

for the moment I can't access by Id - but it would be clear to me.

mgeyer · July 2019

please see the attached file (image)

the PMSchluessel (projectmanagementkey) is going to be generated dynamically on the user inputs - and the user sees the generated key in real time - it is combined with a onchange event for the text input - how to do this for the create button in datatable?

thank you!

rf1234 · July 2019

https://editor.datatables.net/reference/api/dependent()

I would use "dependent" for this.

I use it all the time to show, hide and fill Editor fields depending on the respective situation. This can be very powerful - and complex too.

Here is a simple example from my own coding. As you can see you can embed "dependent" into "initEdit" and "initCreate" event handlers and hence create different dependencies if your are in edit or create mode. You can use "undependent" to delete the dependency on closing Editor for example.

ctrEditor
    .on('close', function() {
        this.undependent('ctr.end_date');
//if you don't have the latest Editor version you may also use this:
// $( this.field('ctr.end_date').node() ).off( 'change keyup' );
    })
    .dependent('ctr.automatic_prolongation', function (val, data, callback) {
        var self = ctrEditor;
        if (val == '1') {
            self.show(['ctr.prolongation_months', 'ctr.last_day_month']);
        } else {
            self.set({'ctr.prolongation_months': 0, 'ctr.last_day_month': 0})
                .hide(['ctr.prolongation_months', 'ctr.last_day_month']);
        }
    })    
    .on ( 'initCreate', function ( e ) {
        this.dependent('ctr.end_date', function (val, data, callback) {
            var self = ctrEditor;
            self.set( { 'ctr.original_end_date': self.val('ctr.end_date') } );          
            ctrEndDateStandardDependencies(self);
        })
   .on('initEdit', function ( e, node, data, items, type ) {
        this.dependent('ctr.end_date', function (val, data, callback) {
            ctrEndDateStandardDependencies(ctrEditor);
        })
    })
....
function ctrEndDateStandardDependencies(that) {
    if (that.val('ctr.end_date') > '') {
        that.show(['ctr.automatic_prolongation']);
    } else {
        that.set({'ctr.automatic_prolongation': 0})
            .hide(['ctr.automatic_prolongation']);
    }
    if (that.val('ctr.end_date') > '' && that.val('ctr.automatic_prolongation') == 1) {
        that.show(['ctr.prolongation_months', 'ctr.last_day_month']);
    } else {
        that.set({'ctr.prolongation_months': 0, 'ctr.last_day_month': 0})
            .hide(['ctr.prolongation_months', 'ctr.last_day_month']);
    }
}

mgeyer · July 2019

@rf1234 hi roland,

can you give me some start help?

I'm not very familar with initCreate, etc.

where should I write which code to get things happen the right way?
how can I read the value of a field?
how can I change the value of a field when another field is changed (change event)?

=======

here is my php file (parent table)

<?php

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;

Editor::inst( $db, 'Projekte_Kundenprojekte' )
    ->fields(
        Field::inst( 'Projekte_Kundenprojekte.ID' )->set( false ),
        Field::inst( 'Projekte_Kundenprojekte.PMSchluessel' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'Projekte_Kundenprojekte.Beschreibung' ),
        Field::inst( 'Projekte_Kundenprojekte.Statusmeldung' ),
        Field::inst( 'Projekte_Kundenprojekte.Zieldatum' ),
        Field::inst( 'Projekte_Kundenprojekte.Mitarbeiter_ID' )
            ->options(Options::inst()
            ->table('Mitarbeiter')
            ->value('ID')
            ->label('Abkuerzung')
        ),
        Field::inst( 'Projekte_Kundenprojekte.Modus_ID' )
            ->options(Options::inst()
            ->table('Modus')
            ->value('ID')
            ->label('Modus')
        ),
        Field::inst( 'Projekte_Kundenprojekte.Kunde_ID' )
            ->options(Options::inst()
                ->table('Kunden')
                ->value('ID')
                ->label('Abkuerzung')
        ),
        Field::inst( 'Projekte_Kundenprojekte.Nummernkreis_ID')
            ->options(Options::inst()
                ->table('Nummernkreise')
                ->value('ID')
                ->label('Wert')
        ),
        Field::inst( 'Mitarbeiter.Abkuerzung' ),
        Field::inst( 'Modus.Modus'),
        Field::inst( 'Nummernkreise.Name')
    )
    ->leftJoin( 'Kunden', 'Kunden.ID', '=', 'Projekte_Kundenprojekte.Kunde_ID')
    ->leftJoin( 'Modus', 'Modus.ID', '=', 'Projekte_Kundenprojekte.Modus_ID')
    ->leftJoin( 'Mitarbeiter', 'Mitarbeiter.ID', '=', 'Projekte_Kundenprojekte.Mitarbeiter_ID')
    ->leftJoin( 'Nummernkreise', 'Nummernkreise.ID', '=', 'Projekte_Kundenprojekte.Nummernkreis_ID')
    ->join(
        Mjoin::inst( 'Aufgaben_Kundenprojekte' )
            ->link( 'Projekte_Kundenprojekte.ID', 'Aufgaben_Kundenprojekte.Projekt_ID' )
            ->fields(
                //nur noch die oder das Feld verwenden was wirklich gebraucht wird überlegen oder try and error
                Field::inst( 'Projekte_Kundenprojekte.ID' )->set(false)->name('ID'),
                Field::inst( 'Aufgaben_Kundenprojekte.ID' )->set(false)->name('ID'),
                Field::inst( 'Aufgaben_Kundenprojekte.Projekt_ID' )->set(false)->name('Projekt_ID'),
                Field::inst( 'Projekte_Kundenprojekte.PMSchluessel' )->set(false)->name('PMSchluessel'),
                Field::inst( 'Aufgaben_Kundenprojekte.PMSchluessel' )->set(false)->name('PMSchluessel')
            )
    )
    /*
    ->join(
        Mjoin::inst('Kunden')
            ->link('Projekte_Kundenprojekte.Kunde_ID', 'Kunden.ID')
            ->fields(
                Field::inst('Abkuerzung')
            )
    )
    */
    ->process( $_POST )
    ->json();

<?php
>
```
?>


here is my javascript file (parent table)

//==============
//Parent Table
//==============
$(document).ready(function () {
var siteEditor = new $.fn.dataTable.Editor({
ajax: "php/projekte_kundenprojekte.php",
table: "#projekte_kundenprojekte",
fields: [
{
label: "Projektschlüssel:",
name: "Projekte_Kundenprojekte.PMSchluessel"
},
{
label: "Kunde:",
name: "Projekte_Kundenprojekte.Kunde_ID",
type: "select"
},
{
label: "ID:",
name: "Projekte_Kundenprojekte.Nummernkreis_ID",
type: "select"
},
{
label: "Beschreibung:",
name: "Projekte_Kundenprojekte.Beschreibung"
},
{
label: "Statusmeldung:",
name: "Projekte_Kundenprojekte.Statusmeldung"
},
{
label: "Zieldatum:",
name: "Projekte_Kundenprojekte.Zieldatum",
type: 'datetime',
def: function () { return new Date(); },
format: 'YYYY-MM-DD'
},
{
label: "Verantwortlich",
name: "Projekte_Kundenprojekte.Mitarbeiter_ID",
type: "select"
},
{
label: "Modus",
name: "Projekte_Kundenprojekte.Modus_ID",
type: "select"
}
]
});

var siteTable = $("#projekte_kundenprojekte").DataTable({
    dom: "Bfrtip",
    ajax: "php/projekte_kundenprojekte.php",
    language: { url: "js/trans/dataTables.german.lang" },
    order: [1, "asc"],
    columns: [
        {
            className: "details-control",
            orderable: false,
            data: null,
            defaultContent: "",
            width: "7%"
        },
        { data: "Projekte_Kundenprojekte.PMSchluessel" },
        { data: "Projekte_Kundenprojekte.Beschreibung" },
        { data: "Projekte_Kundenprojekte.Statusmeldung" },
        { data: "Projekte_Kundenprojekte.Zieldatum", },
        { data: "Mitarbeiter.Abkuerzung" },
        { data: "Modus.Modus" },
        {
            data: "Aufgaben_Kundenprojekte",
            render: function (data) {
                return data.length;
            }
        }
    ],
    select: {
        style: "os",
        selector: "td:not(:first-child)"
    },
    buttons: [
        { extend: "create", editor: siteEditor },
        { extend: "edit", editor: siteEditor },
        { extend: "remove", editor: siteEditor }
    ]
});

// Add event listener for opening and closing details
$("#projekte_kundenprojekte tbody").on("click", "td.details-control", function () {
    var tr = $(this).closest("tr");
    var row = siteTable.row(tr);

    if (row.child.isShown()) {
        // This row is already open - close it
        destroyChild(row);
        tr.removeClass("shown");
    } else {
        // Open this row
        createChild(row);
        tr.addClass("shown");
    }
});

// When updating a site label, we want to update the child table's site labels as well
siteEditor.on("submitSuccess", function () {
    siteTable.rows().every(function () {
        if (this.child.isShown()) {
            updateChild(this);
        }
    });
});

});
```

thank you!

max

rf1234 · July 2019

This is all java script. Put it all underneath the Editor definition before the data table definition.

editor.val('yourField') gives you the value of a field

The "dependent" event IS the actual change event.

editor.set sets the value of a field - just like in the examples above.

And yes: "Read the docs" ...
https://editor.datatables.net/reference/api/set()
https://editor.datatables.net/reference/api/val()
https://editor.datatables.net/manual/events

mgeyer · July 2019

@rf1234 thank you very much!

for the moment I have a test license so I can't read all docs ^^

can you show me a demo code based on my code - for reading a field?
should look like this or?

    siteEditor.on('initCreate', function (e) {
        siteEditor.val('PMSchluessel');
    })

rf1234 · July 2019

yes, but why don't you just try it? I mean you have a debugger, I guess.
var key = siteEditor.val('PMSchluessel');
set a break point and take a look at the content of "key" for example or log it to the console.
And if you don't have access to the docs then I wouldn't use Editor. That doesn't really make very much sense. We can't replace the docs in the forum, sorry.

allan · July 2019

siteEditor.on('initCreate', function (e) {
    siteEditor.val('PMSchluessel');
})

that is indeed how you could read a value inside initCreate. You aren't using the value for anything there, but you could then go on to do something with it.

Allan

mgeyer · July 2019

@rf1234 because I have tried and don't get the information why it isn't working.
I would really like to use the editor, but I will get the money for the editor if the system is nice to use - so it is a little bit tricky for me to get this challenge accomplished.

@allan thank you!

it would be great if somebody could give me a start up code - for the moment I'm trying this:

siteEditor.on('initCreate', function (e) {
        this.dependent('Projekte_Kundenprojekte.PMSchluessel', function (val, data, callback) {
            var self = siteEditor;
            self.set({ 'Projekte_Kundenprojekte.PMSchluessel': self.val('Projekte_Kundenprojekte.PMSchluessel') });
            PMSchluesselGenerator(self);

        })
    });
...
function PMSchluesselGenerator(that) {
    if (that.val('Projekte_Kundenprojekte.PMSchluessel') == '') {
        that.show(['Projekte_Kundenprojekte.Zieldatum']);
    }
}

I just want to display in the PMSchluessel field a startup value - could also be test or something like this, but I don't get the information for the moment why it is not working.
It is for me like a needle in a haystack to get a clean solution, so any help would be awesome!

maximilian

rf1234 · July 2019

this line assigns the value of the variable to itself. Maybe you want to think it over.

.....
self.set({ 'Projekte_Kundenprojekte.PMSchluessel': self.val('Projekte_Kundenprojekte.PMSchluessel') });

mgeyer · July 2019

@rf1234

thank you

I see - how can I concatenate "_test"

self.set({ 'Projekte_Kundenprojekte.PMSchluessel': self.val('Projekte_Kundenprojekte.Zieldatum' + '_test') });

edit:
solved - thank you!!!

self.set({ 'Projekte_Kundenprojekte.PMSchluessel': self.val('Projekte_Kundenprojekte.Beschreibung').concat("_test") });

allan · July 2019

For what it is worth, I would suggest against calling dependent() inside initCreate. You'd be adding a dependency every time you trigger a create event. That's going to leak memory and clock cycles.

Allan

mgeyer · July 2019

@allan

thank you for your hint.

I am going to get a solution!

rf1234 · July 2019

@allan
you are ertainly right regarding max's code and the use of “dependent“! Thanks for clarifying!
I only had “dependent“ inside initCreate and initEdit in my example above because I have different dependencies for creating and editing on the same field and hence use “undependent“ on each close of the Editor form.

mgeyer · July 2019

@allan @rf1234

so what would be a clean solution for my problem?

just callin 'initCreate' and a javascript function?

    siteEditor.on('initCreate', function (e) {
        var self = siteEditor;
        PMSchluesselGenerator(self);
    });
...
function PMSchluesselGenerator(that) {
    that.set({ 'Projekte_Kundenprojekte.PMSchluessel': that.val('Projekte_Kundenprojekte.Kunde_ID').concat("_") });
}

how to implement the onchange event without depend?

how could I get the value of a select input?

and how could I increase a value on the table if I click save?

I'm sorry I don't have enough knowledge for the moment.

thank you!

allan · July 2019

Just don't use initCreate. Use dependent().

how to implement the onchange event without depend?

dependent() is the way to listen for a change event (at least, one of them!).

how could I get the value of a select input?

field().val()

and how could I increase a value on the table if I click save?

Use initSubmit to get the value, increment it and then set the new value.

Allan

mgeyer · July 2019

@allan thank you - I'm trying my best.

I'm trying to convert this to "Create" - just for information.

    $(document).ready(function() { projektnummer(); });
    function projektnummer (){
        richtige_laenge_bezeichnung();
        richtige_laenge_projektnummer();
        var projektnummer = document.getElementById("kunde").options[document.getElementById("kunde").selectedIndex].text + "_" + document.getElementById("bezeichnung_projekt").value + "_P" + document.getElementById("nummer").value;
        document.getElementById("h1_projektnummer").innerHTML = projektnummer;
    }
    function richtige_laenge_bezeichnung(){
        var bezeichnung = document.getElementById("bezeichnung_projekt");
        while(bezeichnung.value.length < 6){
                bezeichnung.value = "x" + bezeichnung.value;
        }
    }
    function richtige_laenge_projektnummer(){
        var projektnummer = document.getElementById("nummer");
        while(projektnummer.value.length < 5){
                projektnummer.value = "0" + projektnummer.value;
        }
    }

allan · July 2019

You've got a shadow variable name in projektnummer btw (function is also called the same thing). It won't error, but it can be a bit confusing.

From the code, you aren't updating based on user input, but rather when the page loads. So using initEdit would be the correct event to use here. Perhaps something like:

editor.on('initEdit', function () {
  while( editor.field('bezeichnung_projekt').val().length < 6 ) {
   editor.field('bezeichnung_projekt').val(
    'x' + editor.field('bezeichnung_projekt').val();
   );
  }

  //... etc for others
});

Allan

mgeyer · July 2019

@allan thanks.

===

just for information - without docs I can't solve this problem, so I have used bootstrap4 and the modal.

use this for calling the modal from a "custom" button (javascript-datatable)

        buttons: [
            {
                text: 'Create', extend: "create",
                action: function (e, node, config) {
                    $('#tableID').modal('show')
                }
            },

build the modal

<?php
    $con = mysqli_connect("localhost", "user", "password", "database");
    if (mysqli_connect_errno())
    {
        echo "Ahoi Matrose - Datenbankleck entdeckt: " . mysqli_connect_error();
    }
    
    $Kunde = mysqli_query($con, "SELECT * FROM Kunden");
    $NummerQr = mysqli_query($con, "SELECT Wert FROM Nummernkreise WHERE Name = 'Projekte' limit 1");
    $Nummer = mysqli_fetch_assoc($NummerQr);
    $Verantwortlich = mysqli_query($con, "SELECT * FROM Mitarbeiter");
    $Modus = mysqli_query($con, "SELECT * FROM Modus");
    mysqli_close($con);

<?php
>
...
    
    
        
            
                
                
                    Kundenprojekt hinzufügen
                    ×
                
                
                
                    
                    
                        PMSchluessel:
                        
                    
                    
                        Bezeichnung:
                        
                    
                    
                        Kunde:
                        
                            <?php while($KundeAktiv = mysqli_fetch_array($Kunde)):;?>
                                " value="<?php echo $KundeAktiv['ID'];?>"><?php echo $KundeAktiv['Abkuerzung'];?>
                            <?php endwhile;?>
                        
                    
                    
                        Nummer:
                        " value="<?php echo $Nummer['Wert']; ?>">
                    
                    
                        Beschreibung:
                        
                    
                    
                        Statusmeldung:
                        
                    
                    
                        Zieldatum:
                        
                    
                    
                        Verantwortlich:
                        
                            <?php while($VerantwortlichAktiv = mysqli_fetch_array($Verantwortlich)):;?>
                                " value="<?php echo $VerantwortlichAktiv['ID'];?>"><?php echo $VerantwortlichAktiv['Abkuerzung'];?>
                            <?php endwhile;?>
                        
                    
                    
                        Modus:
                        
                            <?php while($ModusAktiv = mysqli_fetch_array($Modus)):;?>
                                " value="<?php echo $ModusAktiv['ID'];?>"><?php echo $ModusAktiv['Modus'];?>
                            <?php endwhile;?>
                        
                    
                
                
                
                    Hinzufügen 
                    Schließen
                
            
        
    
    
```
?>


handle your inputs (javascript)

...
//put some where in $(document).ready(function () { and close
$('#modKundenprojekte').submit(function (e) {
$('#modKundenprojekte').modal('show');
e.preventDefault();
});

$('#modKundenprojekte').on('click', '.btn-info', function (e) {
    var vPMSchluessel = $('#PMSchluessel').val();
    var vBezeichnung = $('#Bezeichnung').val();
    var vKunde = $('#Kunde').val();
    var vNummer = $('#Nummer').val();
    var vBeschreibung = $('#Beschreibung').val();
    var vStatusmeldung = $('#Statusmeldung').val();
    var vZieldatum = $('#Zieldatum').val();
    var vVerantwortlich = $('#Verantwortlich').val();
    var vModus = $('#Modus').val();

    $.post("../php/your_php_file.php",
        {
            PMSchluessel: vPMSchluessel,
            Bezeichnung: vBezeichnung,
            Kunde: vKunde,
            Nummer: vNummer,
            Beschreibung: vBeschreibung,
            Statusmeldung: vStatusmeldung,
            Zieldatum: vZieldatum,
            Verantwortlich: vVerantwortlich,
            Modus: vModus,
        },
        function (response, status) {
            $("#result").html(response);
            //$('#projekte_kundenprojekte').DataTable().ajax.reload();
            //$('#modKundenprojekte').modal('hide');
            //$('#modKundenprojekte').modal('show');
            location.reload();
        });
    $('send').modal('show');
    $('schliessen').modal('hide');
});

...


do on the serverside (your_php_file.php)

<?php
//or use configuration file
$con = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno())
{
echo "Ahoi Matrose - Datenbankleck entdeckt: " . mysqli_connect_error();
}

if($_POST["PMSchluessel"])
{
$pmschluessel = $_POST["PMSchluessel"];
$bezeichnung = $_POST["Bezeichnung"];
$kunde = $_POST["Kunde"];
$nummer = 1; #$_POST["Nummer"];
$beschreibung = $_POST["Beschreibung"];
$statusmeldung = $_POST["Statusmeldung"];
$zieldatum = date("Y-m-d"); #$_POST["Zieldatum"];
$verantwortlich = $_POST["Verantwortlich"];
$modus = $_POST["Modus"];

  $NummerQr = mysqli_query($con, "SELECT Wert FROM Nummernkreise WHERE Name = 'Projekte' limit 1");
  $Nummer = mysqli_fetch_assoc($NummerQr);
  $Nummer = $Nummer['Wert'] + 1;
  mysqli_query($con, "UPDATE `Nummernkreise` SET `Wert`='$Nummer' WHERE `Name`='Projekte'");

  mysqli_query($con, "INSERT INTO Projekte_Kundenprojekte (PMSchluessel, Beschreibung, Statusmeldung, Zieldatum, Nummernkreis_ID, Modus_ID, Kunde_ID, Mitarbeiter_ID, Bezeichnung) VALUES ('$pmschluessel', '$beschreibung', '$statusmeldung', '$zieldatum', '$nummer', '$modus', '$kunde', '$verantwortlich', '$bezeichnung')");
  mysqli_query($con, "INSERT INTO Nummernkreise ");
  echo mysqli_error($con);
  echo "<b>Gratulation - Projekt generiert:</b>";
  echo "<br>Projektschlüssel: <b>".$_POST["PMSchluessel"]."</b>";
  echo "<br>Bezeichnung: <b>".$_POST["Bezeichnung"]."</b>";
  echo "<br>Kunde: <b>".$_POST["Kunde"]."</b>";
  echo "<br>Nummer: <b>".$_POST["Nummer"]."</b>";
  echo "<br>Beschreibung: <b>".$_POST["Beschreibung"]."</b>";
  echo "<br>Statusmeldung: <b>".$_POST["Statusmeldung"]."</b>";
  echo "<br>Verantwortlich: <b>".$_POST["Verantwortlich"]."</b>";
  echo "<br>Modus: <b>".$_POST["Modus"]."</b>";
  echo "<hr>";

}
mysqli_close($con);

<?php > ``` ?>

access a 1x1 javascript function to force your will

//ProjektNummernGeneratorAnteil
function projektnummer() {
    richtige_laenge_bezeichnung();
    var projektnummer = document.getElementById("Kunde").options[document.getElementById("Kunde").selectedIndex].text + "_" + document.getElementById("Bezeichnung").value + "_P" + document.getElementById("Nummer").value;
    document.getElementById("PMSchluessel").value = projektnummer;
}
function richtige_laenge_bezeichnung() {
    var bezeichnung = document.getElementById("Bezeichnung");
    while (bezeichnung.value.length < 6) {
        bezeichnung.value = "x" + bezeichnung.value;
    }
}

hope I could help

Manipulate DOM - Change values in database

Manipulate DOM - Change values in database

Replies

Howdy, Stranger!

Categories

DataTables

Manipulate DOM - Change values in database

Manipulate DOM - Change values in database

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables