Where Clause with conditions in an array

daniegloy

HI
I have an array of Stand data ['All','Local','Auction','Wholesale']

My table data is access with ajax.

How do i set the where clause for unknown array elements.

<?php
session_start(); 
@$CompanyId = $_SESSION['ADASOFTCLIENT_CompanyId'];
@$userCompanyName = $_SESSION['ADASOFTCLIENT_userCompanyName'];
@$username =  $_SESSION['ADASOFTCLIENT_userName'];
@$usersurname =  $_SESSION['ADASOFTCLIENT_userSurname'];
@$loggedin =  $_SESSION['ADASOFTCLIENT_loggedin'];
@$UserLevel =  $_SESSION['ADASOFTCLIENT_level'];
@$UserStands = $_SESSION['ADASOFTCLIENT_stands'];

if(!isset($username) && !isset($loggedin) && (!$loggedin == true) && isset($CompanyId)){
  header('Location:login.php');
} 


@$CompanyId = $CompanyId."_stock";

@$CompanyId = strtolower($CompanyId); 

  include( "./Editor/php/DataTables.php" );

 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
 
// Build our Editor instance and process the data coming from _POST


 
Editor::inst( $db, $CompanyId)
    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'StockNr' ),
        Field::inst( 'DOS' ),
        Field::inst( 'DYOS' ),
        Field::inst( 'REGNR' ),
        Field::inst( 'OLDREG' ),
        Field::inst( 'NatisNr' ),
        Field::inst( 'TYPE' ),
        Field::inst( 'MAKE' ),
        Field::inst( 'MODEL' ),
        Field::inst( 'MMCODE' ),
        Field::inst( 'VEHICLE_CONDITION' ),
        Field::inst( 'COLOUR' ),
        Field::inst( 'YEAR' ),
        Field::inst( 'REG_MONTH' ),
        Field::inst( 'KILOS' ),
        Field::inst( 'CLASSIFICATION' ),
        Field::inst( 'VIN_NO' ),
        Field::inst( 'ENGINENR' ),
        Field::inst( 'Status' ) ,
        Field::inst( 'Stand' ) ,
        Field::inst( 'OwnerSuppAccCode' ), 
        Field::inst( 'keynumber' ) 
        
 
    
    
  
 

    )
    ->where( function ( $q ) {
    
    ??????????  Array [ 'Local'   OR  'wholesal' OR .......]   = to Stand field
    
    
} )
    
    ->process( $_POST )
    ->json();

rf1234

This is an example from my own coding - more complex than what you need but you'll recognize what you can use of this stuff: I have a string called $_SESSION['govSearchString']. I split it into an array and then use the array elements in my WHERE clause. All you need to know is the maximum number of potential array elements. Let's assume the array has maximum 4 elements but only two of them are actually provided in reality. In this case you fill your first two where clause fields with those values and the other two "or_where" fields are blank (or whatever is appropriate for you). That works.

The WHERE clause:

-->where( function ( $q ) {
        if (! isset($_SESSION['masterDataId']) ) {
            $q  ->where( function ( $r ) {
                    $govSearchString = filter_var($_SESSION['govSearchString']);
                    $split = splitQueryString($govSearchString);

                    $r  ->where( 'gov.name', $split[5], 'LIKE' );
                    $r  ->or_where( 'gov.regional_8', $split[6], 'LIKE' );
                    $r  ->or_where( 'gov.regional_12', $split[6], 'LIKE');
                } )
                ->where( function ( $s ) {
                    $govSearchString = filter_var($_SESSION['govSearchString']);
                    $split = splitQueryString($govSearchString);

                    $s  ->where( 'gov.type', $split[0] );
                    $s  ->or_where( 'gov.type', $split[1] );
                    $s  ->or_where( 'gov.type', $split[2] );
                    $s  ->or_where( 'gov.type', $split[3] );
                    $s  ->or_where( 'gov.type', $split[4] );
                } );
        } else {
            $q  ->where( 'gov.id',  
                '( SELECT DISTINCT gov.id    
                    FROM user, govdept_has_user, govdept, gov   
                    WHERE user.id = :id                             AND  
                          user.id = govdept_has_user.user_id        AND  
                          govdept_has_user.govdept_id = govdept.id  AND  
                          govdept.gov_id = gov.id  
                    ORDER BY gov.name ASC  
                    )', 'IN', false);
            $q  ->bind( ':id', $_SESSION['masterDataId'] );
        }
    } )

