Timestamp Where Clause

notyou61

I am trying to filter a datagrid for those records which unixtime stamp (tblActions.actionTimeStamp) occurs today. This is done by using the php where clause (->where( $key = "DATE(tblActions.actionTimeStamp)", $value = "CURDATE()", $op = '=' )). The code is as follows:

        // Obtain Action Grid
        if(isset($_GET['gridNumber']) && $_GET['gridNumber']==2){ 
            // Fields
            Editor::inst( $db, 'tblActions', 'actionID' )
                ->fields(
                    Field::inst( 'tblActions.actionID' ),
                    Field::inst( 'tblActions.actionTime' )
                        ->getFormatter( function ($val) {
                            return date( 'D, d M Y, h:m:s a', $val );
                    }),
                    Field::inst( 'tblActions.actionTimeStamp' ),
                    Field::inst( 'tblActions.actionTaken' ),
                    Field::inst( 'tblActions.actionUserID' ),
                    Field::inst( 'tblUsers.userID' ),
                    Field::inst( 'tblUsers.userHonorific' ),
                    Field::inst( 'tblUsers.userFirstName' ),
                    Field::inst( 'tblUsers.userLastName' )
                )
                // Where
                ->where( $key = "DATE(tblActions.actionTimeStamp)", $value = "CURDATE()", $op = '=' )
                // Join Query
                ->leftJoin( 'tblUsers', 'tblUsers.userID', '=', 'tblActions.actionUserID' )
                ->process($_POST)
                ->json();
        }

This has worked use Datatables however I think the "CURDATE()" function can not be used with Editor. Please let me know if there is an alternative.

Thanks,

allan

Hi,

You are quite correct, the Editor PHP libraries don't currently play too nicely with SQL functions (it tries to escape the strings...). This is something I'm going to address in a future Editor update, but for the moment, what to do is use PHP's date() function to create a timestamp from the current time (this assumes that either the DB and web-server are on the same machine, or if on difference machines in the same timezone!).

Regards,
Allan

notyou61

I have tried the following however come across other problems:

// Time
$date = date_create();
$date = date('m/d/Y', date_timestamp_get($date));
// Where Clause
if (isset($_GET['gridNumber']) && $_GET['gridNumber']==2 && isset($_GET['varTodaysActions']) && $_GET['varTodaysActions']=='y') {
            $editor->where($key = date('m/d/Y', strtotime('tblActions.actionTimeStamp')), $value = $date, $op = '=' );
}

The error is {"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column '12\/31\/1969' in 'where clause'","data":[]}

Let me know if able to provide any assistance.

Thanks