RackForms v7.7.3
sql+
Site Tag Line
Latest Release: Build 942 - April 3, 2021
Contact Us  Purchase Options Purchase Options Contact Us
header-image


 
Top Link
Sortable Page SQL Plus


In Brief: Send the result of this forms user specified fields to an external database schema.

Dependencies: A valid SQL results schema.

Hints & Tricks: Use this module to send the results of a form submission to a database of your choosing. You are responsible for specifying what fields are sent and the SQL to handle the result data on both the RackForms and Database end.

Make sure your SQL Variables list does not have extra (trailing) commas in it.

Correct:

var1, var2

Incorrect:

var1, var2,

Options/Properties

Basic Attributes

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.

Data Source

When using the SQL+ 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.
Important Note: Please note that as of Build 687 this item has been expanded to: MySQL (PDO), MSSQL, and now, MySQLi. Previously this item did not allow us to select which MySQL driver we needed, which would lead to an error if PDO wasn't installed on the server, as PDO was always set in this case. Now, by selecting MySQLi, we ensure this is the driver used.

DB Host Name

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 Schema Name

The Database catalog you wish to query.

DB User Name

Database user name

DB Password

The password for this database connection.

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 = '127.0.0.1';
$mysql_socket = '';
$mysql_port = '';
$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.

 

Confirmation/Query Condition

Confirmation Condition PHP Code

This field allows you to specify a condition which must be met for the confirmation field to be processed.

We can create code manually, or use the Condition Wizard.

Condition Wizard

If you're new to using conditions, or simply want to have RackForms generate the condition code for you, the wizard is a fast and easy way to do so. To create a confirmation condition, simply follow the wizard from top to bottom.

Start by mousing over the This Field: select item. All fields in your form will display, select the item you want to base the condition on.

Please note if we select a Checkbox field an additional select item will display, Having This Checkbox Value (optional):. This select item will display all possible values for that checkbox. Select which value you want to base the condition on. When you do, the condition process is complete.

For all other fields, the next step is to set the Must: field. This will take the value of Equal or Not Equal, and several others.

Finally, type into the This Value: box to set which value the form field we selected above will validate against.

If you want to add additional conditions, such as a name must be Equal to Steve and age less than 10, first select the Additional Condition button and start from the top of the wizard again.

Hand Coding Conditional Code

The logic takes the form of:

Variable 1 | Condition | Variable 2

For example, let's assume we have a radio item in our form with the Name/Value of opt_1. The radio item has two possible vales, 'Yes' and 'No'. If the value of opt_1 is 'Yes' we want to send an email, if no, we do not.

We would write the Confirmation Condition PHP Code as such:

#{opt_1} == 'Yes'

