Where Clause with conditions in an array

Where Clause with conditions in an array

daniegloydaniegloy Posts: 35Questions: 12Answers: 5

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();

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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;
    }
    
  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5

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

  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5
    edited June 2017

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

  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5

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

  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited June 2017
    $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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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'].'%' );
    } );
    
  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5

    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;
        }
    
    
    
  • daniegloydaniegloy Posts: 35Questions: 12Answers: 5
        ->where("Stand",$UserStandsArray)
    
This discussion has been closed.