Multiple Range Filter

CMS Builder keeps multi-value list fields in a tab-separated format with extra tabs at both ends. This is done so that you can search the field for TAB-value-TAB; without the leading and trailing tabs, that wouldn't work. Tabs are represented as "\t" by both PHP and Mysql.

Therefore, your ranges fields look like this:

ProductA.ranges = "\trange2\t"
ProductB.ranges = "\trange4\t"
ProductC.ranges = "\trange1\trange2\t"
ProductD.ranges = "\trange1\trange3\t"
ProductE.ranges = "\trange2\trange4\t"

Because of the leading and trailing tabs, you can find anything with "range2" with WHERE `ranges` LIKE '%\trange2\t%'.

We can use trim($productRecord['ranges']) to strip the outside tabs, then explode("\t", ...) to turn the tab-separated list into an array. If $productRecord is set to your ProductC record, this would be array("range1", "range2"). Then we need to build up a WHERE clause which searches other product records for `ranges` LIKE "%\trange1\t%" OR `ranges` LIKE "%\trange2\t%".

// build a where clause for any products sharing one or more `ranges`
$ranges = explode("\t", trim($productRecord['ranges'])); // e.g. array("range1", "range2")
$where = 'FALSE'; // a trick for concatenating OR clauses
foreach ($ranges as $range) {
  $where .= ' OR `ranges` LIKE "%\t' . mysql_escape($range) . '\t%"';
}

// disinclude the current product
$where = "($where) AND num != '" . mysql_escape($productRecord['num']) . "'";

// now you can look for all products which share one or more ranges:
list($otherProducts, ) = getRecords(array(
  'tableName'   => 'products',
  'allowSearch' => false,
  'where'       => $where,
));

"FALSE OR a OR b OR c" is the same as "a OR b OR c", it's just easier to code since you don't have to worry about whether or not you need to add " OR ".

I hope this helps. Please let me know if you have any questions.

Chris -

This is spot on! Thanks.

:0)

Perch