There is a possibility to define a SQL expression that should be applied to particular field when reading or writting data from database. This is very useful when you want to interpret the field's content in different way than it is stored in database. To be more clear, see following examples.
Example 4-34. Read SQL expressions
$opts['fdd']['surname']['sql'] = 'CONCAT(surname, ', ', firstname)'; $opts['fdd']['title']['sql'] = 'IF(TRIM(title) != "", title, title2)';
The first example appends content of the
firstname field to the
surname field. Because this is done on the database level, sorting and searching (in table filtering page) on this field will properly work. Similarly in the second example, the
title2 field will be used if the
title field is empty. In this manner you can define a special static string, which should be printed in case a field is empty. Just substitute a quoted string in place of
Similarly, you can use SQL expression for storing data into database.
Example 4-35. Write SQL expressions
$opts['fdd']['surname']['sqlw'] = 'UPPER($val_qas)'; $opts['fdd']['title']['sqlw'] = 'TRIM("$val_as")';
The first example above makes
surname uppercase when storing field into database. The second one trims all whitespace characters around
title before writing it to database.
As a placeholder for the field's content, there are three variables available.
||-- value of the field|
|| -- value with
|| -- same as
"some\"nice\"thing". You have to use these variables correctly in your
['sqlw'] expressions, otherwise a MySQL parsing error could occur. We recommend you use the
$val_qas variable whenever possible, as it is the safest one from the mentioned alternatives.
A very useful and favourite usage of the
['sqlw'] option is to explicitly tell phpMyEdit to store a
NULL value instead of an empty string for the particular column. Empty string and
NULL are two different values. Many people really do not like empty strings in their tables, thus now they have possibility to change them to
NULL when user simply enters nothing into form input field.
Example 4-36. Storing
NULL instead of empty string
$opts['fdd']['col_name']['sqlw'] = 'IF($val_qas = "", NULL, $val_qas)';
Another example of the
['sqlw'] usage is the storage of user passwords. It is good idea to process user password using some well-known hash function before saving it in the database. Following statement is used in order to avoid re-hashing an already hashed string. This means, if
col_name value was not changed, then do not apply
MD5() on it. If
col_name value was changed, then apply
Bootstrap phpMyEdit PHP Form Generator