where condtition with arrays - serverside

svendber

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

rf1234

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

svendber

@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!

rf1234

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

rf1234

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) . ')';;

allan

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

svendber

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');
    }
    )