In other words, we use the token for our field variable like we would in other property boxes, that is, a pound sign (#) followed by the Name/Value of the field in braces. Recall that at run time this token evaluates to the value set by our forms users, which in this case will be the value of the radio button with the Name/Value of opt_1.

We then set the comparison, in this case out comparison is equal too, denoted with the double == sign. Finally, because we know our radio item is a string value that can be 'Yes' or 'No', we wrap the value we want to check for in single tick marks.

On the raw code side, RackForms wraps the token call in an isset() block, which is further wrapped in the proper PHP if() syntax:

if(isset($_SESSION['qs']["{$_SESSION['entry_key']}"]['opt_1']) && $_SESSION['qs']["{$_SESSION['entry_key']}"]['opt_1'] == 'Yes') { // condition code start

When run, if the users set value for the radio box was 'Yes', we would process this confirmation element.

Using Array Based Form Elements (Checkboxes)
PHP Treats checkbox items a little differently than other elements. The good news is the only element that needs this special attention is a checkbox field. The difference then, is that in order to use checkbox items we need to place an index indicator just after the form field name.

For example, lets say we have a checkbox field with the Name/Value: email_condition, and it has two elements: Recipient A and Recipient B

To process this field in our condition statement we append the proper index in the format of [index-number]. It's quite simple when you see it in practice:

#{email_condition[0]} == 'Recipient A'

#{email_condition[1]} == 'Recipient B'

Please note the [0] and [1]. These are the index positions of the field being checked. Compare this to a standard field token:

#{email_condition} == 'Recipient A'

A checkbox group has several items, the [0] and [1] bit tell RackForms which number item to grab.

How do we know which number to use? In our example the checkbox field has two values, and array indexs start with 0. Thus, the first field, Recipient A, is [0], and Recipient B is [1].

The rule then is our numbering simply adds 1 for every checkbox item in the group, and always starts at 0.

SQL+

SQL Code

The SQL code to run on your database. Code can be in the form of inline query or stored procedure. In either case, queries are parameterized for the benefit of security. This means your queries must take the form of:

Stored Procedure Call:
call sample_insert(?,?)

Note: SQL Server cannot call a another stored procedure from the main proc. If this type of logic is needed, simply call two stored procedures.

With the stored procedure code being (MySQL sample):

DEFINER = CURRENT_USER
PROCEDURE db.sample_insert(IN in_name VARCHAR(20), IN in_age INT)
BEGIN
INSERT INTO sample_insert_table (name, age) VALUES (in_name, in_age);
END


Or Inline SQL as in:

INSERT INTO sample_insert_table(name, age) VALUES (?,?)

In short, all form variables take the form of a question mark, these question marks telling your database their will be real values substituted for these stand-ins, which we cover in the next section, 'Variables'.

Complex and Non-Standard INSERT Statement
It should be noted that the ability to call Stored Procedures opens a world of possibility with regard to complex and non-standard INSERTS.

For example, you may have a column in your database called date which requires a specialized format that the standard RackForms Calendar field does not support. You decide to create three drop-down menu's instead, but you do not want three separate values in the date column, only one.

To solve this problem we would create a stored procedure that takes our three fields and combines them into one. This forum topic describes how.

The main take away is that RackForms gives you a huge amount of control when dealing with the actual process of INSERT'ing data. Stored Procedure's and custom INSERT code is just one of the many ways this is so.

Variables

Because RackForms uses parameterized queries, you must set the values of the stand-in variables defined above in the SQL field list created above. We accomplish this by providing a comma delimited list of the <name> attributes of the form field items we want sent to the database. As of build 586, we can also include local PHP variables. Let's look at each in turn:

Form Elements
Form elements will be the most common variable type given, simply because we are most likely gong to be interested in INSERTING the values our form user has supplied more than anything thing else. For example, in the SQL+ section above we could have this simple inline query:

INSERT INTO sample_insert_table(name, age) VALUES (?,?)

In our RackForms form we have two text fields that have id's of <name> and <age>. We would thus set the value of the Variables field as:

name,age

RackForms will then process these variables at form submission time and send the result to your database. This is the same whether you're using inline SQL or stored procedures.

PHP Variables
We can also supply the Variables list with local PHP variables. To do so we insert the PHP variable name without the dollar sign wrapped up in a token in the format of:

${php_variable_name}

For example, let's say I create a PHP variable like so and place it into the Page PHP Top Code text box:

<?php
$text1 = 'tester text 1';
?>

To retrieve this variable at runtime, I would use the following text in my SQL Variables text box:

${text1}

Notice we do not use a dollar sign in the variable name e.g., we use ${text1} not ${$text1}

One final point is that we only have access to local PHP variables, in that we cannot list a variable that has been used a previous page, nor can we use $_SESSION variables. The only valid variables are those we define on the same page as our SQL+ module is running in the format of $variable = 'value'.

$_SESSION Variables
To use $_SESSION variables in your SQL+ Variables list you will first need to assign the $_SESSION variable to a local PHP variable with something like this in the Page PHP Top Code area:

<?php
session_start();
$session_var =
$_SESSION['sample'];
?>

We would then be able to use the $session_var PHP variable as described above.

This is necessary because as of right now RackForms does not yet support direct inclusion of $_SESSION variables in the SQL+ Variables list. However, this restriction may be relaxed in a future build.

IMPORTANT NOTE OF USING $_SESSION VARIABLES

One thing to keep in mind--if you use $_SESSION variables please be sure to use:

unset($_SESSION['variable_name']);

In the Page PHP Footer Template Code part of your confirmation page or the variable will persist and possibly cause strange behavior!

TIMESTAMPS AND META DATA
RackForms allows you to capture and insert various data from your PHP session, as well as easing the insertion of common Database elements.

On the PHP data side you have SESSION_ID and visitor_ip/REMOTE_ADDR. Use any of these keywords to insert the related data item. For example, typing the keyword SESSION_ID in the Variables text area will pull the PHP SESSION_ID variable for that session.

To grab the visitors ip address, use either visitor or REMOTE_ADDR. Their are two keywords for the same attribute, as some users will have an easier time remember one over the other.

The other main meta data set is common Database items for capturing time information. You can use the variable keyword: TIMESTAMP to insert a UNIX timestamp into the field of your choice, or: NOW(), DEFAULT_TIMESTAMP, or DATETIME to insert a SQL DATETIME value (YYYY-MM-DD HH:MM:SS) into the database.

For example, the following SQL Code:
INSERT INTO fb_sample (id, manual_ts,manual_dt) VALUES(?,?,?)

With these parameter values (Variables Field):
id,TIMESTAMP,NOW()

Would insert something similar to:
3, 1224637303, 10/21/2008 8:01:43

File Uploads and Meta Data
To help make the process of file uploads easier when dealing with custom SQL statements, RackForms features several handy shortcuts.

If you need to insert a user IP address into your Database record, use one of the following aliases: REMOTE_ADDR or visitor_ip. Using eiether of these key words will retireve the remote ip address of the form user and place into your Database record.

For starters, the process of including a file upload element in your SQL+ statement is as simple as including the field id of the file upload item. For example, if you created a video upload element and gave it an id of 'video_file', simple include the text 'video_file' in your SQL+ Variables list to include that file. So long as you have an appropriate SQL column to handle such data, your good to go. This includes all file upload formats: All Files, Images Only, and Files Only.

In addition, RackForms automatically tags file uploads with three pieces of meta data:
_name
_mime
_size
A fourth item: _thumb is available when you choose the 'Images Only' option from the Allowed File Types drop down of the File Field element attribute editor.

To access these meta data, simply prefix the meta name with the field id of the file upload element in question. For example, our 'video_file' item above would need 'video_file_name' to access the original file name attribute.

For thumbnail images, we would use {item_name}_thumb.

As an example, this is what our SQL Code and Variables elements would look like if we wanted to upload an image along with a thumbnail for a field item with an id of: image

SQL+ Code:
INSERT INTO result (image, image_thumb, image_name, image_mime, image_size) VALUES (?,?,?,?)

Variables:
image,image_thumb,image_name,image_mime,image_size

Flat File Save Name
When you check the "Save As Flat file" checkbox, their is a possibility the name of a file uploaded by one user will be the same as one already in the file system. Thus, if RackForms finds a new file matches an existing one it will rename the flat file, and also update the _name meta field with this new name. That way your flat file's will match the database record.

How RackForms Handles NULL (Empty) Values Important!

When we design a database table one of the options all fields have is the ability to accept or reject NULL values via the NOT NULL keyword.

In many cases this is an important property to set, as it forces what we call Data Integrity. That is, something else requires a value for this field in order to function properly, in no case should we ever allow blank values, as that will break that something else.

Data Integrity is a very good thing, but a subtle problem can arise if we designate a database field (column) as NOT NULL, but then in RackForms, attempt to feed in a value that is in fact, NULL.

From the standpoint of RackForms then the rule is this: Any field that has no value is seen as NULL by the Database if we include that field in a SQL+ statement.

Thus, if we have such a value and that value is not required by the form, our SQL+ statement will fail silently if we attempt to submit the form through. This is because the database expects a value to be set for that field, and RackForms correctly passes empty values as NULLS.

This may seem limiting at first, but it's important to note not doing so would mean RackForms actually subverts your database's data integrity. Instead, RackForms and the database work together to make sure if a field requires a value, it should get it.

Finally, it's very important to note the error that's triggered is silent when using MySQLi. That is, we will not get a warning about the INSERT operations failure for subtle technical reasons.

Set lastInsertId() = $ret_val

This field allows you to retrieve the lastInsertId via a PHP variable called:

$ret_val

And as Of Build 757, Result Set Variable Name + _ret_val as in:

$fb_result_ret_val

That is, the value we use in the Result Set Variable Name / RET_VAL Prefix field + ret_val.

You could then use this variable to insert into a second database call, email message, link to a record edit page, and so on.

It should also be mentioned that if you do not have this field checked, $ret_val will be set with a 0 or 1 depending on the database call's success.

A few notes:

  • The field is only valid for SQL queries performed in native PHP 5, that is, queries where MySQL 4.1 Compatibility Mode is not enabled.
  • It is also only valid is the database table we've performed the INSERT into uses an auto-increment field.
  • The $ret_val variable is only accessible to confirmation modules created after this one. In other words, if you need to pass $ret_val for an Email+ call, create the SQL+ module first.
  • The $ret_val PHP variable name is separate from the Result Set Variable Name property, described below. That is, we do not name this element, it is always $ret_val.
  • If we enable Return Full Result Set, ret_val is disabled.
  • This field is used in Bucket Repeater logic where we need to return the unique entry id to the bucket item table.

Access Later Via PHP SESSION

As of RackForms 3 Build 780, we now save this value via a PHP session variable called:

$_SESSION['sqlplus_retval']

This makes it easy to retrieve the value in a different form submission, such as one where we need this value as a foreign key value,

It's important to note however, that any addition SQL+ items will overwrite the previous value, so make sure the first SQL+ items on your form is the one that uses the value. As a workaround, we can also use a session token to grab the value and assign a different identifier to it in our PHP Top Code block.

Return Full Result Set

New in Build 640, this feature allows you to query your database for data, as opposed to simply INSERT'ing data into it.

Please note that while this feature is very handy for quick jobs, you may want to consider using a Query module item instead for larger jobs.

Result Set Variable Name / RET_VAL Prefix

This important field is how you define the name of the PHP variable any result value will be assigned to. This field can take on two main values:

1. The first is an INT value if you have the Return lastInsertId() checkbox checked (please see below).

2. If the lastInsertId() checkbox is left unchecked you get an array of any values returned by the query. It is important to note this array is created using the PDO::FETCH_BOTH constant, which means every field item in the array has a numerical and INT based index.

This is important because when creating an SQL+ result for an element you may not know or be able to specify the field name as you would in an associative array, in those cases you can simply use the numerical index.

Most importantly, it is important to note that PDO creates an array of array's in that each result row is a key in the 'main' result array, with each 'row' being itself an array. This means if you want to retrieve a single value from the first row of a result set you need to specify the first index as in:

$result_var[0]["db_field"];

That is, the name of the PHP variable which holds the result, the index of the row as defined in the brackets [0], and the database field name.

Finally, you will notice that when you set this name or change it the field item in your Form edit area will update accordingly.

Bucket Repeater Processing Mode

This select item is needed to enable Bucket Processing when we wish to process a Bucket Repeaters. By default we do not process buckets with an SQL+ item, which means the default value of No Bucket Processing is set. However, if we've added a Bucket that has Bucket Repeater > Repeating Bucket Logic Enabled? set to enabled this must be set to a non-default value. At this time we only have one option, though more may be added as time goes on:

1 Row Per Duplicated Item - This mode means for each plus button click our users apply to a form the contents of that Bucket will INSERT into 1 database row. This is by far the most common operation we'll wish to use, as it means unique entries remain just that - unique.

1 Row Per Duplicated Item + Original Item - This mode is exactly the same as above, only now we include the first, non-duplicated bucket in the results. This is handy for times when we want to group all items together instead of possibly breaking them into two tables.

Process This Bucket...

This select item dynamically updates based on the contents of the current form. Generally speaking we simply select the bucket we wish to process from the list.

Using This SQL+ Last Insert ID Return Value...

Key to using the Bucket Repeater logic is knowing that at some point we'll usually want to grab our repeater rows of data back out and relate them to some larger form entry.

For example, a birthday cerebration form allows users to add an arbitrary number of guests. The parent or guardian filling out the form is the main entry, which means any guests they add should be related to their entry key. Of course when they're related the person in charge of setting up the guest list will be able to look up a user and see the guests they plan on bringing.

The 'main' entry key usually comes from a database table that has a column set as a Primary Key INT field that Auto Increments. That is to say, each entry to that form will have a unique, auto-incrementing identifier. Because this key is unique to that user any related entries to a different table that carry that same key will relate back to the original entry.

In practice then to implement a proper Bucket Repeater form we add two SQL+ items to a form, the first one inserts the main, non-bucket repeater data and has Set lastInsertId() = $ret_val checked so that when we INSERT the data, the PHP variable $ret_val holds the unique entry key generated automatically by the database..

As we now have a unique value in $ret_val we then set the second SQL+ item to INSERT the needed bucket repeater items using the $ret_val PHP variable as the value for this field.

Of course our database will need to have a column this value can be inserted into, such as an INT field called entry_key.

Important! - In order for the Last Insert ID logic to work, we must specify the $ret_val variable name in two places:

1. The SQL Code Block as the name of the column and parameter placeholder:

INSERT INTO fb_demo (name, age, ret_val) VALUES (?,?,?)

2. The associated Variables entry as a dynamic token:

F{Name}, F{Age}, ${ret_val}

In other words, this SQL+ item will grab the last insert ID value, via $ret_val, from our initial INSERT SQL+ item, and use that value as the foreign key to relate its inserted rows with the main entry.

If we fail to do this our SQL+ statement will fail, usually with a warning about an incorrect number of parameters.

Finally, it's key to note this field must be a valid PHP varable, as in $ret_val, $entry_id, and so on.

To see SQL Bucket Repeaters in action please load the bucket-repeaters-sql-demo demo job in the editor.

SQL+ Display Name

New to Build 700, this feature allows us to name each SQL+ item and have this name display in the SQL+ item on the form page.

Array Based Variables Separator

New to Build 638, this option lets you set the value of the array based variable separator.

An example of an array based variable would be any checkbox items you have in your form. The reason why this option exists is PHP treats check box items as arrays, thus, when RackForms processes the check box form item during submission it needs to create a logical separation between each item. This is because if we have multiple items we do not create multiple database entire's for each checkbox item, as this would create logistical problems with the rest of the forms data. We instead glue the items together and then break (explode) then when needed.

This value defaults to the | pipe symbol, as this is a very rare character in normal data sets. However, it could also be a comma if you can say with certainty the data being entered is integer based, and so on.The key is to make sure the character being used will never appear in the data entered by the forms users.

It is recommended you keep using this variable as the default, as this is the native separator used in the Builder Repeater Module for checkbox items. That is, if left as a PIPE RackForms will be able to use this field in Builder items with no extra work needed.

If we need to process items manually, we would query the database table field that has our character delimited list and use the explode() PHP function to create an array of the values entered by the user. We would pass the explode function the character used (the delimiter), and we would get back an array with each value in an array item.

MySQL 4.1 Compatibility Mode

Checking this box means the php_mysql extension will be used to submit your SQL query rather than the standard pdo_mysql or php_sqlsrv_xx. This has the benefit of being MySQL 4.1 compliant. Please be sure you have this extension loaded before using this mode.

In order to use this mode, you will have format your query parameters to work with the sprintf php function. For example, a call with four parameters would be:

INSERT INTO fb_sample (id,name, manual_ts,manual_dt) VALUES ('%d', '%s', '%s', '%s')

Notice that instead of question marks, each parameter must be represented by a type specifier, the %d above meaning treat the variable as an integer, the %s meaning string. As per the example, you must also wrap each of the type specifiers with tick marks ''.

You pass parameters the same as without MySQL 4.1 Compatibility Mode, with each field id or TIMESTAMP keyword being separated by a comma, as in:

id,name,TIMESTAMP,NOW()

PLEASE NOTE: RackForms assumes magic_quotes_gpc is not enabled on your server.


So many features, So little time