populate select element with data dependent on selected row

mf

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!!

mf

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?

tangerine

Enable debug mode in your Editor instance.

->debug(true)

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

allan

$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

mf

Thanks for the answers! unfortunately still the error

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

allan

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

Allan

mf

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)

mf

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();

mf

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
]
] );

<?php > ``` ?>

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

allan

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

Allan