populate select element with data dependent on selected row

populate select element with data dependent on selected row

mfmf Posts: 41Questions: 11Answers: 0

Hello all,

I'm trying to populate a select element in editor with contacts but I only want to see the contacts belonging to the company(dealer) I select and edit instead of all of them. So I have to select only the contacts with a particular dealernr, how can I get this value in the query? this is the code I have

        Field::inst( 'bezoekverslagen.dealercontacts_id' )
            ->options( Options::inst()
                ->table( 'dealercontacts, bezoekverslagen' )
                ->value( 'dealercontacts.dealercontacts_id' )
                ->label( array('dealercontacts.initials', 'dealercontacts.firstname', 'dealercontacts.lastname') )
                ->where( function ($q) {
                    $q->where( 'xstop', 0 );
                    $q ->where('dealercontacts.dealernr', 'bezoekverslagen.dealernr'); 
                })
            )
            ->validator( 'Validate::dbValues' )

It's not working this way. The dealernr is in the row I select and want to edit, when editing I don't want to see all contacts, only thos that belong with that particular dealernr. How can I get this value? I hope someone can help, Thanks!!

This question has accepted answers - jump to:

Answers

  • mfmf Posts: 41Questions: 11Answers: 0
    edited October 2017

    I also tried the following:

    $q->where( 'dealernr', 'bezoekverslagen.dealernr', '=', false );
    

    But then the column is not recognised

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bezoekverslagen.dealernr' in 'where clause'","data":[],"ipOpts":[],"cancelled":[]}
    

    I think I just don't know what to use :(

    How can I see what actual mysql query actually is produced?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

    Enable debug mode in your Editor instance.

    ->debug(true)
    

    You can then view the result in your browser's console.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    $q ->where('dealercontacts.dealernr', 'bezoekverslagen.dealernr');

    Change that to be:

    $q ->where('dealercontacts.dealernr', 'bezoekverslagen.dealernr', '=', false);
    

    The problem was that it was searching for the string bezoekverslagen.dealernr, not the column value. The false in the forth parameter will stop that from happening and make it use the column search.

    If that works, that's a clever solution! I hadn't thought of doing a join with the Options class that way.

    Allan

  • mfmf Posts: 41Questions: 11Answers: 0

    Thanks for the answers! unfortunately still the error

    Column not found: 1054 Unknown column 'bezoekverslagen.dealernr'

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Could you enable debugging as tangerine suggested and then show me the JSON return from the server, which should include the executed SQL.

    Allan

  • mfmf Posts: 41Questions: 11Answers: 0
    edited March 2018

    Sorry for the late reply but the JSON return is this:

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bezoekverslagen.dealernr' in 'where clause'","data":[],"ipOpts":[],"cancelled":[],"debugSql":[{"query":"SELECT COUNT(bezoekverslagen.bezoekverslagen_id) as 'cnt' FROM bezoekverslagen LEFT JOIN gespreksonderwerp ON gespreksonderwerp.gespreksonderwerp_id = bezoekverslagen.gespreksonderwerp_id LEFT JOIN dealers ON dealers.dealernr = bezoekverslagen.dealernr LEFT JOIN dealercontacts ON dealercontacts.dealercontacts_id = bezoekverslagen.dealercontacts_id LEFT JOIN tbl_users ON tbl_users.associate_id = bezoekverslagen.associate_id LEFT JOIN appointment ON appointment.appointment_id = bezoekverslagen.appointment_id ","bindings":[]},{"query":"SELECT COUNT(bezoekverslagen.bezoekverslagen_id) as 'cnt' FROM bezoekverslagen ","bindings":[]},{"query":"SELECT bezoekverslagen.bezoekverslagen_id as 'bezoekverslagen.bezoekverslagen_id', bezoekverslagen.beschrijving as 'bezoekverslagen.beschrijving', bezoekverslagen.afspraak as 'bezoekverslagen.afspraak', bezoekverslagen.dealernr as 'bezoekverslagen.dealernr', bezoekverslagen.appointment_id as 'bezoekverslagen.appointment_id', dealercontacts.firstname as 'dealercontacts.firstname', dealercontacts.initials as 'dealercontacts.initials', dealercontacts.tussenvoegsel as 'dealercontacts.tussenvoegsel', dealercontacts.lastname as 'dealercontacts.lastname', dealercontacts.xstop as 'dealercontacts.xstop', appointment.bezoekdatum as 'appointment.bezoekdatum', dealers.naam as 'dealers.naam', dealers.bezoek_plaats as 'dealers.bezoek_plaats', tbl_users.user_name as 'tbl_users.user_name', gespreksonderwerp.onderwerp as 'gespreksonderwerp.onderwerp', bezoekverslagen.registered as 'bezoekverslagen.registered', gespreksonderwerp.zichtbaar as 'gespreksonderwerp.zichtbaar', bezoekverslagen.associate_id as 'bezoekverslagen.associate_id', bezoekverslagen.updated_associate_id as 'bezoekverslagen.updated_associate_id', bezoekverslagen.opvolgen as 'bezoekverslagen.opvolgen', bezoekverslagen.voltooid as 'bezoekverslagen.voltooid', bezoekverslagen.gespreksonderwerp_id as 'bezoekverslagen.gespreksonderwerp_id', bezoekverslagen.dealercontacts_id as 'bezoekverslagen.dealercontacts_id' FROM bezoekverslagen LEFT JOIN gespreksonderwerp ON gespreksonderwerp.gespreksonderwerp_id = bezoekverslagen.gespreksonderwerp_id LEFT JOIN dealers ON dealers.dealernr = bezoekverslagen.dealernr LEFT JOIN dealercontacts ON dealercontacts.dealercontacts_id = bezoekverslagen.dealercontacts_id LEFT JOIN tbl_users ON tbl_users.associate_id = bezoekverslagen.associate_id LEFT JOIN appointment ON appointment.appointment_id = bezoekverslagen.appointment_id ORDER BY bezoekverslagen.appointment_id desc LIMIT 10","bindings":[]},{"query":"SELECT DISTINCT gespreksonderwerp_id as 'gespreksonderwerp_id', onderwerp as 'onderwerp' FROM gespreksonderwerp WHERE (zichtbaar = :where_1 AND gespreksonderwerp.afdeling = :where_2 )","bindings":[{"name":":where_1","value":0,"type":null},{"name":":where_2","value":"2","type":null}]},{"query":"SELECT DISTINCT dealercontacts.dealercontacts_id as 'dealercontacts.dealercontacts_id', dealercontacts.initials as 'dealercontacts.initials', dealercontacts.firstname as 'dealercontacts.firstname', dealercontacts.lastname as 'dealercontacts.lastname' FROM dealercontacts WHERE (xstop = :where_1 AND dealercontacts.dealernr = bezoekverslagen.dealernr )","bindings":[{"name":":where_1","value":0,"type":null}]}]}

    Is there another way how I can get the value "bezoekverslagen.dealernr"? The dealernr is known because its in the record I am editing, can I put it in a variable? and if so how? thanks!!

            Field::inst( 'bezoekverslagen.dealercontacts_id' )
                ->options( Options::inst()
                    ->table( 'dealercontacts' )
                    ->value( 'dealercontacts.dealercontacts_id' )
                    ->label( array('dealercontacts.initials', 'dealercontacts.firstname', 'dealercontacts.lastname') )
                    ->where( function ($q) use ($dealernr)  {
                        $q->where( 'xstop', 0 );
                        $q ->and_where('dealercontacts.dealernr', $dealernr );
                    })
                )
                ->validator( 'Validate::dbValues' )
    

    I don't know how to put bezoekverslagen.dealernr into $dealernr (I'm just a beginner)

  • mfmf Posts: 41Questions: 11Answers: 0

    This is the whole file

    <?php
    session_start();
    /*
     * Editor server script for DB table bezoekverslagen
     * Created by http://editor.datatables.net/generator
     */
    // DataTables PHP library and database connection
    include( "DataTables.php" );
    //get department of user
    include( "../*************" );
    $stmt = $db_con->prepare("SELECT afdeling FROM tbl_users WHERE user_id=:uid");
    $stmt->execute(array(":uid"=>$_SESSION['user_session']));
    $uid=$stmt->fetch(PDO::FETCH_ASSOC);
    $department = $uid['afdeling']; 
    // 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;
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'bezoekverslagen', 'bezoekverslagen_id' )
        ->fields(
            Field::inst( 'bezoekverslagen.bezoekverslagen_id' ),
            Field::inst( 'bezoekverslagen.beschrijving' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'bezoekverslagen.afspraak' ),
            Field::inst( 'bezoekverslagen.dealernr' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'bezoekverslagen.appointment_id' ),
            Field::inst( 'dealercontacts.firstname' ),
            Field::inst( 'dealercontacts.initials' ),
            Field::inst( 'dealercontacts.tussenvoegsel' ),
            Field::inst( 'dealercontacts.lastname' ),
            Field::inst( 'dealercontacts.xstop' ),
            Field::inst( 'appointment.bezoekdatum' ),
            Field::inst( 'dealers.naam' ),
            Field::inst( 'dealers.bezoek_plaats' ),
            Field::inst( 'tbl_users.user_name' ),
            Field::inst( 'gespreksonderwerp.onderwerp' ),
            Field::inst( 'bezoekverslagen.registered' )->set( Field::SET_CREATE ),
            Field::inst( 'gespreksonderwerp.zichtbaar' ),
            Field::inst( 'bezoekverslagen.associate_id' )->set( Field::SET_CREATE ),
            Field::inst( 'bezoekverslagen.updated_associate_id' )->set( Field::SET_EDIT ),
            Field::inst( 'bezoekverslagen.opvolgen' )
                ->validator( 'Validate::dateFormat', array( 'format'=>Format::DATE_ISO_8601 ) )
                ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
            Field::inst( 'bezoekverslagen.voltooid' )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return ! $val ? 0 : 1;
                } ),
            Field::inst( 'bezoekverslagen.gespreksonderwerp_id' )->validator( 'Validate::notEmpty' )
                ->options( Options::inst()
                    ->table( 'gespreksonderwerp' )
                    ->value( 'gespreksonderwerp_id' )
                    ->label( 'onderwerp')
                    ->Where( function ($q) use ($department) {
                      $q->where( 'zichtbaar', 0 );
                      $q->and_where('gespreksonderwerp.afdeling', $department );
                    })
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'bezoekverslagen.dealercontacts_id' )
                ->options( Options::inst()
                    ->table( 'dealercontacts' )
                    ->value( 'dealercontacts.dealercontacts_id' )
                    ->label( array('dealercontacts.initials', 'dealercontacts.firstname', 'dealercontacts.lastname') )
                    ->where( function ($q) use ($dealernr)  {
                        $q->where( 'xstop', 0 );
                        $q ->and_where('dealercontacts.dealernr', $dealernr );
                    })
                )
                ->validator( 'Validate::dbValues' )
        )
        ->on( 'preCreate', function ( $editor, $values ) {   
            $editor->field( 'bezoekverslagen.associate_id' )->setValue( $_SESSION['user_session'] );
            $editor->field( 'bezoekverslagen.registered' )->setValue( date("Y-m-d H:i:s") );
        } )
        ->on( 'preEdit', function ( $editor, $id, $values ) {
            if ( $values['bezoekverslagen']['associate_id'] == $_SESSION['user_session'] ) {
                $editor->field( 'bezoekverslagen.updated_associate_id' )->setValue( $_SESSION['user_session'] );
            } else { 
              return false;
            }
        } )
        ->leftJoin( 'gespreksonderwerp', 'gespreksonderwerp.gespreksonderwerp_id', '=', 'bezoekverslagen.gespreksonderwerp_id' )
        ->leftJoin( 'dealers', 'dealers.dealernr', '=', 'bezoekverslagen.dealernr' )
        ->leftJoin( 'dealercontacts', 'dealercontacts.dealercontacts_id', '=', 'bezoekverslagen.dealercontacts_id' )
        ->leftJoin( 'tbl_users', 'tbl_users.associate_id', '=', 'bezoekverslagen.associate_id' )
        ->leftJoin( 'appointment', 'appointment.appointment_id', '=', 'bezoekverslagen.appointment_id' )
        //->where('bezoekverslagen.bezoekverslagen_id', '47665', '>=' )
        //->where('bezoekverslagen.voltooid', 0) 
        ->debug(true)
        ->process( $_POST )
        ->json();
    
  • mfmf Posts: 41Questions: 11Answers: 0
    edited March 2018

    I solved it using the dependent() option.

    For anyone who likes to know

    In javascript

    editor.dependent( 'bezoekverslagen.dealernr', 'contacts_dynamic.php' );
    

    and in contacts_dynamic.php

    <?php
    include_once( "dbconfig.php" );
    $stmt = $db_con->prepare("SELECT dealers_dealercontacts.dealercontacts_id as value, dealercontacts.lastname as label FROM dealers_dealercontacts 
    LEFT JOIN dealercontacts ON dealercontacts.dealercontacts_id = dealers_dealercontacts.dealercontacts_id WHERE dealers_dealercontacts.dealernr=:dlr");
    $stmt->execute(array(":dlr"=>$_REQUEST['values']['bezoekverslagen.dealernr']));
    $contacts=$stmt->fetchAll();
    echo json_encode( [
        'options' => [
            'bezoekverslagen.dealercontacts_id' => $contacts
        ]
    ] );
    ?>
    

    I know I can also use
    // DataTables PHP library and database connection
    include( "DataTables.php" );

    Instead of my own, but I simply couldn't figure out how to do the query as I need it, I need working examples for left joins etc

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thanks for posting back - sorry I didn't manage to get back to you before you resolved it yourself!

    Allan

This discussion has been closed.