Hey rez,
Glad that worked! Learning MySQL can definitely help when crafting these "where" clauses, though there are a few things here that are CMSB-specific which I can expand on a bit.
mysql_escapef() is a CMSB function used to prepare portions of a MySQL query by escaping values, which is good for security. The first parameter is the MySQL with "?" where you want any values to be entered, and the following parameters are the values. So this statement:
'where' => mysql_escapef('the_client = ?', $case_studiesRecord['the_client']),
Will evaluate to something like this:
'where' => "the_client = '20'",
It can also be used for two or more values like this:
'where' => mysql_escapef('field_one = ? AND field_two = ?', $value1, $value2),
As for the multi-select fields, the "where" is formatted differently because the data for these fields is stored in a "tab-delimited" format. The "\t" is the code representing a tab, and the "%" is a wildcard. Using "LIKE" instead of "=" is what lets the wildcards work. So if a multi-select field is storing the values 20, 21, and 22, the data will look like this:
\t20\t21\t22\t
So when we use "LIKE '%\t20\t%'" this means "match when the field contains '\t20\t'", rather than requiring an exact match on the whole field, so you can match a single value from many.
I know there may be 50 ways to set this up but in the end, would you use MySQL to search like this, arrays, or what? There could be 1k print projects uploaded... but definitely hundreds.
I would personally use PHP to craft a "where" to handle this. As you say there are many different ways, but a simple method could look something like this:
$whereClause = "TRUE";
// single select
if (!empty($_REQUEST['filterOne'])) {
$whereClause .= mysql_escapef(' AND field_one = ?', $_REQUEST['filterOne']);
}
// single value in multi-select
if (!empty($_REQUEST['filterTwo'])) {
$whereClause .= mysql_escapef(' AND field_two LIKE ?', "%\t{$_REQUEST['filterTwo']}\t%");
}
// multiple values in a multi-select
if (!empty($_REQUEST['multiFilter'])) {
foreach($_REQUEST['multiFilter'] as $filterValue) {
$whereClause .= mysql_escapef(' AND field_three LIKE ?', "%\t{$filterValue}\t%");
}
}
list($records, $meta) = getRecords([
...
'where' => $whereClause,
...
]);
We start with a "TRUE" to default to showing all records, then append " AND ..." for each different filter that exists.
Your exact implementation will depend a lot on the fields you use, but hopefully that gives you a good starting place!
Cheers,
Daniel
Technical Lead
interactivetools.com