Editor - construct WHERE clause only if variable is sent

asle

Hi, I need some WHERE clauses for my SQL so after suggestion I moved to the EDITOR libraries. I have no problems with this code and several WHERE conditions. My server script handles FROM_date and TO_date like this:

Editor::inst( $db, 'myDB.my_table' )
    ->fields( 
        Field::inst( 'garnummer' ),
        Field::inst( 'ordrenummer' ),
        Field::inst( 'montdato' ),
        Field::inst( 'testrun' ),
        Field::inst( 'trykktest_nitro' ),
        Field::inst( 'garanti_ok' ),
        Field::inst( 'reg_dato' )
        )

    ->where( 'mont_id', $_POST['mont_id'])  //works fine
    ->where( 'ekstern_reg', 1) //works fine
    ->where( 'garanti_ok', 1, '!=') //works fine

// This is where the dates are not always sent
    //->where( 'reg_dato', $startDate, '>=')
    //->where( 'reg_dato', $endDate, '<=') 

/* // I tried this but does not work
    ->where( function ( $q ) use ( $startDate ) {
    if(isset($startDate)){
        $q->where( 'reg_dato', $startDate, '>=' );
        }
} ) */
    ->process( $_POST )
    ->debug(true)
    ->json();

So the fields I always send are handled fine. In my js I send the values.

 ajax: {
       url: "/editor-php/controllers/not_finished.php",
       type: "POST",
       data: function ( d ) {
       d.mont_id = kundenr; 
       d.ekstern_reg = 1;
       d.startDate = moment($('#min').val()).format('YYYY-MM-DD');
       d.endDate = moment($('#max').val()).format('YYYY-MM-DD');
             }
             },

But if the user does not fill in start and end date, how do I ignore this in the server script (not_finished.php)?

I tried this code but I get invalid json:

    ->where( function ( $q ) use ( $startDate ) {
        if(isset($startDate)){
            $q->where( 'reg_dato', $startDate, '>=' );
            }
    } ) 

So how do I set a WHERE condition only when there is a value sent? And how can I check that it is a valid date? Do I do this in the datatables initialisation or in the server script?

allan

I assume somewhere you are doing this?:

$startDate = $_POST['startDate'];

Really that should have a isset check around it:

$startDate = isset($_POST['startDate'])
  ? $_POST['startDate']
  : null;

Then you would do if ( $startDate !== null ) ....

Allan