IN clause syntax

Datagaard

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

<?php > ``` 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();

<?php > ``` ?>

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.

Datagaard

Ah ha!

Answered my own question.

Change the $orgList to

$orgList = "(0,1)";