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

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.

Confirmation Condition PHP Code

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

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/Query Conditional 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.

SQL Debug/Data Return Mode

This select item lets you choose the type of debug mode you want. The first option is No Debug, which as the name implies, will not return any value if an error occurs, and is the mode any production form should use.

The second mode is Error Messages, which will return any error messages the query produces. Finally, the last option, Data Dump, will echo all rows the query produces, or if it is a lastInsertId() query, the lastInsertId();

SQL Code

In this field we create the SQL query to run. Like all other SQL code fields, RackForms uses Prepared Statements to handle parameters, which means for every external parameter we assign a question mark 'placeholder'. These placeholders are then defined in the Variables field directly below. It should be noted however, that if you have a simple, static variable such as a number or text block, you can input this value directly inline with the SQL code like any other query.

Variables
This text area holds a comma delimited list of any variables used in the SQL Code as defined by the question mark placeholders (?) above in the SQL Code text box. This list must sequentially match the SQL Code list--the first question mark placeholders value will be defined by the first SQL Variable item.

RackForms allows you to use many types of variables, from direct values placed inline with the SQL query to predefined values such as TIMESTAMP, to token based parameters of which each has a special syntax to retrieve at query runtime.

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

Return lastInsertId()  

Checking this box means any auto-increment value from an INSERT query to a table defined with one will be returned to the variable name you define. This is very handy for creating say, a user account for a login system, in that the first query would return the key of the new user record in an 'admin_login' table (the lastInsertId), to which you can then take that index and assign it as a value to a second SQL query for a related 'personal_information' table.

Please keep in mind if you have this box checked you will no longer be able to retrieve any result set values, as would be the case in normal SQL anyway.

Finally, this mode doesn't work when MySQL 4.1 Compatibility Mode is checked.

Run In Page Top

This checkbox gives you the ability to define where in the page flow this query will run. This is important because in some instances you will not have the required variables to run the query until some other action has taken place, such as when you need to initiate a separate action in the PHP Top Code Block, or inline in your page html code.

By default this is unchecked, and thus the query will appear inline with your page HTML form code, in the order it was placed in the form, or later arranged while in Sortable mode.

If checked, the query code is moved into the head of the page, before any HTML code, but after the PHP Top Code block. This means you could use the PHP Top Code block to query any session variables etc, then use these values to populate any parameters for the query.

To retrieve your SQL result you can use the PHP Head Code, PHP Header Template Code, or PHP Footer Template Code areas.

Finally, unlike Form and Builder page types, if you use a Query module in a confirmation page it always executes inline and in order with any other confirmation modules, it never runs inline with your HTML like it can with Form and Builder pages.

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 (php_mysql) 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()

IMPORTANT NOTES: Unlike the non 4.1 compatible mode which returns all rows of a result into an array, your results must be iterated through via a loop. However, the first row of a result is returned via:

$fb_query = mysql_fetch_array($result, MYSQL_BOTH);

Which means for one-row queries you can use:

$fb_query['field_name'];

Also, when this mode is checked you cannot use the Return lastInsertId() feature, as that is only supported in MySQL 5 queries.

Append Label

Along with this item, the next two 'Append' items are new to Build 673, are 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.

One such use is in Builder forms where we want to add an 'Any' option to a Select item for a search field. Due to the nature of how searching works, when we have Relaxed Search set for the Builder options, any time a search field is passed in using a blank value that field is ignored, which means we will not restrict results based on that field. Hence, we get 'all' results back if the user selects the 'All' option.

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.

Code To Run After Query

This box lets you specify the code to run after the main query. This is particularly handy for things like logging transactions, setting return values, or any other operation that is logically tied to the previous database query.

Please note as this query takes place in the same code block as the query, you do not need to add PHP start and close tags.

Also, this block does not except tokens at this time. Thus, any variables you want to use from elsewhere in your page you must create as local PHP variables first.