What is the correct format for where with in and or?

rmeetin

In my MySQL table field the data may look something like:

99999
88888:33030
33030:66666:11111:10001
14701
10000:99999
10000
77777

Always 5 digits. A simple where:

->where( 'box_id','66666','=' ) // works fine

I need to add an or so I try:

->where( 'box_id','66666','=' )
->or_where( 'box_id','12345','=' ) 

When I add the or_where it causes the table to error out with the standard error popup message and not load any data.

Another way to solve the problem might be with a mysql IN as in:

where box_id in (66666,12345)

How would you set up datatables IN (or NOT IN) syntax?

tangerine

https://datatables.net/forums/discussion/43542

rmeetin

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

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

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

In the real table all the ids, box_id's will have 5 digits, not like what I just tested.

allan

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

Thanks,
Allan

rmeetin

<?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

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

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.