Hi,

I have an editor that works fine for a single value parameter in a select list, but when I attempt to use more than one value with an IN clause I get an SQL Error.

Here is the php code that works for one value:

<?php

/*
 * Editor server script for DB table assetdisposaltype
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

$orgList = 0;
// 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,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'assetdisposaltype', 'assetdisposaltype.disposaltype' )
    ->fields(
        Field::inst( 'assetdisposaltype.organisationid' )
            ->options( Options:: inst()
                ->table( 'organisation' )
                ->value( 'organisationid' )
                ->label( 'orgname' )
                ->where( function ($q) use ( $orgList ) {
                    $q->where( "organisationid", $orgList );
                } )
            )
            ->validator( 'Validate::dbValues' ),            
        Field::inst( 'organisation.orgname' ),
        Field::inst( 'organisation.inactive'),
        Field::inst( 'assetdisposaltype.description' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A Description is required') ) )
            ->validator( function ( $val, $data, $field, $host ) {
                return strlen( $val ) > 50 ?
                    'Description length must be 50 Characters or less':
                    true;
            }),
        Field::inst( 'assetdisposaltype.isdeleted' )
            ->setFormatter(function ($val, $data, $opts) {
                return ! $val ? 0 : 1;  
        })
    )
    ->leftJoin( 'organisation', 'organisation.organisationid', '=', 'assetdisposaltype.organisationid')
    ->where (function ( $q ) use ( $orgList ) {
        $q->where('assetdisposaltype.organisationid', $orgList );   
    } )
    ->process( $_POST )
    ->json();
?>

If I want multiple values I tried this:

<?php

/*
 * Editor server script for DB table assetdisposaltype
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

$orgList = array(0,1);
// 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,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'assetdisposaltype', 'assetdisposaltype.disposaltype' )
    ->fields(
        Field::inst( 'assetdisposaltype.organisationid' )
            ->options( Options:: inst()
                ->table( 'organisation' )
                ->value( 'organisationid' )
                ->label( 'orgname' )
                ->where( function ($q) use ( $orgList ) {
                    $q->where( "organisationid", $orgList, "IN", false );
                } )
            )
            ->validator( 'Validate::dbValues' ),            
        Field::inst( 'organisation.orgname' ),
        Field::inst( 'organisation.inactive'),
        Field::inst( 'assetdisposaltype.description' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A Description is required') ) )
            ->validator( function ( $val, $data, $field, $host ) {
                return strlen( $val ) > 50 ?
                    'Description length must be 50 Characters or less':
                    true;
            }),
        Field::inst( 'assetdisposaltype.isdeleted' )
            ->setFormatter(function ($val, $data, $opts) {
                return ! $val ? 0 : 1;  
        })
    )
    ->leftJoin( 'organisation', 'organisation.organisationid', '=', 'assetdisposaltype.organisationid')
    ->where (function ( $q ) use ( $orgList ) {
        $q->where('assetdisposaltype.organisationid', $orgList, 'IN', false );
    } )
    ->process( $_POST )
    ->json();
?>

This is the error I receive:

error: An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 AND assetdisposaltype.organisationid IN 1' at line 1

I had to make $orgList an array, as setting $orgList = (0,1) gave me this error:

Parse error: syntax error, unexpected ',' in C:\Apache24\htdocs\Temp\EQAS\assetdisposaltype\php\table.assetdisposaltype.php on line 11

Thanks in advance.