[HOWTO] SQL for Multivalue check box

4 posts by 2 authors in: Forums > CMS Builder
Last Post: November 19, 2009   (RSS)

By richb - November 18, 2009

I have a table, called zipcodes, with zip codes. I have another table called affiliate with field “zip” defined as field type of “list”. This uses checkbox (multi value) “get options from database”, with zipcodes as the options and value. All is working fine. My question is around the SQL Where statement.

$sql = “zip=76016”;
list($affiliateRecords, $affiliateMetaData) = getRecords(array(
'tableName' => 'affiliate',
'where' => $sql,
'limit' => '1',
));

The database shows “zip” containing multiple values (from multi value checkbox). The data shows “76016 76092 76003” and my SQL where clause is not working. Any ideas? NOTE: the database actually has some additional characters between the zip codes. I think CMS uses this as separator chars.

Re: [richb] [HOWTO] SQL for Multivalue check box

By Dave - November 19, 2009

Hi richb,

They're tab characters. Try this:

$sql = " zip LIKE '\t76016\t' ";

Let me know if that works for you!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] [HOWTO] SQL for Multivalue check box

By richb - November 19, 2009

Dave this works. I also used this as well.

$sql = "zip LIKE "%\t76016\t%"

you see any issue using % wild card?