Mapping submission info to other database tables

The RSform!Pro Mapping feature can be accessed from within the Components > RSForm!Pro > Manage Forms > edit a form > Properties: Mappings area.

Mapping configuration

1. Connect to the database you wish to map the data to

Clicking on the New button on the Mappings area will display a configuration window that will allow you to set the connection type and the method that should be used:

• Local: A local connection type implies that the table that the data will be mapped to will be inside your current Joomla! database (runs on the same database as your RSform!Pro installation).
• Remote: The remote connection type implies that the destination database table is located on a different database (different server or outside the Joomla! databse). Choosing the Remote type will need some additional configuration details: Host (connection URL), Username, Password, Database (name).

2. Mapping type

The mapping process can perform one of the following three MySQL actions: Insert, Replace, Update and Delete. So, for example, if a Insert method is used a new table row (record) will be created.

3. Table selection

After the connection type and mapping method, you should click the Connect to database button. If the connection is successful a Database table drop-down selector should be displayed (otherwise an unsuccessful connection message). This will allow you to select the destination database table (the list is automatically populated with the database tables).

4. Field mapping

Selecting a table will display a list of all columns that can be mapped for that table. This step simply implies telling the mapping tool, what RSform!Pro field should go into what column.

Clicking inside a table field will display a list of your available RSform!Pro field placeholders. One should take caution when performing the field mapping as field types are an important factor. For example, if the database column requires a numeric input, then the RSform!Pro field should only contain numeric values.

Using the RSform!Pro Mapping feature you can set multiple mapping actions with various actions (insert, replace, update or delete), to various tables (local or remote).

The {last_insert_id} placeholder returns the automatic id number generated by the most recent INSERT query. You can use the {last_insert_id_1} placeholder to retrieve the auto-incremented ID from the first query, {last_insert_id_2} for the second one and so on. You can also use the {empty} placeholder to insert an empty string.

Available placeholders

Aside from the value placeholders associated with each field (for example {fieldname:value}), the following placeholders can also be used:

• {global:formid} - The Id of the form
• {global:username} - If the user is registered to your Joomla website, his username will appear when using this placeholder
• {global:userid} - If the user is registered to your Joomla website, his user id will appear when using this placeholder
• {global:userip} - The IP of the user that submitted the form
• {global:useremail} - If the user is registered to your Joomla website, his email will appear when using this placeholder
• {global:fullname} - If the user is registered to your Joomla website, his fullname will appear when using this placeholder
• {global:sitename} - The name of your site, as defined in your Joomla configuration
• {global:siteurl} - The URL of your site
• {global:mailfrom} - The Global Configuration From Email address.
• {global:fromname} - The Global Configuration From Name.
• {global:confirmation} - The URL of used for the submission confirmation
• {global:confirmation_hash} - Returns the confirmation calculated hash
• {global:confirmedip} - The IP of the confirmed submission
• {global:confirmeddate} - The date of the confirmed submission
• {global:deletion} - The Global placeholder that can be used in order to delete the submission.
• {global:deletion_hash} - Returns the deletion calculated hash
• {global:submissionid} - The id of the submission
• {global:submission_id} - The id of the submission
• {global:date_added} - The date the user submitted the form
• {global:utc_date_added} - The date the user submitted the form, in UTC SQL format
• {global:language} - The language code a submission was made in.
• {global:formtitle} - The configured form's title.

5. Dynamically control the mapping values

You can also use the Scripts called after form has been processed field in order to better control/edit the information that will be mapped. The $mappings variable available in this field stores all the information that will be processed by the mappings plugin. The following example strips any HTML code from the values that will be mapped before the mappings plugin is triggered:

foreach($mappings as $mapping)
{
  $data = unserialize($mapping->data);
  $data["field1"] = strip_tags($_POST['form']['first_field_name']);
  $data["field2"] = strip_tags($_POST['form']['second_field_name']);
  $mapping->data = serialize($data);
}

The initial values from the $mappings variable are serialized strings, in order to properly edit the values you will first need to convert them to PHP values, this is done using the unserialize function(). After the values are edited you will need to convert them back to serialized strings using the serialize() function.