Dropdown list with where clause

SWATswat

Hello

I have two tables:

1) qualite_clients
- id_qc (id table) (int)
- id_whs_qc (id warehouse) (int)
- nom_qc (name client) (varchar)

2) qualite_cofor
- id_qco (id table) (int)
- id_whs_qco (id warehouse) (int)
- nom_cofor_qco (name cofor) (varchar)
- id_clients_qco (= qualite_clients.id_qc) (int)

JAVASCRIPT page :

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "Staff_Qualite_edit_COFORparCLIENT.php",
        table: "#EditQualiteCoforClient",
        fields: [ {
                label: "WHS :",
                name: "qualite_cofor.id_whs_qco",
                def: "<?php echo $Id_WHS; ?>",
                type: "readonly"
            },{
                label: "Client :",
                name: "qualite_cofor.id_clients_qco",
                type: "select"
            }, {
                label: "Cofor :",
                name: "qualite_cofor.nom_cofor_qco"
            }
        ]
    } );



     $('#EditQualiteCoforClient').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "Staff_Qualite_edit_COFORparCLIENT.php",
            type: "POST"
        },
        serverSide: true,
        columns: [
            { data: "qualite_clients.nom_qc" },
            { data: "qualite_cofor.nom_cofor_qco" }
        ],
         select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor }
        ]
    } );
} );

PHP page :

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'qualite_cofor', 'id_qco' )
    ->fields(
        Field::inst( 'qualite_cofor.id_whs_qco' ),
        
        Field::inst( 'qualite_cofor.id_clients_qco' )
            ->options( Options::inst()
                ->table( 'qualite_clients' )
                ->value( 'id_qc' )
                ->label( 'nom_qc' )
                ->where( function ( $q ) use ( $Id_WHS ){
                    //Where pour la liste correspondant a l'entrepot
                    $q->where('qualite_clients.id_whs_qc', $Id_WHS, '=' );
                });
                
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'qualite_clients.nom_qc' ),
        
        Field::inst( 'qualite_cofor.nom_cofor_qco' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A name is required' )   
            ) )
    )   
    ->debug(true)   
    ->leftJoin( 'qualite_clients', 'qualite_clients.id_qc', '=', 'qualite_cofor.id_clients_qco' )
    ->where( 'qualite_cofor.id_whs_qco', $Id_WHS, '=' )
    ->process( $_POST )
    ->json();

I would like to have a drop-down list with the list of COFORs (qualite_cofor.nom_cofor_qco) by customer (qualite_clients.nom_qc) for a warehouse (id = $Id_WHS)

if I remove the WHERE condition from my php file :

->where( function ( $q ) use ( $Id_WHS ){
    //Where pour la liste correspondant a l'entrepot
    $q->where('qualite_clients.id_whs_qc', $Id_WHS, '=' );
});

The list works but in this list appears all the clients of all the warehouses.
if I place this clause where, I get an INTERNAL ERROR 500

I don't understand where the error is, I followed the documentation of the where clause and obviously there is something wrong but what?

Thank you for your invaluable help

allan

});

Remove that semi-colon. You have a PHP syntax error there.

For 500 errors I always put:

error_reporting(E_ALL);
ini_set('display_errors', '1');

at the top of the script to force it to send errors back to the client so you can get information such as syntax errors.

Allan

SWATswat

Hello Allan,
Thanks a lot for your help, I removed the ';' and it works perfectly.

Thanks again Allan for your patience and help.