Back
Warning: flock(): 1996 bytes of buffered data lost during conversion to FILE*! in /var/www/html/php/phplens/adodb/adodb-csvlib.inc.php on line 92

Filtering and Searching
filterModifierFn
Allows you to perform special searching on a column Allows you override normal WHERE searching clauses, so you can use database specific syntax. Useful for full text search and case-sensitive matching. Formerly named filterLikeFunction. 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 two additional parameters to the function, $valueArray and $inputArray. function LensFilterFn(&$colname,&$oper, &$prefix, &$suffix, &$quote, $matchMode, &$valArray, &$inputArr) $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. 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] .= '%'; } }` 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';` Basic:No Advanced/Enterprise:Yes DynamicEdit:No [Version 1.3]