Answers

• tangerine Posts: 3,365Questions: 39Answers: 395

  August 2019

  https://datatables.net/forums/discussion/43542
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019 edited August 2019

  Back to the or_where, a common mysql query would look like:

  select * from widgets where widget_size = 'large' or widget_size = 'medium'
  

  or perhaps

  select * from widgets where (widget_size = 'large' or widget_size = 'medium') AND ...
  

  if there are other conditions.

  Regarding the "IN" clause I saw that other post before I posted the question but didn't understand what it was conveying.
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019

  Just to see if the IN works I tried the syntax referenced in the post, 43542.

  ->where( function ( $q ) {
    $q->where( 'boxid', '(10000,2,3)', 'IN', false );
  } )
  

  also tried:

  ->where( function ( $q ) {
    $q->where( 'boxid', '(10000,2,3)', 'IN' );
  } )
  

  In both cases it failed with a popup error like:

  DataTables warning: table id=example - 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 MariaDB server version for the right syntax to use near ''(10000,2,3)'' at line 1
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019

  Oops my bad,

  $q->where( 'box_id', '(1000,6,5)', 'IN', false );

  This works. I missed the underscore in box_id. That leaves getting the OR format working.
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019

  In the real table all the ids, box_id's will have 5 digits, not like what I just tested.
• allan Posts: 64,390Questions: 1Answers: 10,629 Site admin

  August 2019

  Can you show me your PHP code (the full Editor instance) when you are using the or_where() method please?

  Thanks,
  Allan
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019 edited August 2019

  <?php
  include ("../../config.php");
  include( "../assets/datatables-editor/php/DataTables.php" );
  use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
  
  Editor::inst( $db, 'field_values' )
    ->fields(
      Field::inst( 'title' )->validator( 'Validate::notEmpty' ),
      Field::inst( 'field_values.box_id' ),
      Field::inst( 'publish' ),
      Field::inst( 'introtext' ),
      Field::inst( 'id' ),
      Field::inst( 'box_id' ),
      Field::inst( 'rowOrder' )->validator( 'Validate::numeric' )
    )
  
    ->where( 'box_id','99999','=' )
    ->or_where( 'box_id','10006','=' )
  
    ->on( 'preCreate', function ( $editor, $values ) {
      $editor->db()
        ->query( 'update', 'field_values' )
        ->set( 'rowOrder', 'rowOrder+1', false )
        ->where( 'rowOrder', $values['rowOrder'], '>=' )
        ->exec();
    } )
    ->on( 'preRemove', function ( $editor, $id, $values ) {
      $order = $editor->db()
        ->select( 'field_values', 'rowOrder', array('id' => $id) )
        ->fetch();
      $editor->db()
        ->query( 'update', 'field_values' )
        ->set( 'rowOrder', 'rowOrder-1', false )
        ->where( 'rowOrder', $order['rowOrder'], '>' )
        ->exec();
    } )
    ->process( $_POST )
    ->json();
  
• allan Posts: 64,390Questions: 1Answers: 10,629 Site admin

  August 2019 Answer ✓

  Right - there is no Editor->or_where() method (the API documentation is here). You need to do this instead:

  ->where( function ($q) {
    $q->where( 'box_id','99999','=' );
    $q->or_where( 'box_id','10006','=' );
  } )
  

  Allan
• rmeetin Posts: 107Questions: 27Answers: 2

  August 2019

  I get basic php/mysql but api documentation especially javascript is a challenge. I do much better mirroring directly off examples like your examples page, then making incremental changes. I'm sure that I'm not alone in my skills. With the above usage example I have it working. Tx.