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 title2.
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.
$val | -- value of the field |
$val_as | -- value with addslashes() function applied |
$val_qas | -- same as $val_as with quotes around |
If the $val is some"nice"thing, then
$val_as becomes some\"nice\"thing and
$val_qas becomes "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 MD5() function.