4.8. SQL expressions

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.

$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.

$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.

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.

$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.

$opts['fdd']['col_name']['sqlw'] = 'IF(col_name = $val_qas, $val_qas, MD5($val_qas))';