Help with Editor cascading list syntax

Help with Editor cascading list syntax

PaulVickeryPaulVickery Posts: 11Questions: 3Answers: 0

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"

Answers

  • rf1234rf1234 Posts: 3,006Questions: 87Answers: 421

    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);
    } ),
    
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • PaulVickeryPaulVickery Posts: 11Questions: 3Answers: 0

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

Sign In or Register to comment.