Back
filterModifierFn
Filtering and Searching
filterModifierFn
Allows you to perform special searching on a column
Default:
Allows you override normal WHERE searching clauses, so you can
  • Use special database specific search syntax.
  • Search in tables which are not in the current sql statement.
  • Perform full text search and case-sensitive matching.
First a quick overview of how it works. Let's say we have a WHERE clause for field id:

id = 'abc'

which we want to rewrite to:

id like 'abc%'

Now the filter modifier works by breaking the string to the component parts:

$colname = 'ID' # automatically converted to uppercase for you
$oper = ' = '
$prefix = "'"
$valArray = array('abc')
$suffix = "'"

We need to change it to:

$colname = 'ID' # automatically converted to uppercase
$oper = ' like '
$prefix = "'" # remains unchanged
$valArray = array('abc%')
$suffix = "'" # remains unchanged

or

$colname = 'ID' # automatically converted to uppercase
$oper = ' like '
$prefix = "'" # remains unchanged
$valArray = array('abc') # remains unchanged
$suffix = "%'"

Since phpLens 2.5.2, we have added 3 additional parameters to the function, $valueArray, $inputArray and $sqlFrom.

function LensFilterFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode, &$valArray, &$inputArr, &$sqlFrom)

  • $colname is the name of the column
  • $oper is the operator to use (LIKE/=)
  • $prefix is the prefix to attach to the search string
  • $suffix is the suffix to attach to the search string
  • $quote = true to let phplens quote the search string, false if you do it yourself, default is true (phplens quotes for you)
  • $matchMode is 0 == exact match, 1 = match beginning, 2 = match anywhere.
  • $valueArray is an array holding the values keyed in by the user. It is an array because a series of checkboxes could have been keyed in.
  • $inputArray allows you to use data binding. Initially set to false, modify this to set an array value. See example 4 below.
  • $sqlFrom which holds the FROM tables as a string. You can modify it to join with additional tables. For example, it could hold 'tableA, tableB, tableC'. See example 5 below.

So the above change to ID can be implemented with

function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode, &valArray)
{
if ($colname == 'ID') {
  $oper = ' LIKE ';
  $valArray[0] .= '%';
}
}

Final Notes
This function was formerly called filterLikeFn.

Syntax
Example 1: MySQL Full Text Search
/*********************************************************************************

example to support say mysql full text search for column 'message':

SELECT * FROM articles WHERE MATCH (message) AGAINST ('text');
*********************************************************************************/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
if (strtoupper($colname) == 'MESSAGE') {
  $quote = false;
  $colname = " MATCH ($colname) ";
  $oper = " AGAINST ";
  $prefix = "('";
  $suffix = "')";
}
}

$lens->filterModifierFn= 'LensLikeFn';


Example 2: Switch to using PostgreSQL ILIKE operator

# ILIKE is case sensitive
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
  if (strtoupper($oper) == ' LIKE ') $oper = ' ILIKE ';
}
$lens->filterModifierFn= 'LensLikeFn';


Example 3: Using Full Text Search in Microsoft SQL Server

/*********************************************************************************
To support full text search for column 'message':
SELECT * FROM articles WHERE CONTAINS(message,'text');
*********************************************************************************/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode)
{
if (strtoupper($colname) == 'MESSAGE') {
  $quote = false;
  $oper = " CONTAINS($colname,";
  $colname = "";
  $prefix = "'";
  $suffix = "')";
}
}
$lens->filterModifierFn= 'LensLikeFn';


Example 4: Implementing data binding with oci8

function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode,&$valArray,&$inputArr)
{
foreach ($valArray as $k => $v) {
  if ($matchMode == 1) $v .= '%';
  else if ($matchMode == 2) $v = '%'.$v.$'%';
  $inputArr[$colname.$k] = $v;
  $valArray[$k] = ':'.$colname.$k;
}
}
$lens->filterModifierFn= 'LensLikeFn';


Example 5: Joins

/*
This is only a partial example.

The main table is TABLE1, but we need to search for data in TABLE2.COL2.
This requires a join with TABLE2. So we modify $sqlFrom to add TABLE2,
and a join condition is added to $colName.
*/
function LensLikeFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode,&$valArray,&$inputArr, &$sqlFrom)
{
if ($colname == 'COL2') {
  $sqlFrom .= ',TABLE2';
  $joinCondition = ' TABLE1.KEY1 = TABLE2.KEY2 AND ';
  $colName = $joinCondition . " COL2";
}
}

 Basic:Yes  Advanced/Enterprise:Yes  DynamicEdit:Yes   [Version 1.3]