4.5. Input restrictions

You can restrict user input for selected fields to selected values. There are several ways to do this. A variety of methods and examples appear below.

Simple restriction

Simple restriction means to restrict user input to the specified constants. Examples appear below.

$opts['fdd']['col_name']['values'] = array('','No','Yes'); // default is '' (nothing) 
$opts['fdd']['col_name']['values'] = array('','Yes','No'); // default is '' (nothing) 
$opts['fdd']['col_name']['values'] = array('0','1');       // default is 0 
$opts['fdd']['col_name']['values'] = array('A','B','C');   // default is A 
$opts['fdd']['col_name']['values'] = array('No','Yes');    // default is No 
$opts['fdd']['col_name']['values'] = array('Yes','No');    // default is Yes 
$opts['fdd']['col_name']['values'] = range(1,99); 

Table lookup

Variables $opts['fdd']['col_name']['values']['table'] and $opts['fdd']['col_name']['values']['column'] restricts user input to the values found in the specified column of another table. The optional ['values']['description'] field allows the values displayed to the user to be different from those in the ['values']['column'] field. This is useful for giving more meaning to column values.

$opts['fdd']['col_name']['values']['table']       = 'extractTable'; 
$opts['fdd']['col_name']['values']['column']      = 'extractColumn'; 
$opts['fdd']['col_name']['values']['description'] = 'extractDescription'; // optional

Column joining

It is also possible to have multiple fields in your description. For example, to concatenate two description labels found in a different table:

$opts['fdd']['col_name']['values']['description']['columns'][0] = 'desc_column_1'; 
$opts['fdd']['col_name']['values']['description']['columns'][1] = 'desc_column_2'; 
$opts['fdd']['col_name']['values']['description']['divs'][0]    = ' '; 

The 'div' component is what will be used as a divider between the columns in the display. You don't need to define the last 'div' field if it isn't required. So, for example if you have a series of people in a table, with a separate column for id, first name, and last name, you could use:

$opts['fdd']['col_name']['values']['db']     = 'mydb'; // optional
$opts['fdd']['col_name']['values']['table']  = 'mytable'; 
$opts['fdd']['col_name']['values']['column'] = 'id'; 
$opts['fdd']['col_name']['values']['description']['columns'][0] = 'name_last'; 
$opts['fdd']['col_name']['values']['description']['divs'][0]    = ', '; 
$opts['fdd']['col_name']['values']['description']['columns'][1] = 'name_first'; 
$opts['fdd']['col_name']['values']['filters'] = 'id IN (1,2,3)'; // optional WHERE clause
$opts['fdd']['col_name']['values']['orderby'] = 'last_name'; // optional ORDER BY clause 

If prefixation with some string in column description is desired, the $opts['fdd']['col_name']['values']['description']['divs'][-1] can be used. It will precede $opts['fdd']['col_name']['values']['description']['columns'][0] column.

Note that the above example contains additional features, such as filtering values using ['filters'], and ordering values using ['orderby'].

Additional values

Additional values to table lookup could be stored in ['values2'] array. The main difference between simple ['values'] usage is, that array keys will be stored into database and array values will be printed out in input section boxes. This is especially useful for MySQL enumerations when you do not want to print out enumeration keys, but rather some more user-friendly texts. See example:

$opts['fdd']['col_name']['values2'] = array(
	'displayed' => 'Displayed Article',
	'hidden'    => 'Hidden Article',
	'disabled'  => 'Disabled Article',
	'deleted'   => 'Deleted Article'
);

In the example above, keywords 'displayed', 'hidden', 'disabled' and 'deleted' will be stored in database, but user-friendly expressions will appear in select box for user. Usage of ['values2'] can be combined with ['values'] usage.

Advanced joining

Sometimes you want to restrict table joining on the output. This is important in case where ['values']['column'] is not unique in ['values']['table']. For this purpose, you can use $opts['fdd']['col_name']['values']['join'] option. Using the ['values']['filters'] simply will not work, because it is not applied at join time, but only when filling values in the drop down menu.

These variables are available in this option.

phpMyEdit will create by default $main_table.$main_column = $join_table.$join_column join, what is sufficient the most cases. However you may extend it with additional conditions as well.

$opts['fdd']['col_name']['values']['join']
= '$main_table.$main_column = $join_table.$join_column AND '
. '$main_table.another_col  = $join_table.another_col'

Please note that ['values']['filters'] is used for filtering items in dropdown during Add/Edit mode (with a SQL WHERE clause) while ['values']['join'] is useful for having a correct LEFT JOIN against the main table in List/View mode.