Introduction
Note: These features are part of the Visforms Subscription and are not included in the free Visforms version.
Sometimes you want to display list boxes (or radio buttons, or groups of check boxes) in your forms, whose selection lists are dynamically retrieved from the database during form runtime. If the content of the database changes, the corresponding field automatically has the changed selection options the next time it is displayed. This process can also refer to the current user input and the change in user input in other fields on the form.
The Visforms Subscription offers you three new field types whose selection lists are generated dynamically from the database using SQL select statements.
The SQL field types with option lists are:
- Listbox SQL (selectsql)
- Radio SQL (radiosql)
- Checkbox Group SQL (multicheckboxsql).
System requirements
The Visforms Subscription must be installed.
The SQL select statement must return the value and label fields
The selection lists of the SQL field types can be generated from the database using an SQL statement. Create a valid SQL select statement and enter this in the field configuration in the “SQL” option.
You can use any valid SQL select statement. The only condition is that the statement returns the two result fields “value” and “label”. These are then used as the option value and as the option label of the respective option.
A small example:
select id as value, title as label from #__content limit 10
Note: The SQL select statement must not contain any newlines or tabs. Multiple spaces also lead to an error message.
The SQL select statement can return additional fields
You can return the following additional result fields in your SQL select statement:
- redirecturl,
- mail,
- mailcc,
- mailbcc.
These result fields are known to the two Visforms plugins “Option-dependent success pages” and “Option-dependent recipient mail”. Both Visforms plugins use these result fields in the usual way.
Note: The respective plugin must be activated in order to function.
The table name
The table name can be written out completely:
select id as value, title as label from j4_content limit 10
or using the Joomla placeholder #_ for the table prefix:
select id as value, title as label from #__content limit 10
Watch out
The SQL statement is entered in a text area. However, the content of text areas is processed and filtered by Joomla when it is saved.
It is therefore essential that you observe the following points. The statement may:
- Nowhere the combination of < > included.
Even if there is a lot of text between the two brackets. - No < character included.
If necessary, rewrite your statement so that you can use > instead of <.
For example, where F5 < 100 becomes where 100 > F5. - No newlines included.
- No tabs included.
- Do not contain 2 or more consecutive spaces.
Placeholder in the SQL
You can use placeholders in your SQL select statement, which will be replaced with real values at runtime.
The placeholders can be from the following Joomla objects:
- from the Joomla User object, which contains, among other things, the information about the Joomla user or
- from the Joomla input object, which among other things contains the current form data.
Placeholder of the Joomla user object
The Joomla User object contains, among other things, the information of the current user.
The Joomla User Object placeholders have the format: ${user:parametername}.
For example, ${user:id} for the current user’s ID.
The placeholders of the input object
The Joomla input object contains, among other things, all data that is transferred to the backend with the POST request. A POST request is sent by the web browser when the form makes a so-called AJAX call or when the form is submitted. The POST request contains, among other things, all current form data.
Note: The Joomla input object contains all current form data.
The placeholders of the input object have the format ${input:parametername}.
You must replace “parametername” with the name of the input parameter you want to use.
The input object placeholders for a form field
To write the placeholder for a form field, use the input object with a field name: ${input:fieldname}.
Where <span class="code"fieldname is the name of the field whose value you want to have inserted.
Placeholder for list fields that are passed as an array
List box and check box group form fields pass the user input in the POST request as an array.
If you want to use a placeholder for such a parameter in the SQL statement, you must observe a special format entry: ${input:fieldname[]}.
Visforms then automatically converts all values of this parameter to a string so that they can be used in the MySql in() aggregate function.
Example
The input parameter select[]=value1 select[]=value2 is converted to (“value1”,“value2”).
It can be used directly in a SQL where clause like this: where fieldname in ${input:select[]}
Modern text editor or development environment
We recommend that you write the SQL statement in a good modern text editor or, even better, in a dedicated development environment and test it thoroughly. It is also possible to develop and test the SQL statement in a MySql console.
At the very end, remove any newlines and tabs and multiple spaces that are included in large statements for much better readability.
Replace all desired dynamic places in the statement with Visforms’ own format for placeholders.
Replace the hard-coded table prefix with the Joomla placeholder #_ for the table prefix.
Now copy and paste the cleaned expression into the Visforms configuration field and test whether the SQL statement works as expected in the running form.
Keep the original SQL expression in a suitable place to facilitate subsequent work.
Note: The placeholders for form fields are only replaced in the current form. Because without a running form, no form data is available for replacement.
Note: The Joomla User object and the Joomla Input object have different content while you are configuring the SQL field in the administration than when the form is displayed to the user in the frontend. Because an administrator is logged-in and it is a matter of displaying a page within the administration.
When using the test button for the SQL statement, you must take this fact into account and work with fixed values instead of placeholders.
The test button
You can use the Test button right next to the SQL input field to test your SQL statement.
However, please note the following.
If you use placeholders in your statement, the result of the test in the administration can probably deliver different values or often an error message than when using the field in the current form. Therefore, when testing in the administration, do not use the final placeholders in Visforms format in the SQL statement, but instead use sensible concrete values.
Note: The final placeholders should only be included when the current form is used.
Joomla ACL
In principle, you can use any SQL select statement in these field types and select any type of data. This makes it possible to display extremely sensitive data, provided such data exists somewhere in the database.
We have therefore created an additional user authorization. With this user authorization you can limit the right to create an SQL statement to certain user groups. Default for this user permission is only the “Super User” user group.
Effects on saved form data
Dynamically generating the select lists has implications for what can be done with SQL field types.
No control over the visibility of other fields
It is not possible to use these fields to control the visibility of other fields. They are therefore also not displayed in other fields in the “Conditional Display” list.
The old option is no longer available
Suppose you save the submitted form data. Then it can of course happen that the assignment of already saved old values to the current new selection list of the field is no longer possible.
This is particularly the case if the selection list has changed in the meantime and the corresponding old option no longer exists. This has an effect when editing data in the frontend. Or, if the user input is to be used in PDF documents.
If you save submitted data in the database, then Visforms only saves the option value. The option label is not stored in the database but in the field configuration. However, the information about the option label is displayed to the user for his selection. It may then no longer exist for old values from the database if the option in question was deleted or changed in the field configuration. This can lead to unexpected effects in the data display in the frontend.
Note: Therefore, for SQL fields, it can make a lot of sense to use the same value for option value and option label.
Additional options for “selectsql”
Reload options list after the user has made an entry
Suppose the user is currently making entries in the form in the frontend. The selection list of a “selectsql” field can be loaded dynamically, depending on the user input that has just been made.
Note: If you use this feature, you can no longer show and hide the corresponding field via "Conditional display". You can use either the "Allow Relaod" option or the "Conditional Display" option, but not both at the same time.
If you activate the “Allow to reload” option, you will see a list box. In this list you can specify which user inputs should trigger a reload. Setting up the reloading of a selection list is particularly useful if you evaluate the user input as a parameter in the SQL statement of the reloaded field.
Possible trigger fields
Trigger fields are fields whose user input changes cause another field to be reloaded or recalculated. In the case of SQL fields, the stored SQL is executed again. Trigger fields are selected in the configuration of the field to be reloaded.
The following field types can be selected as trigger fields, provided they are not configured as “Edit Only” fields:
- “Listbox”, “Radiobutton”, “Checkbox Group”
- “Listbox SQL”, “Radiobutton SQL”, “Checkbox Group SQL”.
Example
You have data on holiday regions in your database and a list of hotels for each region. After the user has decided on a holiday region, he should be offered a list of hotels from this region to choose from. This can easily be achieved with 2 “selectsql” fields and the reload options. The list in the “Hotel” field is therefore reloaded after every change in the selection of the holiday region.
Hide field if options list is empty
There is an additional option in the field configuration of fields of type “selectsql”. With this option you can hide the field if the selection list is empty. This option is only available if it is a field whose selection list is reloaded.
In this way you can ensure that the list box for hotel selection from the example above is only displayed after the user has selected a holiday region. Because the hotel list box only contains options if a selection of the holiday region has been made.
Show field as data list
There is an additional option “Show as data list” in the field configuration of fields of type “selectsql”. Using this option you can ensure that the data returned by the SQL statement is not used to create a list box from it. In this case, the data is simply displayed in a small data list.
In this case, the user cannot make a selection. The field has a purely informative value and offers the possibility of dynamically loading information from the database and displaying it in the form. The values returned by the SQL statement for “label” are displayed.
The “Make reloadable” option is also available to you when viewing this data list.
No field value recalculation when editing data
If the SQL statement is executed again when editing the data record, the field value may change as a result. If you want to prevent this automatic re-execution of the SQL statement and any possible change to the field value, set the option Execute SQL in form edit to No.
Note: In particular, SQL statements that work with the current date or current quantities/values (auto-increment) can lead to unwanted results if they are executed again at a later time.
