RackForms v7.2.1
ajax
Site Tag Line
Latest Release: Build 924 - December 22, 2018
Contact Us  Purchase Options Purchase Options Contact Us
header-image


 
Top Link
Text AJAX


In Brief: An element that lets you query a database and return the results to form fields such as select boxes.

Dependencies: A standard form page.

Hints & Tricks: This element is best used in situations where we have changing data based on user input and we want to keep the user on the current page.

It's also important to note that this field needs two distinct 'bindings' to work. First, we need to designate what field will fire the AJAX event (AJAX Update Binding Options), the second binding to tell the AJAX module which field(s) to update with the retrieved data (Item Options/AJAX Bindings).

PHP Version Note: This feature, because it makes extensive use of native JSON features, is only supported in PHP 5.2 or later.

AJAX Introduction Tour - Start Here!


Please be sure to watch in HD and full screen for the best experience!

AJAX Module Doesn't Seem To Work?

One of the most common issues with creating AJAX logic is using a faulty SQL Query. This doesn't necessarily mean the query is bad in terms of failing with errors, but rather, that the query may be trying to 'connect the wrong dots'. This is a common problem because by its very nature the AJAX module relies on a relationship created between the SQL query and the binding values we pass it. If our query is trying to say, look up a specific user name by id but we've bound a users age to the SQL variable, the query will run but we'll get no results.

Thus, a good rule of thumb is to carefully consider what data the AJAX query should process.

Using Empty Binds

Sometimes we may want to run an AJAX query, but do not want the result to bind to any field, rather, we simply want to run the query and create custom code in the Code To Run After Query box. For example, we want to create show hide logic based on the values of fields.

To accommodate this, in Builds earlier than 747 we can simply bind to a dummy item such as a hidden field. However, starting with Build 747 we can now create AJAX items to do not contain bindings, and will not throw errors because of this.

CAUTION! By default RackForms attempts to use the MySQLi extension in the RackForms editor UI when setting AJAX binds. As most servers have the MySQLi extension enabled by default this isn't an issue. However, if your server has the rare setup of having PDO but not MySQLi an error will be triggered. To fix this we must set the AJAX items: Data Source > DB Vendor to a valid setting.

Options/Properties

Basic Attributes

Design Notes

These are notes you can input that help you and other production staff understand your confirmation elements logic and purpose. This text is never used in the live form in any way, it is only for internal development.

Datasource Attributes

When using the Query module, you will most likely be talking to a database other than the one RackForms is installed on. Thus, the fields below are vitally important for making a connection to that external database.

Making this connection is the same as any other PHP->SQL interaction, in that you provide a DB Host, user name, password, etc. The only wrinkle is that RackForms offers a tiered connection model, with each tier providing a slightly different path to the final connection. This tier system ultimately speeds up and secures the development and deployment process, but you should take a few moments to understand how it works.

The RackForms Tiered Connection Model
The first tier is the default config.php file which is based in:
app/movefiles/config.php.

That means If you provide no values for the DB Connector file and DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password fields, then the config.php file in your job folder is used. However, this may not be what we need with an SQL+ job, as we may not always need to write to the RackForms database, which is what the config.php file connects to.

This is so because the connection values included in the 'default' config.php file originate from when you first installed RackForms on your server, that is, the connection information would be the same as your RackForms application.

This makes sense for the Simple SQL module, as the RackForms entry viewer runs on the same database as your RackForms application. However, with the SQL+ module you will have a much greater chance of needing to talk to a different database.

The second tier are the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes. If you place values in these fields the config.php is ignored and the values you set in those boxes used instead.

The third tier is the DB Connector File box. Placing a value in this box means we override the default config.php file, as well as any DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password settings. This is considered the 'safest' model, as we could effectively hide connection information from clients or less privileged production workers by restricting access to the connection file pointed to in this field.

To set up and use this method we must:

a) locate the "original" config.php file in app/movefiles/config.php
b) copy this file to the new location defined by the DB Connector File box
c) update the values in the config.php file to match the database being connected to.

