[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.
$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!
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
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?
$sql = "zip LIKE "%\t76016\t%"
you see any issue using % wild card?
Re: [richb] [HOWTO] SQL for Multivalue check box
By Dave - November 19, 2009
Ahh yes, I missed that. You need the wildcard if you want to match any records that have more than just that one zip selected:
$sql = " zip LIKE '%\t76016\t%' ";
$sql = " zip LIKE '%\t76016\t%' ";
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com