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

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!

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - June 2, 2015

Thanks Dave - I will it a try!

nmsinc

nmsinc

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 ";

nmsinc

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.

Dave Edis - Senior Developer
interactivetools.com