Now when we run the job RackForms will consult the custom config.php file for database connection details. This can be especially handy as it means the user of the job in the RackForms editor doesn't need to know the database details, just the path to the config file.

To summarize: if you leave everything blank, RackForms will include and use the 'default' config.php file. If you place values in the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes, they will be used over the config.php file. Finally, if you include a value in the DB Connector File, the connection variables in that file will override all other values.


DB vendor

Choose the database vendor you will be connecting to for the dynamic query.

DB Host Name

The host of your SQL provider - could be 'localhost', an ip address, or a server/instance name.

Please note that for MSSQL we only use one backslash (\) to separate the server/instance.

DB Schema Name

The Database catalog you wish to query.

DB User Name

Database user name

DB Password

The password for this database connection.

DB Port

Much like DB Socket, this value generally needn't be set. However in those cases where needed, the port value is set here.

DB DSN

This option is only used for ODBC connections. ODBC connections are extremely powerful, as they allow us to connect to virtually any database server in existence. So long as your database provider supports ODBC, you can connect and query it from RackForms.

ODBC connections can be made in one of two ways: Using a data source name, or using a direct connection string, often refereed to as a DSN-less connection.

Using a data source name:

At the most basic level, if we've set up a DSN in say, The ODBC Data Source Administrator application, this value can just be the DSN name.

For example, if we've created a data source name of: Production_Data, the value we place in this box is exactly that: Production_Data. The name alone suffices to point to the proper server and possibly connect, as the bulk of the connection details were defined when we created the data source.

Using a DSN-less Connection String

If we do not have a DSN, this value will be a driver definition block (connection string). The typical use-case here is when we do not have access to, or do not want the extra overhead of, creating and deploying DSN's on our client machines. Connection strings allow us to define the database properties "on the fly", which means the machine we're running on does not need to know about specific ODBC connections.

Connection strings take the form of name value pairs. The one gotcha often associated with these connection strings is the first element of the connection string must define which driver we're using.

For example, Microsoft Access would use:

Driver={Microsoft Access Driver (*.mdb)};Dbq=mdbFilename

SQL Server would be:

Driver={SQL Server Native Client 10.0};Server=server;Database=AdventureWorks;Trusted_Connection=yes;

The key here is the Driver={} part, the name between the braces must be the exact name of the ODBC driver loaded on your machine.

Finding this name is quite easy on Windows, simply load the ODBC Data Source Administrator application and click the Drivers tab. The value in the Name column is what we'll use. In we're running a Mac we can download ands use this tool.

Finally, even with a proper DSN or connection string, we may still have to define a DB User Name and DB Password. If at first you cannot connect, supply these two values and try the form again.



DB Connector File

If you input a relative path in this box, RackForms will use that file for all database connection information for this query.This means you can 'secure' your forms by making sure access to important database information is limited to only those with access to this file.

To create your own DB Connector File, simply copy the contents of your existing config.php file from your /app/movefiles/ directory and paste it into a new file named what ever you like.

You will then need to change the values of the fields shown below to match the values of the datasource you wish to use:

$db_type = 'mysql';
$db_host = 'localhost';
$mysql_socket = '';
$mysql_port = '';
$dbdsn = '';
$db_user = 'RackForms';
$db_pass = 'test';

$db_catalog = 'RackForms';

Now in the DB Connector File field place a path to the file you just created relative to your forms final destination. For example, by default all RackForms output is placed in:

RackForms/output/forms/

Which means if I placed my DB Connector file in:

RackForms/output/forms/config.php

My DB Connector File field would have:

../config.php

as this file would indeed be one directory up from my form, which if was called external_db, would be located at:

RackForms/output/forms/external_db

