Error with 'where' date clause
6 posts by 2 authors in: Forums > CMS Builder
Last Post: June 17, 2015 (RSS)
By nmsinc - May 30, 2015
I have the following 'where' clause that gives me the following error:
Parse error: syntax error, unexpected '00' (T_LNUMBER)
$where = "( TIMESTAMP(CURDATE(), "00:00:00") <= createdDate AND createdDate <= (NOW() + INTERVAL 7 DAY) AND (agency = '".intval($CURRENT_USER['agency'])."' || agency = '".intval($CURRENT_USER['assigned_to'])."') )";
Any help would be appreciated!
nmsinc
By Dave - June 1, 2015
Hi nmsinc,
First step to debug that is just print out the $where and exit. You'll catch a lot of errors that way.
Next, use multiple lines to build up the query and it will be much easier to read and debug. Also note that AND has higher precedence than OR so your condition is basically (test1 AND test2 AND test3) OR agency = assigned_to. I think you want those last two conditioned OR'd like this? test1 AND test2 AND (test3 OR agency = assigned_to). Let me know if that's right or if the original way was intentional.
Anyways, I rewrote it to understand it, how about this? (untested code)
// Create where
$isToday = " TIMESTAMP(CURDATE(), '00:00:00') <= createdDate ";
$inNext7Days = " createdDate <= (NOW() + INTERVAL 7 DAY) ";
$agencyMatch = " agency IN (" .intval($CURRENT_USER['agency']). "," .intval($CURRENT_USER['assigned_to']). ") ";
$where = " $isToday AND $inNext7Days AND $agencyMatch ";
showme($where);
exit;
Let me know if that works for you!
interactivetools.com
By Dave - June 1, 2015
That first variable is probably better named $isTodayOrLater
interactivetools.com
By nmsinc - June 16, 2015
Hi Dave,
I'm having some troubles with the date code you suggested. I only get the last record posted when there are plenty of records that should match up with the $where clause. Any ideas?
I did make some edits in the $agencyMatch for the updated fields that I need to match with no errors!
Thanks - nmsinc
/ Create where
$isToday = " TIMESTAMP(CURDATE(), '00:00:00') <= createdDate ";
$inNext7Days = " createdDate <= (NOW() + INTERVAL 7 DAY) ";
$agencyMatch = " company IN (" .intval($CURRENT_USER['member_company_accounts']). "," .intval($CURRENT_USER['assigned_to']). ") ";
$where3 = " $isToday AND $inNext7Days AND $agencyMatch ";
By Dave - June 17, 2015
Hi nmsinc,
I'd try each part of the where clause one by one until you find the one that is restricting the records. Also just print $where3 and visually inspect the query. So $where = $isToday; then $where = $inNext7Days, etc.
Once you determine which part it limiting the records returned, just set $where = " 1 "; (or blank) to return all records and do a showme($records). Then visually compare the printed $where with each record to see if the condition should match.
Hope that helps! Let me know how far you get with that and what you find out.
interactivetools.com