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
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!
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
By Dave - July 2, 2015
Hi nmsinc,
Do you want to email me FTP/CMS login details and steps to recreate the issue and I'll take a look at the files? You can email me at dave@interactivetools.com (don't post passwords to the forum).
Thanks!
interactivetools.com