Dates in ->where() clause setting

fabioberetta

Dear all,

I am trying to se a where clause in PHP Editor class to retrieve logs based on dates.

Essentially I need to get all logs between 2 dates. I would like to implement it as follows:

• set 2 boundary dates in case no dates are submitted
• if dates are submited via post replace the boundary dates with dates submitted
• process

include( "../vendor/Editor-1.5.0/php/DataTables.php" );

// start session to get session user data
session_start();

$_from_date = '1970-1-1';
$_to_date = '2070-1-1';

if (isset($_POST['from_date'])) {
    $_from_date = $_POST['from_date'];
    unset( $_POST['from_date'] );
}

if (isset($_POST['to_date'])) {
    $_to_date = $_POST['to_date'];
    unset( $_POST['to_date'] );
}


// 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, 'log' )
    ->fields(
        Field::inst( 'log.id' ),
        Field::inst( 'log.type_id' ),
        Field::inst( 'log.description' ),
        Field::inst( 'log.value' ),
        Field::inst( 'log.log_date_time' ),
        Field::inst( 'log.device_date_time' ),
        Field::inst( 'log.child_id' ),
        Field::inst( 'child.account_id' ),
        Field::inst( 'log.photo_id' ) )
    ->where( 'child.account_id', $_SESSION['account_id'] )
    ->where( 'log.device_date_time', $_from_date, '=>' )
    ->where( 'log.device_date_time', $_to_date, '<=' )
    ->leftJoin( 'child', 'child.id', '=', 'log.child_id' )
    ->process( $_POST )
    ->json();

I receive an error related to the fact that the where clause in SQL fails to understand the dates... How shluld I inser the dates in the where clause?

ty
f

allan

What is the date format that you are submitting for the from and to dates? Also, what is the error message received?

Thanks,
Allan

fabioberetta

Hi Allan,

I food out the problem, the error message was a bit chriptic...

->where( 'log.device_date_time', $_from_date, '=>' )

The operand is >= not => !!

My fault!

ty

allan

Heh - easy done. I completely didn't see that. Good to hear you've got it working now :-)

Allan