where condtition with arrays - serverside

where condtition with arrays - serverside

svendbersvendber Posts: 27Questions: 8Answers: 0

Hi!

I've got the following code.

include(dirname(__FILE__)."/../Editor-PHP-1.9.2/lib/DataTables.php");

// Alias Editor classes so they are easy to use
use DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Validate,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\ValidateOptions;

$my_id=$_POST['my_id'];
$timepoolid=$_POST['timepool_id'];

//$yourQueryArray = join(',',$timepoolid);
// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst($db, 'crapper_vulnerability', 'crapper_vulnerability.id')
//    ->order('ip')

    ->fields(

        Field::inst('crapper_vulnerability.outputtool'),
        Field::inst('crapper_vulnerability.testtypeid'),
        Field::inst( 'crapper_vulnerability.toolid' ),
        Field::inst( 'crapper_vulnerability.scanid' ),
        Field::inst( 'crapper_vulnerability.fp' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'crapper_vulnerability.cvss_temp' ),
        Field::inst( 'crapper_vulnerability.cvss_env' ),
        Field::inst( 'crapper_vulnerability.cvss_base_new' ),
        Field::inst( 'crapper_vulnerability.not_in_report' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'crapper_vulnerability.ip' ),
        Field::inst( 'crapper_vulnerability.mac' ),
        Field::inst( 'crapper_vulnerability.netbiosname' ),
        Field::inst( 'crapper_vulnerability.os' ),
        Field::inst( 'crapper_vulnerability.start' ),
        Field::inst( 'crapper_vulnerability.stop' ),
        Field::inst( 'crapper_vulnerability.plugin_id' ),
        Field::inst( 'crapper_vulnerability.plugin_name' ),
        Field::inst( 'crapper_vulnerability.port' ),
        Field::inst( 'crapper_vulnerability.protocol' ),
        Field::inst( 'crapper_vulnerability.cve' ),
        Field::inst( 'crapper_vulnerability.cvss_base_score' ),
        Field::inst( 'crapper_vulnerability.service_name' ),
        Field::inst( 'crapper_vulnerability.description' ),
        Field::inst( 'crapper_vulnerability.solution' ),
        Field::inst( 'crapper_vulnerability.kundenr' ),
        Field::inst( 'crapper_vulnerability.rapport' ),
        Field::inst( 'crapper_vulnerability.filename' ),
        Field::inst( 'crapper_vulnerability.tp_key' ),
        Field::inst( 'crapper_vulnerability.plugin_output' ),
        Field::inst( 'crapper_vulnerability.timepool' ),
        Field::inst( 'crapper_vulnerability.id' )

    )
    ->where('crapper_vulnerability.kundenr', $my_id)


    ->where( function ( $q ) use ( $timepoolid) {
        $q->where( 'crapper_vulnerability.timepool', '(SELECT crapper_vulnerability.timepool FROM crapper_vulnerability WHERE crapper_vulnerability.timepool LIKE :timepool)', 'IN', false);
        $q->bind( ':timepool', '%'.$_POST['timepool_id'].'%' );}
)

    ->process($_POST)
    ->debug(true)
    ->json();

I'm trying to use an array $timepool in a where condition, but I'm getting the following error:

Notice: Array to string conversion

How do I resolve this?

Kind regards,
Svendber

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,990Questions: 87Answers: 421

    Can't find $timepool in your code. But you seem to want to use $timepoolid in your where condition. But you are effectively not using it there. Instead you use $_POST['timepool_id'].

    Maybe you check this first?!

  • svendbersvendber Posts: 27Questions: 8Answers: 0

    @rf1234
    Ahh should've looked through my code before posting.. Made some edits before posting which I should have changed back..

    I've actually tried your solutions in this post https://datatables.net/forums/discussion/comment/112295/#Comment_112295

    However it still didn't work. If the array only has one entry it works, but with several it doesn't.

      ->where( function ( $q ) use ( $timepoolid) {
            $q->where( 'crapper_vulnerability.timepool', $timepoolid, 'IN');
        }
        )
    

    Trying to use a WHERE-IN clause to loop through the array!

    Hope you can help!

  • rf1234rf1234 Posts: 2,990Questions: 87Answers: 421

    SQL is decades older than PHP and the WWW. If you want to use PHP with SQL you need to deliver what SQL expects. You have two choices here: You can either use a subselect and have SQL return the array you want to use in your "IN" clause. Or if you must use a PHP array you need to transform it into what SQL understands.

    Let's assume $timepoolid is the PHP array you want to use with SQL.

    To transform it for SQL you need to transform it to what SQL expects in an IN statement and that is a comma separated string of values with brackets around them.

    ->where( function ( $q ) use ( $timepoolid) {
         $SqlArray = '(' . implode(",", $timepoolid) . ')'; 
          $q->where( 'crapper_vulnerability.timepool', $SqlArray , 'IN', false);
      }
    
  • rf1234rf1234 Posts: 2,990Questions: 87Answers: 421
    edited January 2020

    Forgot an important detail. SQL does not like empty arrays. If your PHP array is empty SQL would need to process WHERE .... IN (). This won't work. You need to make sure that there is at least one value inside the brackets. If you are using auto-increment ids you could use 0 for this because it won't hurt since it doesn't exist as an auto-increment id. Like this for example:

    if ($timepoolid == []) {
       $timepooldid[] = 0;
    }
    $SqlArray = '(' . implode(",", $timepoolid) . ')';;
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,470 Site admin
    Answer ✓

    Hi both,

    Editor's PHP libraries have a where_in method that can be used for exactly this sort of thing.

    $q->where( 'crapper_vulnerability.timepool', $timepoolid);
    

    Allan

  • svendbersvendber Posts: 27Questions: 8Answers: 0

    Didn't see you replay @allan !
    It works perfectly with where_in method.

    This worked for me for any1 wondering.

    ->where( function ( $q ) use ( $timepoolid) {
            $q->where_in( 'crapper_vulnerability.timepool', $timepoolid, 'AND');
        }
        )
    
This discussion has been closed.