the function splitQueryString called in the where clause:

function splitQueryString(&$query) {
    // Input string consists of the following:
    //1. number of search parms (0 - 4)
    //2. search parms (0 - 4 digits)
    //3. search word - sometimes including keys separated by _
    
    //returns: array with 7 entries, 1 - 5: 5 digits of search parms,
    // 6: search string for LIKE name, 7: search string for like regional_12
    // or regional_8
    
    $length = (int)substr($query, 0, 1);    
    $ix; $y; $args = [];
    
    //if no organization type was entered we're still searching for everything
    for ($ix = 0; $ix <= 4; $ix++) {
        $args[$ix] = (string)$ix;
    }
    
    $ix = 0;
    while ($ix < $length) {
        $y = $ix + 1;
        $args[$ix] = substr($query, $y, 1);
        while ($y < 5) {
            $args[$y] = $args[$ix];
            $y++;             
        }
        $ix++;
    }
    
    $organization = substr($query, ($length+1) );
    if (substr($organization, 0, 1) == '_') {
       $organization = substr($organization, 1);
    }
    $orgArray = explode('_', $organization);
    
    $args[5] = "%" . $orgArray[0] . "%";
    $args[6] = $orgArray[0] . "%";
    
    return $args;
}

daniegloy

Looks good.. But the problem is that i will never know the maximum amount of elements in the array

daniegloy

WOuld it be possible to add my own method to Query.php..... -> whereString()

daniegloy

Is there a way to input sql string....... ->where(' Stand = "All" || Stand = "Wholesale"') ?
Options

daniegloy

I found this in the documentation where( array('fieldName', ...), array('value', ...) );

rf1234

$yourQueryArray = join("','",$yourInputArray); 

$editor->where( function ( $q ) use ( $yourQueryArray) {
    $q->where( 'yourField', $yourQueryArray, 'IN');
} );

that should become a where clause like this:
WHERE yourField IN ("value 1", "value 2", .... ) 

Something like this could work. But it might be harmful to SQL injections. Please check whether it is being bound by Data Tables. You'll find something here:
https://editor.datatables.net/manual/php/conditions

rf1234

Another idea: Don't know where you get your array from. If it is being selected from a database as well you can pass it in via a subselect like this:

$editor->where( function ( $q ) {
    $q->where( 'location', '(SELECT id FROM cities WHERE name LIKE :city)', 'IN', false );
    $q->bind( ':city', '%'.$_POST['city'].'%' );
} );

daniegloy

Here is what i had to do

@$UserStands = $_SESSION['ADASOFTCLIENT_stands'];


$UserStandsArray = explode(',', $UserStands);

and the plug in Query.php file

    public function where ( $key, $value=null, $op="=", $bind=true )
    {
        if ( $key === null ) {
            return $this;
        }
        else if ( is_callable($key) && is_object($key) ) { // is a closure
            $this->_where_group( true, 'AND' );
            $key( $this );
            $this->_where_group( false, 'OR' );
        }
        else if ( !is_array($key) && is_array($value) ) {
            for ( $i=0 ; $i<count($value) ; $i++ ) {
                $this->where( $key, $value[$i], $op, $bind );
            }
        }
        else {
            $this->_where( $key, $value, 'OR', $op, $bind ); // Changed it from AND to OR
        }

        return $this;
    }

daniegloy

    ->where("Stand",$UserStandsArray)