a select with several dynamic field updates

SWATswat

Hello

In order to finish my project, I realized a malfunction.
I'll need a concrete example (js + PHP) to do this (to understand the mechanics):
I have a controle table and a warehouse table with the appropriate foreinkeys
when I choose a warehouse in my select, I need the fields of my form (modal)
to be dynamically updated with information from the database of the entrepot table only when 'EDIT' is performed.
Thank you very much for your clarifications (this is my last hurdle to close my project ).

allan

What's the relationship of the entrepot table to the other two?

When you are doing an edit, which table is the primary table you are editing on?

Allan

SWATswat

hello Allan,

There is the table:

`entrepots`
id_entrepots
nom_entrepots
adresse_entrepots
ville_entrepots
codepostal_entrepots

The controles table (main)

id_controles
date_controles_realiser
date_prochain_controles
date_rdv
etat_controles
commentaire
...
fk_equipements (= id_equipements)
fk_entrepots (= id_entrepots)

and the equipments table

id_equipments
nom_equipements
groupe_equipements
periodicite_equipements

Currently, if I select another warehouse with the SELECT via EDIT, the warehouse name changes but not the other fields corresponding to warehouse.
and the entrepots table is updated with the information from the old warehouse.

example of what's happening now, entrepot table:
Warehouse1: 15 rue des Lilas 75000 PARIS
Warehouse2: 30 rue des Rose 21000 DIJON

if I select Entrepot1 in NEW, no worries, I get
Entrepot1 : 15 rue des Lilas 75000 PARIS

but if I change Entrepot2 in EDIT, I validate :
Entrepot1: 15 rue des Lilas 75000 PARIS
Warehouse2: 15 rue des Lilas 75000 PARIS

The reaction is normal since I didn't reload the right information before validating.

allan

he warehouse name changes but not the other fields corresponding to warehouse.

That suggests to me that the data for the whole row is not being returned to the client-side. Can you give me a link to a page showing the issue please? You can PM me the link if you can't make it public.

Allan

SWATswat

Hello Allan,
I sent you the link by PM
Thank you.

SWATswat

Hello Allan and everyone,

I managed to retrieve the warehouse ID with my SELECT field from the MODAL window. Every time I select a warehouse name, it returns the warehouse ID and full address.

Here's the code used PHP :

include_once( "Editor-PHP-2.0--pour_Entrepots/lib/DataTables.php" );

$idEntrepot = $_GET["entrepotID"];
 
$mysqli = new mysqli("localhost", "xxx", 'xxxxx', "entrepots_gco"); 
$mysqli->set_charset('utf8');
$data = array();
$result ="";
 
$query = "SELECT * FROM entrepots WHERE entrepots.id_entrepots = $idEntrepot";



 if ($result = $mysqli->query($query)) {


    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
        $data[] = array(
         "value" => $row['id_entrepots'].' - '.$row['adresse_entrepots'].' - '.$row['codepostal_entrepots'].' - '.$row['ville_entrepots'].' - '.$row['superficie_entrepots'].' - '.$row['annee_construction_entrepots'],
         "label" => $row['id_entrepots'].' - '.$row['adresse_entrepots'].' - '.$row['codepostal_entrepots'].' - '.$row['ville_entrepots'].' - '.$row['superficie_entrepots'].' - '.$row['annee_construction_entrepots']
        );
    }
 }
$result->free();
 
echo json_encode($data);

Get_details_entrepots.php?entrepotID=4

JSON return

[
    {
        "value": "4 - 83 rue des Champs Fleuries - 75000 - PARIS - 75000 - 1933",
        "label": "4 - 83 rue des Champs Fleuries - 75000 - PARIS - 75000 - 1933"
    }
]

Now comes the most complex part for me,
I need to replace the values of 5 fields with the JSON return in the MODAL window immediately after changing the value of my select without clicking Update and without closing the MODAL window.

Here's the poor code I've come up with, but I can't figure out how to do it.

editor.field('controles.fk_entrepots').input().on('change', function (e, d) {
        if ( !d || !d.editor ) {
            // The change was triggered by the end user rather than an auto set
            var entrepotID = editor.field('controles.fk_entrepots').val();
                console.log("on change fired");
                    if (!d) {
                    console.log("user change fired");
                    //editor.submit();
                }
                $.ajax({
                    url: "Get_details_entrepots.php",
                    data: {
                        entrepotID: entrepotID
                    },
                    dataType: 'JSON',
                    success: function (json) {
                        editor.field('entrepots.adresse_entrepots').update(json);
                    }
                });
        }
    });

The 5 fiels are :

...
{
                label: "Durée contrat :",
                name: "controles.duree_controles",
                fieldInfo: 'Chiffre ou nombre seulement'
            }, {
                label: "adresse_entrepots :",
                name: "entrepots.adresse_entrepots",
                type: "text"
            }, {
                label: "codepostal_entrepots :",
                name: "entrepots.codepostal_entrepots",
                fieldInfo: 'Nombre seulement'
            }, {
                label: "ville_entrepots :",
                name: "entrepots.ville_entrepots",
                type: "text"
            }, {
                label: "superficie_entrepots :",
                name: "entrepots.superficie_entrepots",
                fieldInfo: 'Nombre seulement'
            }, {
                label: "annee_construction_entrepots :",
                name: "entrepots.annee_construction_entrepots",
                fieldInfo: 'Année format YYYY (2024)'
            }
...

A lot of help would be welcome, thank you

allan

Don't combine the result in the JSON data - you really want it to return the value for each field:

{
  "id_entrepots": "...",
  "adresse_entrepots": "...",
  "codepostal_entrepots": "...",
  "ville_entrepots": "...",
  "superficie_entrepots": "...",
  ...
}

Then you can populate each field with the value, rather than needing to try and parse the string. It isn't a list of options you are returning - is the the values for a specific entry, so you don't need an array. Also don't call field().update() you aren't changing the list of options. You are however changing values, so you might use:

editor.field('entrepots.superficie_entrepots').val(json.superficie_entrepots);

As I mentioned in my PM you can use dependent() to do a lot of this, but this way works as well.

Allan

SWATswat

My code dependent JS :

editor.dependent("controles.fk_entrepots", function(val, data, callback) {
        var entrepotID = editor.field('controles.fk_entrepots').val();
        $.ajax({
         type: "GET",
         url: "Get_details_entrepots.php",
            data: {
            entrepotID: entrepotID
        },
        dataType: "json",
        success: function (data) {
             editor.field('entrepots.adresse_entrepots').val(data.adresse_entrepots);
             editor.field('entrepots.codepostal_entrepots').val(data.codepostal_entrepots);
             editor.field('entrepots.ville_entrepots').val(data.ville_entrepots);
             editor.field('entrepots.superficie_entrepots').val(data.superficie_entrepots);
             editor.field('entrepots.annee_construction_entrepots').val(data.annee_construction_entrepots);
             
        }
        });
        callback({});
    });

My code PHP :

ini_set('display_errors', '1');


// DataTables PHP library and database connection
include_once( "Editor-PHP-2.0--pour_Entrepots/lib/DataTables.php" );

$idEntrepot = $_GET["entrepotID"];
 
$mysqli = new mysqli("localhost", "login", 'password', "entrepots_gco"); 
$mysqli->set_charset('utf8');
//$data = array();
$result ="";
 
$query = "SELECT * FROM entrepots WHERE entrepots.id_entrepots = $idEntrepot";

if ($result = $mysqli->query($query)) {
    echo json_encode($data = $result->fetch_array(MYSQLI_ASSOC));
}

and it works perfectly !
Thanks a lot Allan!