Multiple Range Filter
3 posts by 2 authors in: Forums > CMS Builder
Last Post: March 31, 2014 (RSS)
By Perchpole - March 31, 2014
I am trying to put together a filter for a range of products. Each item in the store can be assigned to one or more "ranges" by means of a multi-select menu in the editor. (value=num, label=title)
When a person views a product I want to list of all the other products which are assigned to the same ranges. This would be easy if a product were limited to a single range - but multiple ranges is making my head hurt!
Here are the products and the assignments:
ProductA = range2
ProductB = range4
ProductC = range1,range2
ProductD = range1,range3
ProductE = range2, range4
ProductC is in both range1 and range2 So, if the customer is looking at ProductC, the list of other products should be:
ProductA (in range2)
ProductD (in range1)
ProductE (in range2)
Any help would be most appreciated!
:0)
Perchpole
By Chris - March 31, 2014
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