Editor - construct WHERE clause only if variable is sent

Editor - construct WHERE clause only if variable is sent

asleasle Posts: 96Questions: 28Answers: 0
edited September 2022 in Free community support

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

Sign In or Register to comment.