Search by date range by submitting serach form request

14 posts by 4 authors in: Forums > CMS Builder
Last Post: July 2, 2015   (RSS)

By nmsinc - July 1, 2015

The min and max dates are two separate textfields. The date picker fills the "From" and "to" textfields with the date formatted as: 2015-07-01

The date field for the record to lookup is: 'createdDate'

Thanks - nmsinc

nmsinc

By Dave - July 2, 2015

Ok, got it, thanks.

Try this on the end of your URL and see if it changes the results: ?createdDate_min=2015-07-01+00:00:00

If it does add this code before you call getRecords(): 

if (@$_REQUEST['date_min']) { $_REQUEST['createdDate_min'] = $_REQUEST['date_min'] . ' 00:00:00'; }
if (@$_REQUEST['date_max']) { $_REQUEST['createdDate_max'] = $_REQUEST['date_max'] . ' 00:00:00'; }

And then temporarily add this debugging option to getRecords():

'debugSql' => true,

In addition to the _year, _mon, _day suffixes listed here: http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html

You can also do _min _max search comparisons on dates, it just treats them as a 14 digit number.  I'll update the docs to reflect that.

Let me know if you still have any problems with the above code.  Thanks!

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - July 2, 2015

Hi Dave,

Still having issues - here is what I have:

  if ($CURRENT_USER['isAdmin']) {
  //$where = 'TIMESTAMP(CURDATE(), "00:00:00") <= createdDate AND createdDate <= (NOW() + INTERVAL 7 DAY)';
  $where = "";
  }
  elseif (!$CURRENT_USER['isAdmin']){
  $where = "((assigned_to = '".intval($CURRENT_USER['agency'])."' OR assigned_to = '".intval($CURRENT_USER['assigned_to'])."') and remove_record_from_listing = 0)";
  }
  if ($CURRENT_USER['isAdmin']) {
  $where1 = "disabled = 0 AND isAdmin = 0";
  }
  elseif (!$CURRENT_USER['isAdmin']){
  $where1 = "((agency = '".intval($CURRENT_USER['agency'])."' OR assigned_to = '".intval($CURRENT_USER['assigned_to'])."') AND disabled = 0) AND isadmin = 0";
  }
  // load records from 'daily_log'
 
  // added per Dave before getRecords statement
  if (@$_REQUEST['date_min']) { $_REQUEST['createdDate_min'] = $_REQUEST['date_min'] . ' 00:00:00'; }
  if (@$_REQUEST['date_max']) { $_REQUEST['createdDate_max'] = $_REQUEST['date_max'] . ' 00:00:00'; }
  
  list($daily_logRecords, $daily_logMetaData) = getRecords(array(
    'tableName'   => 'daily_log',
    'where'       => $where,
    'orderBy'     => 'num DESC', // bring highest num to top
    'debugSql' =>'true',
  ));

  // load records from 'accounts'
  list($accountsRecords, $accountsMetaData) = getRecords(array(
    'tableName'   => 'accounts',
    'where'       => $where1,
    'loadUploads' => false,
    'allowSearch' => false,
  ));

I still get all records - Here is the debug statement:

Array (

[min_date] => 2015-06-01

[max_date] => 2015-06-02

[submit] => Click Here )

SELECT SQL_CALC_FOUND_ROWS `daily_log`.*

FROM `cmsb_daily_log` as `daily_log`

ORDER BY num DESCSELECT SQL_CALC_FOUND_ROWS `accounts`.*

FROM `cmsb_accounts` as `accounts`

WHERE (`num` IN (8))

ORDER BY fullname, username

ReportPro1

Log Inventory Report t:

Thanks - nminc

nmsinc