Error with 'where' date clause

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!

Thanks Dave - I will it a try!

nmsinc

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



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.