$recordSet->getWherePattern($fldname, $value)

Up: class RecordSet

This is a shared utility method which generates the expression for selecting records for columns with character values which are compared for a pattern match. It can be called from the getWhere method of RecordSet or a class-specific extension of RecordSet.

The parameters for this method are:

parameter	description
$fldname	A string identifying the field to be used for selecting records. This may be an actual field name or a synonym defined for the table. The case of letters in the field name is ignored.
$value	The value used for the comparison. This is a string and the comparison is for pattern match, not equality. If you wish to compare for equality, use RecordSet::getWhereCommon. The default behavior of the pattern match is as if performed using the MySQL REGEXP operator, however if the value starts with 'LIKE ' then the more portable SQL LIKE pattern is used and for clarity the remainder of the value can be a quoted string. If the string starts with an exclamation point (!) then the comparison matches all instances where the value is not equal to the remainder of the value, ignoring case. If it is a string and the front part of the string is "length=" or "length>" then the remainder of the string is used to compare to the result of the SQL length function. If the value is an array then each element is handled as described for an elementary value. All of the patterns must match.

Note the "as if performed using the MySQL REGEXP operator". Since support for classic regular expressions is an implementation extension specific to MySQL, MariaDB, and SyBASE using the REGEXP operator, PostgreSQL using the ~ operator, Oracle using the REGEXP_LIKE() function, and in some other implementations through the use of user defined functions, the most common cases are simulated using standard SQL operations, which will also in general be more efficient than REGEXP. For example:

• getWherePattern('fldname', '^value') generates "LEFT(`fldname`,5)='value'", that is a match if 'value' appears at the beginning of the value of fldname. The length is one less than the length of the $value parameter.
• getWherePattern('fldname', 'value$') generates "RIGHT(`fldname`,5)='value'", that is a match if 'value' appears at the end of the value of fldname. The length is one less than the length of the $value parameter.
• getWherePattern('fldname', '^value$') generates " `fldname`='value'" if "value" contains no characters that have special meanings in a regular expression or begins with a quantifier. That is comparison for equality, bearing in mind that by default all string comparisons in SQL are case-insensitive. For example '^Smith$' generates " `fldname`='Smith'", '^?$' generates " `fldname`='?'" because '?'is a quantifier and therefore must follow a character class. '^sm[eiy]+the?$' falls through to generate " `fldname` REGEXP '^sm[eiy]+the?$'"
• getWherePattern('fldname', 'LIKE value') generates " `fldname` LIKE 'value'". See the documentation for SQL LIKE. For clarity the value parameter can be specified as a quoted string, for example, "LIKE 'value'". In that case it is necessary to escape characters that cannot be contained in a quoted string, in particular the apostrophe character "'". That is you can specify either of the following: getWherePattern('surname', "LIKE O'Re%") or getWherePattern('surname', "LIKE 'O\'Re%'").
• getWherePattern('fldname', "!value") generates the SQL expression " `fldname`!='$value'" to select all instances where fldname does not match the value.
• getWherePattern('fldname', "^sometext(?!othertext)") generates the SQL expression " LEFT(`fldname`,length of sometext)='sometext' AND SUBSTR(`fldname`,length of sometext plus 1, length of othertext)!='othertext'". This is exploited by Surnames.php to obtain a list of surnames starting with 'M' but exclude surnames starting with 'Mc'. It creates a RecordSet with parameter 'surname' => 'M(?!c)'.
• getWherePattern('fldname', 'value') generates " `fldname` REGEXP 'value'" if value looks like a regular expression but does not match any of the special patterns above.
• getWherePattern('fldname', 'value') generates "LOCATE('value',`fldname`)>0", that is a match if 'value' appears anywhere in the value of fldname, if none of the preceding conditions apply.

Also note that some SQL implementations, for example MySQL prior to 8.0.4, use the original specification of regular expressions as defined by POSIX, not the PERL-compatible regular expressions implemented by most programming languages including PHP and EcmaScript/JavaScript.

This returns a string containing a fragment of an SQL expression that can be substituted into a MySQL WHERE clause

Next: $recordset->getSurnameChk($table,$value)