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.

When using the SQL+ module, you will most likely be talking to a database other than the one FormBoss 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 FormBoss 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 FormBoss Tiered Connection Model
The first tier is the default config.php file. 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 FormBoss 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 form when you first installed FormBoss on your server, that is, the connection information would be the same as your FormBoss application. This makes sense for the Simple SQL module, as the FormBoss entry viewer runs on the same database as your FormBoss 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 summarize: if you leave everything blank, FormBoss 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
The host of your SQL provider - could be 'localhost', an ip address, or a server/instance name.

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, FormBoss 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 = '';
$db_user = 'formboss';
$db_pass = 'test';
$db_catalog = 'formboss';

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 FormBoss output is placed in:

formboss/output/forms/

Which means if I placed my DB Connector file in:

formboss/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:

formboss/output/forms/external_db

Now when we run the form FormBoss 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 FormBoss 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.

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, FormBoss 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 field Name/Value token.

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

To process this field in our condition statement we need to append the proper index of the field used for checking the condition using PHP array index format. It may sound confusing, but it's quite simple when you see it:

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

In short, the non-array way would be to simply use:

#{email_condition} == 'Recipient A'

But because we need to check an array index (again, because PHP treats checkbox items as arrays), we simply add the [0] part before the closing brace (}).

How do we know which array index to use (the number part)? In our example the checkbox field has two values; array indexs start with 0, so the first field, Recipient A, is [0], where Recipient B would be [1].

This numbering simply climbs up by 1 for every checkbox item you add.

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 FormBoss 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

FormBoss will then process these variables at form submission time and send the result to your database. This is the same weather 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 FormBoss 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
FormBoss 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, FormBoss 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, FormBoss 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 FormBoss 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.

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 FormBoss 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 FormBoss 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 specifier's 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()

Set lastInsertId() = $ret_val
This field, new to Build 631, allows you to retrieve the lastInsertId via a PHP variable called:

$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.

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
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.

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.

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