Help Creating 'where' statement

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.

Hi Dave,

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

Thanks - nmsinc

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! :-)

Hi Dave,

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

Thanks - nmsinc

Note:

The multi-list is setup with the following:

1|January

2|February

3|March

and so on through all twelve months!

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!

Using a join request  <?php echo join(', ', $record['when_to_bill:values']); ?> it returns 3,6,9,12

Using a standard request  <?php echo $record['when_to_bill']; ?> it returns 3 6 9 12

I believe these should be correct and the where statement should pick up on this, correct or am I missing something?

The debug statement returns the following:

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

Thanks - nmsinc

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.