Help Creating 'where' statement

12 posts by 2 authors in: Forums > CMS Builder
Last Post: March 27, 2018   (RSS)

By nmsinc - March 18, 2018

I have the following field type (<?php echo join(', ', $record['when_to_bill:labels']); ?> ) that I need to place into a where statement and I'm not sure how to do it. The list may have one or more values that I need to check with a 'where' statement prior to listing records. The field contains labels for all twelve months. I want to compare any of the checked months against the value in '$month' listed below:

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

Thanks - nmsinc

nmsinc

By Dave - March 19, 2018 - edited: March 24, 2018

Hi nmsinc, 

The :labels value is calculated on display, so you're going to want to compare against the numeric value.  What's the value of this?
<?php echo join(', ', $record['when_to_bill']); ?>

Assuming the months are not zero-padded you could do something like this: 

$monthNum   = date('n'); // not zero padded, eg: Jan = 1

// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
  'tableName'   => 'accounts',
  'where'       => " when_to_bill LIKE '\t$monthNum\t' ", // search for month num in tab delimited field,
  'loadUploads' => false,
  'allowSearch' => false,
));

Let me know if that works for you.

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - March 19, 2018

Hi Dave,

I now get a 404 error when using the new code!

Thanks - nmsinc

nmsinc

By Dave - March 19, 2018 - edited: March 24, 2018

Oops, try this: 

'where'       => " when_to_bill LIKE '%\t$monthNum\t%' ", // search for month num in tab delimited field,

Added wildcard % char.  Tricky when I can't test the code! :-)

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - March 22, 2018 - edited: March 23, 2018

Hi Dave,

I know your code should work, but it does not - it still returns zero account listings. Please help!

Thanks - nmsinc

nmsinc

By nmsinc - March 23, 2018

Note:

The multi-list is setup with the following:

1|January

2|February

3|March

and so on through all twelve months!

nmsinc

By Dave - March 23, 2018

Hi nmsinc, 

The way we debug issues like this is to add a debugSQL option to show the exact query being used: 

'debugSql' => true,

And then comment out the 'where' option and print out the value of the field, eg: <?php echo $record['when_to_bill']; ?>

And it's just a matter of comparing what you're searching for and what the actual values are.  It may be that the multi-value field wasn't always multi-value and so the previous values aren't tab separated.  

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By Dave - March 24, 2018

Hi nmsinc, 

Ok, two issues with that SQL debug output, I don't see the "when_to_bill LIKE ..." where code, is that commented out?  Also, it says "WHERE (`num` IN (1))" which is only going to show record num 1.  Maybe you have ?num=1 in the query string for another viewer on the same page?  Try adding to this one:

'allowSearch' => false,

That way it won't add search terms from the query string. 

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - March 24, 2018

Your are correct on multiple queries with more than one 'where' statements and I have commented them out and had already included the 'allowSearch' => false, statement!

Here is the debug with your where statement intact:

SELECT SQL_CALC_FOUND_ROWS `accounts`.* FROM `cms_accounts` as `accounts` WHERE (when_to_bill LIKE "%\tmonthNum\t%") ORDER BY fullname, username

The above returns Null or no listings!

Here it is the 'where' statement commented out and all 'where' statement removed from all other 'LOAD' statemets:

SELECT SQL_CALC_FOUND_ROWS `accounts`.* FROM `cms_accounts` as `accounts` ORDER BY fullname, usernameSELECT SQL_CALC_FOUND_ROWS `accounts`.* FROM `cms_accounts` as `accounts` WHERE (`num` IN (1)) ORDER BY fullname, username

The above returns every record in the file!

Thanks -- nmsinc

nmsinc