Now when we run the form RackForms will not use the config.php file located in the job folder (indeed, one will not even be created unless I have a File Upload module in my form that doesn't also have a DB Connector File specified), which means in principal, any users with access to the RackForms job folder will never see any database login info, and so long as they did not have access to the folder with the External DB Connector File, not see any DB information period.

MySQL SSL Options

RackForms supports secure MySQL connections using SSL. To enable this functionality your MySQL Server will need to support SSL connections, which usually means modifying your servers my.cnf file to supply locations to your ca, server-cert, and server-key file. You may also, depending on your MySQL version, need to enable SSL connectivity. Learn more about this process and settings here.

As a very brief demo, the my.cnf settings we use internally are as follows:

ssl-ca=/mysql-ssl/ca.pem
ssl-cert=/mysql-ssl/server-cert.pem
ssl-key=/mysql-ssl/server-key.pem
ssl

Once the server has been configured to allow SSL connections, RackForms will need to know the location of three key files. These items are described below. To keep things simple, the names used in this document assume we've created our keys using the process described here. Please note the .PEM file locations can be relative or absolute.

MySQL Client Key File

The client key file will be called client-key.pem. This is our private key used in the SSL handshake process.

MySQL Client Certificate

The client certificate will be called client-cert.pem, and is used to publicly identify us using the CA file on record.

CA Cert

The last file will be called ca.pem, and is a "shared" file between the server and client, as in we use the same file.

As an example then, if I created a folder called mysql-ssl and placed my .PEM files within, the values I'd use in RackForms would be:

MySQL Client Key File - /mysql-ssl/client-key.pem
MySQL Client Certificate - /mysql-ssl/client-cert.pem
CA Cert - /mysql-ssl/ca.pem

Important Notes On MySQL and SSL Support

Using localhost: First, when using a localhost setup we'll often use localhost as our Database Host Name. The problem with this is on a local machine localhost uses sockets instead of TCP/IP, which means we're trying to encrypt a local communication channel. This simply doesn't work and we're receive a "doesn't support SSL/crypto" message. The solution here is to use 127.0.0.1 as our host name, which forces TCP/IP on a local connection.

Using Self-Signed Certificates: One common approach we may take when using SSL and MySQL for the first time is to set up a test instance on a localhost machine using self-signed certificates. This is great in concept but unfortunately creates some unique challenges. When we try and connect we may be greeted with the error:

SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

The problem here is new security features in PHP 5.6 and above, along with the MySQL native driver, creates verification issues when using self-signed certs. If this is the case we can disable certificate validation using the Verify MySQL SSL Server Cert option.

Verify MySQL SSL Server Cert

As noted above, when using self-signed certificates it may be necessary to disable certificate peer validation. This option should always be set to Yes in production environments, but if you have problems connecting on a local network or machine, try the No setting.

It's very important to note setting this to No completely defeats the purpose of using SSL, and should only be done on a non-production, local machine.

If you've made it this far we should only have one final consideration, the encryption cipher:

MySQL SSL Cipher

When connecting to a MySQL server using SSL, the client and server need to negotiate an encryption protocol and cipher. In the best case scenario this happens automatically, and the end result is the server automatically selects the most secure grouping possible.

Unfortunately this doesn't always work (especially when using self-signed certificates or builds of MySQL compiled against older versions of OpenSSL). The most common case is when your MySQL server only supports TLS V1, in which case the default cipher (DHE-RSA-AES128-GCM-SHA256), is not supported. If this happens you'll need to choose a different cipher from this list. The best way to do this is to start with the lowest value in the list, then work your way up until it fails again. Then, back down to the last working option.

The general rule will be if the default option fails, none of the 256-bit variants will work. This is usually considered a security issue, and you should take steps to update your MySQL server and/or PHP version to ones that support the latest security protocols.

 

Apply Settings To All Datasource Items...

This handy little button copies the current fields datasource settings to all datasource enabled fields in the job.

This is a big time-saver when we have many datasource items in the job that require the same settings.

AJAX Options

SQL Debug/Data Return Mode

This field will allow you to view the HTTP Response object for any error messages. Pleas note however, that you will need to have FireBug or some other HTTP Request viewer for these messages to be visible.

SQL Code

Sets the currency type of the transaction. If you need a currency type added, please use the contact page to request one!

Code To Run Before Query

This code, which must be raw JavaScript, runs just before the HTTP Request. This can be useful for checking the state of a page and showing/hiding fields based on this information.

Code To Run After Query

This feature allows us to run custom JavaScript code upon a successful AJAX query. It is important to note that this code must be raw JavaScript, in that we do not include <script> tags.

It is also important to note that this code only runs after a successful query, that is, one that returns results. Technically, Prototype JS uses a custom event handler: onComplete: to handle a successful query, and the code we add with this control is placed at the very end of that event handler.

One very handy use for this feature is to create code to update specific parts of our form that aren't nessesarily input fields.

For example, a common need is to update an HTML <span> or other text element with a value. We do not, in other words, need to update an input field, but rather just show some related value to the user.

In order to grab such display values out it's important to note that:

All AJAX queries return results to a variable called: res

res is an array, with each index containing an object that holds an index (always 0) to your fields value, as well as the database Column name that can be accessed via standard dot notation.

The number of indexes the array has is determined by the number of results our AJAX query returns.

This is best explained in a sample job, of which you can find under AJAX > customize-ajax-results.xml

The short version though is that so long as we know that a variable called res contains our data, we can refer to the rows data with one of the following (or both) syntaxes:

res[0][0]

res[0].your_fields_database_column_name

One final hint is that using FireBug we can enable the console and watch our JSON results populate the log. This is a great way to see a nice structured listing of all results, though we can create our own code to view raw results as well.

For example we could add the following code to the Code To Run After Query box:

var d = new Element('div', { 'class': 'ajax-debug', 'id': 'ajax-debug' }).update("AJAX Debug");

if(res) { // always check to see if res is set, if not we had an exception or an empty result.

    res.each(function(i){
        label = i[0];
    
        var br = new Element('br', { }); d.appendChild(br);
        var e = new Element('span', { 'class': 'ajax-debug-item', 'id': ''  }).update(label);
        
        d.insert(e);
    });
    
}

$(document.body).appendChild(d);
            

Please note how we check for the existence of res before using it! This is an very important step, as we can never assume that res will exist. Network errors like timeouts, faulty data and so on, can all place this variable's existence in jeopardy, checking is a simple step that will ensure our users do not experience unnecessary errors.

How RackForms AJAX Calls Return Data

Knowing how RackForms returns AJAX data is essential for creating custom logic. The basic idea is as already noted RackForms returns the result of a call to a JavaScript variable called res. res, in turn, is created by eval()'ing the JSON response of an AJAX call.

We can view this JSON data (and hence, the res variable data) in the Firefox Firebug console (or any other browser that shows the result of HTTP Request calls) like so (note how we've clicked the JSON tab to get a nicely formatted view of our data):

json-result

So in this case res contains 5 'rows' of data, each row contains several items. The top or first item is the main select display element, and is created by default so that our select element retains the same appearance as before the AJAX call. For most purposes we can ignore this element.

The next rows contain the actual AJAX database call result. Note that each 'row' contains an indexed and named item. This means if we do not know the name of a field we can use the index. In most cases we will know the name of the field, as these names simply come from the database columns our SQL Code defined via the SELECT statement.

In conclusion then when developing custom AJAX logic it may be helpful to visualize the actual data being returned. Using a web browsers HTTP Request facilities we can easily do so.

AJAX Result Processing Library
This option allows us to set which JavaScript library processes our Ajax call. The default is jQuery, through we can also use Prototype.JS. The behavior between the two libraries is identical, only with jQuery we're ensured better compatibility with future builds of RackForms.

The main reason we'd want to change this value is if we're using custom JavaScript code in our forms and we want to ensure the same library is used for your code and that code RackForms generates for the AJAX module.

AJAX Result Processing Mode
This powerful feature lets us process standard RackForms tokens in the AJAX result text. The following tokens are supported:

S{sesion_element_name}

F{form_field_name}

Thus, we could include this code in the text our AJAX call returns:

Thank you for using the AJAX Module S{name}!

...and so long as we have a session element already set with a value, the AJAX module will substitute the value of the token and return the modified string.

To see this logic in action please load the sample job: ajax-result-processing-mode. In this job we step through the process of creating an AJAX item that processes a dynamic token call using a PHP SESSION element.

AJAX Result Error Mode

This option displays any error messages in dialog boxes resulting from faulty AJAX calls.

Technically, we trap any error using Prototypes onException() callback, which means we can trap network issues, but more commonly, any problems with bad AJAX result sets. This means if we return an empty result set when we should always have one we would display an error to this affect.

By default this option is set to not display errors.

Append Label

Along with this item, the next two 'Append' items were designed to give you greater control over the results returned and how you can use them. The basic idea is that when you query the database it could be useful to append a value to the front or back of the result set.

The main use of this item for AJAX queries is to add a 'default' value to the select item you're posting to.

These fields then are broken into two main parts: the label and value.

The label, this field, is what would be set as the label of the form field. Technically speaking, this is the first index of the array (the [0] position)

Append Value

Similar to Append Label, this is the value or as far as the raw array is concerned, the [1] position.

Append Position

The last option for appending values is the append position. We have three choices; do not append, append to the front of the array, and append to the back.

 

AJAX Update Binding Options
Here is we actually set the AJAX properties. One of the most powerful features of this implementation is that we can create as many bindings as needed.

Watch This Field...

This is the form field we'll watch for update events as set in the next item. It is important to note that the event we chose should make sense for the item type.

Please note that as of Build 691 we can watch an auto-suggest field. However, their are important limitations and items you should know about before you do!

Using This Event:

The JavaScript event that will trigger the AJAX POST call.

Please note that as of Build 691 we can use auto-suggest fields to drive AJAX queries. However, due to a specific requirement of the auto-suggest logic, we must set this value to onblur to work. Please see this link for more.

AJAX Additional Bind 2-5

These fields, new to Build 715, allow us to add additional database field bindings to an AJAX call. This is handy in cases where having a single binding is not enough to describe the data that must be pulled from the database.

For example, if we need to chain ajax calls together, we might need the result of a previous AJAX call to properly grab a new result.

Another scenario would be using other forms fields entirely, that is, ones not directly involved with the AJAX logic. For example, you could have a hidden field that stores a user id number, this id number would help drive better AJAX results.

To use these fields, we must supply the Name/Value of the field we wish to grab a value from.

These fields are intelligent in that if we do not supply a value to one of the fields the AJAX query will still work, though without the added filtering provided by the fields value.

Most importantly, these fields are only meaningful if we supply a more complex SQL query in term of our WHERE clause. Thus, a WHERE clause with only 1 element will not use any additional binds, whereas one with two would. For example:

SELECT name, id FROM fb_demo WHERE age = ? OR age = ?

We can see this new feature in action in Load Example Job > AJAX > ajax-additional-args

AJAX Query Result Population Mode:

This option lets us define what happens when a query returns results and populates its bound fields. By default we clear all existing field values, be it text from a text field or all existing radio or checkbox items.

In most cases this is the preferred behavior, but their may be times when clearing old values is not desired. If this is the case, we want to set this field to Retain Existing Fields/Values.

Why the two settings?

Imagine we have a text field that's being populated by an AJAX query. When we populate this text field with a new value from an AJAX call we obviously do not want to save or append to the old value. However, what if our AJAX query returns no results? If we automatically clear the old value our users may be left confused as to why a form field element has decided to clear itself of a value. This is especially true if we're creating auto-suggest type logic.

A sample job detailing this scenario and why the AJAX Query Result Population Mode setting is important can be found in the editor under: Load Example Job > AJAX > ajax-clear-null-results

Another common scenario is for checkbox and radio items. By default an AJAX result will clear all old checkbox and radio items. However, what happens if we do not want to clear old values, but just keep appending them? If this is the case, then setting this field field to Retain Existing Fields/Values would be desired.

Of course this would be rare, as again, this type of behavior would be confusing to most users. However, this brings up an important point: what happens if we have preexisting values in a checkbox or radio item array that did not come from our AJAX query?

In most cases we've added these values for a reason, and we would not want an AJAX query to clear them out. If this is the case, then we want to use Retain Existing Fields/Values, as this way, while we'll retain old values from previous queries, we will not remove preexisting values.

That said, it's important to note that RackForms will not re-populate existing items when in Retain Existing Fields/Values mode. That is, if we have an existing checkbox value of 'Matt', and the AJAX query has a result item with the same value, 'Matt' will not be added twice.

That, in essence, is what this option controls. Do we clear the value when we have no valid values or do we leave it be.

Ignore First Parameter

This feature allows us to ignore the first parameter of an AJAX operation. The implications of this are quite important and open up huge possibilities for form creators.

Traditionally, when we fire off an ajax request we automatically bind the item that triggers the request as the first parameter of the database call. So for example, if we have a select item with names, the value of the selected name in that select item is the first parameter of the ajax call. This makes writing and using Ajax queries simple, but basically prevents us from using buttons as the Ajax event trigger.

This new option says that instead of doing that, we ignore that first or "trigger" parameter and instead only use the values passed via the Ajax Additional Binds boxes. As noted above the best example of this is using a Button element to fire off an Ajax query.

For example, imagine a form where we provide two select items, one for Album Name and the other for Artist Name. The user provides to each, and then, to populate a third text field, clicks a button element. Without this feature we'd pass the value of the button element to the Ajax call, which is almost certainly of no use to the Ajax logic. Now, however, we can skip that element and just use the fields we want!

Item Options
These are the AJAX bindings well use when an AJAX request occurs.

Bind This Field...

This is field that will receive the AJAX response. Once we have this item set, we need to set which values we want for the Label and Value are.

You'll notice when we click this item a list of elements appears broken into two main categories:

Form Fields - All form elements on a page that receive their ID value from the fields Name/Value property. This is the 'classic' behavior of this field, and is used when we want to monitor a form field for changes.

Display Elements - New to Build 700, these id's are automatically harvested by RackForms from the first instance of a label field that possess an id="" attribute. That is, for any form element that has the label property (which includes all forms fields and text elements) if we add HTML code such as a div and assign an id to that div as in:

<div id="sample">Some Text</div>

RackForms will add this id to the list of fields the AJAX module can update with AJAX content! Thus, we could have a select item trigger a content update on a random div in our form, and so on.

Please note that at this time the only binding used to update our element is the Label As... property, described below.

Important Note: As noted above, RackForms will only find the first instance of an id="" declaration. This means if we have several nested divs with multiple id="" declarations RackForms will not add that element to the list of items we can dynamically update. The rule of thumb is to keep our nested structures simple.

Label/Element As...

This is the value that will show as the label of our Select items, and the primary value of any text based item such as text boxes and text areas.

Please note that if we're binding a non-form field element such as a DIV, this is the value that is always used to populate the value, not the Value To... option.

Value To...

Required for selection based items, this will become the HTML value of the field. By default this field is set to be blank, so take care to set a value (binding) for this item!

As noted below, when using select based items such as select and check box items, we can 'chain' AJAX calls together by putting, for example, an ID field that relates to another queries label field.

Important Notes And Limitations

As of Build 675 RackForms handles the following form items: [text, textarea, radio, select, checkbox]

Most items, particularly Select items, work as you would expect. However, their are caveats for the other field types.

First, text items, as you would expect, can only receive 1 value, the Label, which is unlike Select items which can have a separate Label and Value. Also, Text items can only receive 1 value in total. Thus, if you have a SQL statement that will return more than 1 row, you should use a select item to store the extra values. By design, should a bound text box item receive multiple items, only the last item is shown.

Get Creative!
The AJAX Field is quite flexible, allowing us to do some pretty neat stuff.

For example, we can 'chain' AJAX enabled fields together to create smart forms. Doing so allows us to create some really dynamic form logic. For example, we could have two select boxes and a text box. The first select item updates the second select item, and then the second select item updates the text box!

To see this type of logic in action, please check out the sample job:

\RackForms\goodies\Sample Jobs\AJAX\ajax-chain-calls.xml


So many features, So little time