Answers

• Gargiucn Posts: 104Questions: 29Answers: 0

  May 2019

  I believe I have solved it:

  Field::inst( 'pagamenti.pag_idcontr' )
     ->options( function(){
        global $db;
          return $db->sql( 'SELECT contratti.con_id as value, 
             CONCAT(contratti.con_num, contratti.con_sigla, " ",clienti.cli_ragsoc) as 
             label FROM clienti INNER JOIN contratti ON clienti.cli_id' )
            ->fetchAll();
      } )                
      ->validator( Validate::dbValues()
  

  I hope he can help someone ...
  Giuseppe
• allan Posts: 61,667Questions: 1Answers: 10,096 Site admin

  May 2019 Answer ✓

  Hi Giuseppe,

  Perfect - thanks for posting your solution. That looks good to me. You might be able to do function () use ($db) { rather than accessing it from global scope, but that's the only thing I'd change.

  Allan
• Gargiucn Posts: 104Questions: 29Answers: 0

  May 2019

  I thought I had done a good job, the select works fine on its own but within my program it generates the following error:

  <b>Fatal error</b>:  Uncaught Error: Call to a member function table() on null in C:\inetpub\wwwroot\test\php\Editor\Validate.php:1026
  Stack trace:
  #0 C:\inetpub\wwwroot\test\php\Editor\Field.php(690): DataTables\Editor\Validate::DataTables\Editor\{closure}('1', Array, Object(DataTables\Editor\Field), Array)
  #1 C:\inetpub\wwwroot\test\php\Editor.php(814): DataTables\Editor\Field-&gt;validate(Array, Object(DataTables\Editor), '0')
  #2 C:\inetpub\wwwroot\test\php\Editor.php(996): DataTables\Editor-&gt;validate(Array, Array)
  #3 C:\inetpub\wwwroot\test\php\Editor.php(683): DataTables\Editor-&gt;_process(Array)
  #4 C:\inetpub\wwwroot\test\php\table-pagamenti.php(76): DataTables\Editor-&gt;process(Array)
  #5 {main}
    thrown in <b>C:\inetpub\wwwroot\test\php\Editor\Validate.php</b> on line <b>1026</b>
  
  

  This is the php script:

  <?php
  
  include( "DataTables.php" );
  use
      DataTables\Editor,
      DataTables\Editor\Field,
      DataTables\Editor\Format,
      DataTables\Editor\Mjoin,
      DataTables\Editor\Options,
      DataTables\Editor\Upload,
      DataTables\Editor\Validate,
      DataTables\Editor\ValidateOptions;
  
  Editor::inst( $db, 'pagamenti', 'pag_id' )
      ->fields(               
      /*
          ********** SCRIPT THATS WORKS **********
          Field::inst( 'pagamenti.pag_idcontr' )
              ->options( Options::inst()      
                  ->table( 'contratti' )
                  ->value( 'con_id' )
                  ->label( array('con_num', 'con_sigla','con_importo') )
                  ->render( function ( $row ) {
                      return $row['con_num'].''.$row['con_sigla'].' - Importo contratto €'.$row['con_importo'];
                  } )
                  ->order( 'con_sigla DESC, con_num DESC ' )
                  ->where( function ($q) {
                      $q->where( function ( $r ) {
                          if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                              $r->where( 'contratti.con_user',$_SESSION['id'] );
                          }else{
                              $r->where( 'contratti.con_id', $_POST['idcontratto'] );
                          }
                      } );
                  } )             
              )
              ->validator( Validate::dbValues() ),    
      */  
          /********** SCRIPT THAT GENERATES ERROR **********/     
          Field::inst( 'pagamenti.pag_idcontr' )
              ->options( function() use ($db){                
                  $select = 'SELECT contratti.con_id as value, 
                      CONCAT(contratti.con_num,contratti.con_sigla," ",clienti.cli_ragsoc) as label 
                      FROM contratti LEFT JOIN clienti ON clienti.cli_id = contratti.con_cli  
                      WHERE contratti.con_user='.$_SESSION['id'].' AND contratti.con_chiuso=0';   
                      if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                          //nothing
                      }else{
                          $select.=' AND contratti.con_id='.$_POST['idcontratto'].'';
                      }   
                  return $db->sql( $select )->fetchAll();
              } ) 
              ->validator( Validate::dbValues() ),    
          /********** END **********/ 
      
          //Field::inst( 'contratti.con_num' ),
          //Field::inst( 'contratti.con_sigla' ),
          
          Field::inst( 'pagamenti.pag_user' ),
          Field::inst( 'pagamenti.pag_importo' )  
          Field::inst( 'pagamenti.pag_data' ) 
  
          ->on( 'preCreate', function ( $editor, $values ) {
              $editor
                  ->field( 'pagamenti.pag_user' )
                  ->setValue( $_SESSION['id'] );
          } )         
          ->leftJoin( 'contratti', 'con_id', '=', 'pagamenti.pag_idcontr' )   
          ->where( function ($q) {
              if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                  $q->where( 'pagamenti.pag_user',$_SESSION['id'] );
              }else{
                  $q->where( 'pagamenti.pag_idcontr', $_POST['idcontratto'] );
              }
          } )     
          ->process( $_POST )
          ->json();
  
  ?>  
  
  

  Sigh...
• allan Posts: 61,667Questions: 1Answers: 10,096 Site admin

  May 2019

  That's this line.

  Its throwing an error because it can't get the information needed for the validator from the Options class.

  What you can do is pass in the table name and column to check against for the validation using the options for the dbValues validation method.

  Allan