Help with Editor cascading list syntax

PaulVickery

Please can someone assist me. I have successfully got the example on the blog https://datatables.net/blog/2017-09-01 working, but I am now trying to add another parameter in the “where” clause.

Using the example below from the blog:

include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );
 
$countries = $db
    ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
    ->fetchAll();
 
echo json_encode( [
    'options' => [
        'country' => $countries
    ]
] );

I want to restrict the cascading Country list by have an “active” field so that I can ‘remove’ the options in the list , ie something like

[active => “Y” ].

I can get the above to work on its own but cannot combine it with

['continent' => $_REQUEST['values']['continent']]

Please can someone assist me with the syntax for "and where"

rf1234

If you replace the "select" method with Editor's "raw" method you are much more flexible. You can use regular SQL as you are probably used to.

Here is an example from a getFormatter:

->getFormatter( function($val, $data, $opts) use ( $db ){
    $stmt = ('SELECT b.linked_ctr_id, a.serial, a.ctr_name
                FROM ctr a  
          INNER JOIN ctr_has_ctr b ON a.id = b.linked_ctr_id
               WHERE b.ctr_id = :ctr_id 
            ORDER BY 1 ASC');  
    $result = $db ->raw()
                  ->bind(':ctr_id',$val)
                  ->exec($stmt);
    return $result->fetchAll(PDO::FETCH_ASSOC);
} ),

allan

With the Database->select method, the third parameter is an array which is used for the condition. So you could use:

$countries = $db
  ->select(
    'country',
    ['id as value', 'name as label'],
    [
      'continent' => $_REQUEST['values']['continent'],
      'active' => 'Y'
    ]
  )
  ->fetchAll();

Allan

PaulVickery

Thank you both very much for your prompt responses. Alan, I have implemented your solution and it works perfectly, thank you.