Create options of fieldtypes with option lists dynamically on runtime from database using sql
This feature is part of the Visforms Subscription and not available in the free Visforms version.. It was added to the Visforms Subscription 3.4.0.
Sometimes you want to display selects (or radios or checkbox groups) in your form that have an option list, which is created dynamically from content in your database. If the content in the database changes, the next time the form is displayed, it will have a different option list, too.
The Visforms Subscription provides you now with set of new field types (selectsql, radiosql and multicheckboxsql) which allow you to define an SQL select statement that is used to create the option list.
The SQL statement must return a value and a label
In order to create option lists from the database you have to enter a valid select sql statement in the "sql" option of the selectsql, radiosql or multicheckboxsql field.
You can use any valid SQL select statement with these field types. These Visforms field types just expect, that your SQL statement returns a value and a label. These are the two return values, that are used as option value and as option label.
Example: select id as value, title as label from #__content limit 10.
Further on can the select statement return a redirecturl, a mail, a mailcc and a mailbcc, which will then be used by the visforms Plugins Custom Redirect and Custom Mail Address
Using placeholder
You can use some placeholders in your SQL statements, that are replaced with real values, when the statement is processed. These are data from the Joomla! user object of the currently logged in user or data form the current input object.
Placeholder for the Joomla! user objects are of format ${user:parametername}. I.e. ${user:id} for the current users id.
Placeholder for the current input object are of format ${input:parametername}, where you have to replace "parametername" with the actual parameter which you want to use.
Heads Up
As the SQL statement is entered into a textarea and the content of textareas is preproccessed (filter) when it is stored, you have to avoid some things.
- Make sure, that you do not have a combination of < > in your code (even if there is a lot of text between the opening and the closing bracket). If necessary change your statement, so that you can use a > instead of the <
- Make sure, that there are no line breaks in the code
- Make sure, that there are no multiple blanks in the code
Placeholder of parameters which are passed as array in the POST
Some form field types are passed as arrays in the POST. I.E. selects and checkboxgroups. Please note, that you must use the following syntax then, if you want to use placeholders for those fields in your sql statements: ${input:fieldname[]}. visForms will convert the values into a string so that they can be used with the mysql in() function. I.E. Input parameter select[]=value1 select[]=value2 will be converted to ("value1","value2") so that it can be used directly in the sql where clause like this: where dbfieldname in ${input:select[]}
The test button
You can use the Test button, in order to test your SQL statement. Please note, that, if you use placeholder for the user object of the currently logged in user or for the input object in your SQL statement, the result of the "Test" in the administration will probably differ (or when using the input object even fail) from the result that is obtained, when the field is displayed in the frontend.
ACL
Basically you can use any valid SQL select statement with these field types. So basically it is possible to expose any (sensible) data to the frontend. Therefore we have created a specific permission "Create SQL Statements", which you should use to restrict the right to create or change the SQL statements for these field types.
Implications of using dynamically created options lists on stored submissions
Creating the option lists dynamically has some impact on what can be done with fields of these types. First of all, these field types cannot be used to trigger conditional fields. Further on, if you store the submissions, it might not be possible to match the stored values to the option list (and the label that was displayed to the user in the first place) again (because it might just no longer be part of the list). Visforms tries to deal with these problems as intelligent as possible, but there are situations, where it might not be possible to use the stored submissions for such a field type without complications.
Custom field options for selectsql
It is possible to reload the option list of a selectsql field dynamically, depending on the user inputs in the form. Please not, if you want to use this very powerfull feature, you can no longer make the field conditional it is either relaoding the option list or making field conditional. If you choose to use this feature (by setting the option "make reloadable" to yes) you can then select fields, which will trigger a reload ("Reload options on change of") of the option list, if the user changes their selection in this field. You can then use placeholder for parameters of the input object in order to get an option list, which suits the current user selection in the trigger field.
For example. You have data of holiday regions and hotels for each region in your database. Users should be able to send contact requests to those hotels with a visForms form. You have create a field where the user can select a specific holiday region. After the user has selected that region you want to display all hotels which are stored in your database for that region as options of a listbox. This can easily achived with a field of type selectsql using the reload options feature.
Hide field, if option list is empty
In the field configuration of fields of type selectsql you find an option, that allows you to hide fields with sql driven option lists, if the option list is empty and if you have enabled the "Reload options on change of" parameter.