where for a list field problem

7 posts by 2 authors in: Forums > CMS Builder
Last Post: July 3, 2017   (RSS)

By dlsweb - July 2, 2017

I'm confused. For a winery in thomas county included in my one table

This works to show the entry (among others)
'tableName' => 'entry',
'loadUploads' => true,
'where' => 'county = "Thomas"',
'allowSearch' => false,

But this does not, 
'tableName' => 'entry',
'loadUploads' => true,
'where' => 'category = "Winery"',
'allowSearch' => false,

the only difference is that category is a list field (checkboxes (multi value)), there are some with multiple entries (comma seperated in database)
nor does any other category entry work

Thanks

By Deborah - July 3, 2017 - edited: July 3, 2017

Hi, dlsweb. I get confused with those, too. The answer will depend on whether the multi-value checkbox list is in the same table or an external table.

If category list is in same table, use the category label for your 'where' in this manner, note the $ on either side:
If category list is in same table, use the category label for your 'where' in this manner, note the % on either side:

'where' => 'category = "%Winery%"',

If category list is in an external table, use the category record number. For example, if your Winery category recordn number was 5 in the external table, that would look like this:

'where' => 'category = "5"',

Let me know if that works.

~ Deborah

By dlsweb - July 3, 2017 - edited: July 3, 2017

the $ did not work
'where' => 'category = "$Winery$"', 
Gets No records were found!

category is in the same table, and my value and label are exactly the same.

Thanks, Larry

By Deborah - July 3, 2017

Larry, so sorry. The $ was a typo. Did you try this with the %?

~ Deborah

By Deborah - July 3, 2017

Yes. The following is what I'm using:

list($items_inRecords, $items_inMetaData) = getRecords(array(
'tableName' => 'apples'
'loadUploads' => true,
'allowSearch' => false,
'where' => ' category LIKE "%Sweet%" ',
));

Then in the HTML:

<?php echo join(', ', $record['category:values']); ?>

Resulting display:
Red Delicious, Fuji, Golden Delicious

By dlsweb - July 3, 2017 - edited: July 3, 2017

I had not tried the % and the LIKE
This works
'where' => 'category LIKE "%mycategory%"',

Thanks so very much for the help, you're terrific

Larry