Notes

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.

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

Datasource

Dynamic Query (Select Items Only)

You must set this to Yes if you need to run a dynamic query on this item. Running a dynamic query means your list values will be populated by this query, so be sure to only use one column in your SELECT statement*.

We can select and then use use two columns in the SQL select statement. One column queries are fully backwards compatible, and if used you do not need to do anything extra--simply create your query as normal.

If you would like to use two column queries you must be careful of the order of the field list. The first column given in your Source SQL will be used as the display name of the field, the second column will be set as the value. Only the value is transferred through the system and given as a variable to your confirmation page elements, in other words, the option text is for display purposes only.

DB Vendor

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

Source SQL

Source SQL is the query to run on the select item. Can be inline sql, a stored procedure call, or a 'prepared statement' SQL string.

A simple query might look like:
SELECT make from cars

An example of a MSSQL/MySQL stored procedure call with no parameters would like this:
call sample_proc

A MSSQL/MySQL stored procedure call with one 'int' parameter would look like so:
call sample_proc(10)

To use a non-integer as a parameter surround it with single ticks as in:
call sample_proc('hi')

Please note that if we use double quotes as in:

SELECT * FROM states WHERE state = "Wisconsin"

We must escape the quotes manually as in:

SELECT * FROM states WHERE state = \"Wisconsin\"

In general the safest route is to not use double quotes in SQL statements, as RackForms will never escape them.

To load several parameters use:
call sample_proc(1, 'matt')

More on calling MSSQL stored procedures here. More on MySQL stored procedures.

Prepared Statement SQL String means we use inline variable(s) in the SQL code to pass dynamic values. We do so as in the rest of RackForms, by using question marks (?) in place of actual values. We then define the value of these placeholder values via the Source SQL Parameters block, defined below.

It's important to note this functionality is only used for text fields when using Auto Suggest logic, and for Select items.

Parameter Order

This field determines the order in which our query populates the form field item from the columns returned by our SQL query. By default (and historical convention), we use the convention: label:value. This means the first column returned becomes the form field items label, the second its value. For most queries this is fine, though for some queries we need the opposite. Consider:

SELECT DISTINCT(set_id), set_title FROM t5_question_sets

In SQL, we cannot use DISTINCT anywhere but the first column, which means if we want to use set_title as the fields label, we need to set the label as the second value populated, and the value the first. This is exactly what this option allows us to do.

Source SQL Parameters

This field lets us define the parameters to pass to the database query when using prepared statements. This means our SQL block will contain code that has placeholders in the form of question mark characters, for each of those items we define the value here.

These values must be standard RackForms tokens, which are defined in the Datasource tooltip.

Default Select Value (Select, Radio, and Checkbox items only)
New to Build 705, this value will be passed to the select item creation/population logic, and if a value (match) is found with the incoming data, and provided we do not already have a user-selected value defined, this value will become the select items default value.

This value can take one of two forms:

1. Token: We can use the standard RackForms 2 token set to drive this field. This means: S{}, F{}, G{}, P{}, and ${}.

2. Simple String: Sometimes we do not want to use a dynamic value to set the default value, but rather just a known string. For example, we create a dynamic drop down list of states, and as the form will be mainly used by citizens in Washington State, we set that as the default value.

These values can be pipe delimited strings at which point RackForms will explode the string and match any items in the list it finds, or if it's a simple string value at which point we just match one item.

This is true for database and simple string values.

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.

Database Name (formerly known as: DB Schema Name)

The Database catalog you wish to query.

MongoDB Collection Name

The name of the MongoDB collection we wish to query. This is a required field for most Mongo queries. Please note that if this collection doesn't exist at runtime it will be created, as per MongoDB spec.

User - Dynamic

Database user name

Password - Dynamic

The password for this database connection.

Socket - Dynamic

Can usually be left blank, as the socket setting will be defined by the server. However, some server are not properly configured, and in those cases we may need to manually specify the socket path.

Port - Dynamic

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

DSN- Dynamic

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 an absolute path into this box, RackForms will use that file for all database connection information for this query.

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.

A note on connections

If you leave all fields blank and simply supply a SQL call in the SQL Source field, RackForms will by default include the movefiles/config.php file. By default, this file is populated with the RackForms Schema connection info.

If you provide a value to the DB Connector File field, the config file is not moved to your final job folder and connection info comes from that file.

Finally, if you use inline connection information, that will be used in the absence of any other fields. in short, the connection variable precedence is:
1) DB Connector File
2) config.php
3) inline variables

SQL Debug Mode

Enabling debugging will show error messages for your queries. Please note that for SELECT items you will need to view the page's source code (usually done by right clicking on a blank part of the page and selecting view source) to see any error messages. This is because the error messages are embedded in the SELECT items options field, which will not show in html.

Populate Using Web Service (Select Items Only)

In many cases we'll want to use a web service to populate the values of our drop down. This feature allows us to do so. It's basic operation is quite simple, enter a URL to the web service, a method (POST or GET), and provide any Query Parameters and custom headers. Your request runs and the returned data populates the select item in question.

URL

The location of the web service we will call. This should almost always be a secure URL (https).

Method

The request method used for the web service, either GET or POST.

Query Parameters

We can customize our response by passing parameters to the Web Service. Use this section to do so, supplying both a parameter name and value. Please note the Value field can be dynamic, accepting any standard RackForms token such as F{} for form fields and ${} for PHP variables. Of course if we do make use of user-supplied variables extreme caution should be exercised! We'll want to make sure no data can be returned that's unsafe for a given user, doubly so if the query returns sensitive data.

Query Headers

Use this section to supply values for logins and other custom logic. The most common will be for authorization, a few examples may be:

Key	Value
Authorization	Bearer <ACCESS_TOKEN>
Authorization	Basic username:password (value usually Base64 encoded)

Developers may notice when creating raw header tokens we separate the key and value with a :. RackForms adds this for us, so our values need only be the "raw" data.

Web Service Data Format Hints

When creating a web service for consumption using this method it's important to know the best way this data should be formatted for optimal results. In general, a select field can have a value only, or a separate label and value. When using value only, the value is both the select items label and value. When using a separate label and value, the label part becomes the option label which is visible to the user, and the value is that options value part.

RackForms supports XML and JSON result data, this section shows each in turn to highlight what the resulting select field would display.

JSON Data

Let's start with JSON data. Given this JSON data:

[
  [
      {
          "First Array": 1
      }
  ],
  {
      "Second Array, First Value": 2,
      "Bob Johnson": 2,
      "Bill Smith": "Fourth Value",
      "Sally Fields": false
  }
]
  

The resulting select item will have the following HTML:

<option value="1"> First Array </option>

<option value="2"> Second Array, First Value </option>

<option value="2"> Bob Johnson </option>

<option value="Fourth Value"> Bill Smith </option>

<option value="false"> Sally Fields </option>gt;

The first item of note is the JSON result has 5 items and thus our select menu does as well. This is despite the fact that the JSON data is broken into 2 separate arrays. Critically, RackForms does not care about data organization in the returned data, it simply pulls all valid records it can.

The next item of importance is the relationship between the JSON data's key / value structure and how that maps to our select field's label / value. For example, the First Array element has a value of 1, and this structure is indeed reflected in the generated <option> element.

XML Data

For XML data we run through the same "greedy" process, meaning we do not care about structure, but rather just pull all entities we can to generate select options. So for this XML we'll generate the options as:

<?xml version="1.0" encoding="UTF-8"?>
<employees status="ok">
   <record man_no="101">
      <name>Joe Paul</name>
      <position>CEO</position>
   </record>
   <record man_no="102">
      <name>Tasha Smith</name>
      <position>Finance Manager</position>
   </record>
</employees>
  

Resulting option HTML:

<option value="ok"> status </option>

<option value="101"> man_no </option>

<option value="Joe Paul"> name </option>

<option value="CEO"> position </option>

<option value="102"> man_no </option>

<option value="Tasha Smith"> name </option>

<option value="Finance Manager"> position </option>

Internally RackForms converts all XML to JSON before parsing the results for option creation, meaning the rules by which we parse the result are in part governed by that conversion process. The upshot is both XML fields (nodes) and properties are respected, and we use the following rules to map values:

XML Properties

For properties, the property identifier becomes the option field's label, and the property value becomes the option value.

status="ok" is converted to: value: ok | label: status.

XML Fields

For standard XML field nodes, the label is the node name, and the value is the node's value:

<name>Joe Paul</name> is converted to: value: Joe Paul | label: name

Confirmation / Query Condition

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.

Query

Query

SQL Debug/Data Return Mode

This select item lets you choose the type of debug and data return 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.

The third option, Data Dump, will echo all rows the query produces, or if it is a lastInsertId() query, the lastInsertId();

The fourth option is called Populate Form Fields, and allows us to populate any form field on the page where that form items Name/Value property matches the database column name.

For example, if we have a column in our database table called name, a form field with the exact same name and punctuation will be populated with the data from that column at page load.

This is most useful for those times we want to build a form that captures data from the user, and upon submission, allows that user to view their submitted data in the same format it was entered. It is also, of course, simply fast, as we let RackForms handle all of the logic we'd normally need tokens for!

A good way to learn more is to load the sample job: query-populate-form-fields

MongoDB Query Mode

Used only in MongoDB queries, this specifies the type of query we wish to run.

Technically speaking, a read operation performs a find() against a collection as in:

$collection->find($params)

A write operation performs an insert() to a collection as in:

$collection->insert($params);

Where $params is the array/data value defined in the SQL Code / Mongo Query field below.

SQL Code / Mongo Query

In this field we create the SQL query to run or if using Mongo, the associative array of values we wish to query against. Please note Mongo queries work a bit differently than standard SQL queries, both in form and how dynamic variables are managed. Please see below for a full explanation.

When using standard SQL: 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.

Calling Stored Procedures: We call procedures slightly different between the two main database providers.

MySQL / No Parameters
CALL ProcedureName

MySQL With Parameters
CALL new_procedure_param(1)

MSSQL / No Parameters
EXEC proc_ReportViewFiltered

MSSQL With Parameters
EXEC proc_ReportViewFiltered ?,?,?,?,?

Note how MSSQL never uses (), whereas MySQL does.

Using NULL Values.
By default RackForms passes empty string values as just that, an empty string to the database. Thus, when writing procedure/routine code it's important to not check for IS NULL, but rather = ''.

Important Note: Please note that if we have a JOIN that creates an ambiguous column name as this data will:

table_1: id, name, age

table_2: id, table1_id, sport, height

SQL: SELECT * FROM table_1 INNER JOIN table_2 ON table_1.id = table_2.table1_id;

RackForms will automatically index the now ambiguous id column as id1, and do the same for any subsequent duplicate field names with progressively higher indexes.

It should be noted however that this is not desirable, and writing JOINS with SELECT * should be avoided if possible! That is, when writing JOINS always define each column and use aliases if needed to avoid duplicates.

For MongoDB: For Mongo this field will be the document data we wish to query against or insert into a collection. Unlike standard SQL, for PHP and RackForms this takes the form of an associative array. For example, we could have a query in the form of:

array('name' => 'Kevin Smith')

If MongoDB Query Mode is set to Read this would query the collection for any documents (think traditional database rows) where the name property is Kevin Smith. If in Write mode this would create a collection if needed (as defined in the SQL Code / Mongo Collection value) and insert a document using Kevin Smith as the name property.

One important item of note is when you wish to use dynamic variables in a query, which is how a typical INSERT would work when collecting user data in a form.

In these cases, instead of using a string value we'll use either a raw PHP variable or token. For example, let's say we create a PHP variable as:

<?php
$s = "Matt";
?>
          

To use this in a Mongo INSERT, we'd write our SQL Code / Mongo Query as:

array('name' => ${s})

In this form, the ${s} is transformed at runtime into the actual PHP variable.

We call this out as for standard SQL queries we never use dynamic tokens in the query box, only ? place holders, and supply the tokens in the SQL / Mongo Variables box. We do this for security reasons that MongoDB simply doesn't have to contend with, hence the difference.

Note that for Mongo Updates we should always use both the SQL Code / Mongo Query and SQL / Mongo Variables boxes, with both supporting dynamic tokens or raw PHP variables.

 

SQL / Mongo Variables

For Standard SQL:

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.

For MongoDB:

This field is only used when we wish to perform an UPDATE operation as defined in the MongoDB Query Mode select box.

The data should be a standard PHP array, just as with almost all other MongoDB operations. The data being updated is the result of the search query performed in the SQL Code / Mongo Query box. In other words, we use both fields when performing an update.

A note on retrieving MongoDB result set variables.

When we query a standard database table, we issue the data as a single level array, where each index in the array is one row of the database result set. Within each of those array items is an associative array containing numeric and text based indexes to your database columns.

With Mongo this format changes a bit. A typical response for a single record is:

$q1 = Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [_id] => MongoId Object
                        (
                            [$id] => 5a96fadfdbc8bbe02900002e
                        )

                    [email] => sample11@yahoo.com
                )

        )

    [1] => MongoCursor Object
        (
        )

)

The key difference is each "row" returned is two levels deep. Thus, to fetch and display values in say, a Default Value field, we need to write the token as:

{$q1[0][0]['email']}

Note the double [0][0]. For standrad SQL results this would be a single [0].

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.

Function Call Definition

This very handy feature allows us to wrap the Query Module Database call into a standard PHP function, which means we can 'call' the query from our page code, Builder Function calls, and so on.

So as an example, in a PHP Head Code block we may need to receive a database query result if some condition is met, but only if that condition is met.

To handle this we would add the following code to this field, which is simply a PHP function signature:

get_names($job_id)

Which would say: wrap the Query code in a PHP function call of get_names, which accepts a parameter called $job_id.

Please note that we DO NOT use <?php tags, nor do we add braces { }. We simply put the function name and any parameters in parenthesis.

How To Handle The Result Of The Query

With our function signature defined we can now call the query as needed, and then, based on the Function Call Returns Result Variable setting, create a looping echo statement in the Code To Run After Query block, or return the entire result to the functions caller.

To learn more about this functionality it helps to see a real job, which we can find in the editor under: Load Example Job > The Query Module > using-function-calls.xml

Function Call Returns Result Variable

This option is directly tied to Function Call Definition, in that unless we have a function call definition set, this field is ignored.

When set, this means we use the PHP return keyword to return the entire database result set from the function. This is desirable in situations where we need to further process the data in another code block.

As an alternative, we can leave this setting unchecked and simply add code to the Code To Run After Query block to directly iterate and echo out values. This is very handle for Builder forms, in that we can call the database with a query and then directly output the values into our Builder template.

Create JSON Result Object

When set, creates a JSON object of the result set object. Key to this is the object we create uses the same name we define in the Result Set Variable Name setting.

Creating this object can be incredibly useful for times when we need to bridge the gap between the static nature of results returned by the server and the dynamic nature of JavaScript. For example, imagine a scenario where we need to create an invoice form on an order sheet. The form's calculations require a database lookup to pull sales tax rates that differ depending on origination. The invoice form needs to update in real time however, adjusting calculation values based on product selection. This updating logic uses, as one would expect, JavaScript. Thus, this feature allows us to pull tax rates into the form as a JavaScript object, which can then be used as variables in our dynamic cacluations.

Accessing JSON Variables

We can access the variables created by this process using the same general pattern as the PHP return object. That is:

An Object Array > Columns Accessed As Number Index Or By Name.

For example, when we query the fb_demo table we return this is PHP:

$q1 = Array
(
    [0] => Array
        (
            [name] => Kevin Bolder
            [0] => Kevin Bolder
        )

)
            

To fetch the name field from the first record (result row) of the JavaScript object, we'd write eiether one of these forms:

q1[0][0] -> "Kevin Bolder"
q1[0]['name'] -> "Kevin Bolder"
            

What's key here is for each row of the database contained in our result set we'll use the row numbers using zero-index notation. Thus, the first row is [0], the second would be, [1], and so on. To fetch the entire row, we'd omit the index / name field, and just use:

q1[0] -> Object { 0: "Kevin Bolder", name: "Kevin Bolder" }
            

Export Query Result To Comma Delimited

When used, the current Query result is formatted into a comma delimited list, and the user is either prompted to download the resulting .csv file, or save/append to a file as defined by Export Query CSV Result File.

In both cases, one powerful feature at our disposal is the ability to modify the resulting result set to create new data. For example, we could add additional columns of data, delete entire rows, and so on.

For example, lets say we wish to add the name and email columns from a simply query to fb_demo to create a new column of data called name_mail. We'd use the following SQL Query:

SELECT name, age, email FROM fb_demo

We'd then use code in the Code To Run After Query block to modify the original result set. To see a working example, please see the example job: save-custom-csv-from-query.

Download Prompt: It's important to note the Download Prompt option has the side-effect of terminating any renaming page output, which means we should only use this on form pages. If we use this on a confirmation page we risk not clearing out the form's session state, which means any values the user entered would not be cleared on the next page load.

Save File: Creates a file as defined by this fields value. Please see the next section for details.

Export Query CSV Result File

This option is used in conjunction with Export Query Result To Comma Delimited. The path used can be static or dynamic, though if dynamic, care should be taken that user input cannot maliciously inject code to overwrite other system files.

This field fills several roles, all defined by the value we enter:

If left totally blank, RackForms will create a temporary file and populate it with the CSV content of the query. This file will be location in the same directory of the job, will have a random string of characters as its name, and no file extension.

If just a path is provided, such as: ../, the file will be located in that directory with a random string of characters as its name, again, with no file extension.

If just a file name is given, for example, output.csv, that file will be created and used in the same directory as the job. If the file already exists, this queries results are append to the end of the file. if the file doesn't exist, the file is created.

If a filename and path are given, those values are used to create the file if it doesn't exist, or if it does exists, the values of the query are appended to it.

Return lastInsertId() / Affected Rows

Checking this box means we return one of two possible values. The number of rows affected by an UPDATE or DELETE query, or the Last Insert ID of an INSERT statement. The type is determined by the class of query we've performed.

For UPDATE / DELETE: The number returned here will be the number of rows the database engine modified during the previous query.

For INSERT: 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.

It's also important to note this value value may be 0 for affected rows if the query ran didn't change the rows data.

To retrieve this value is very simple: The value we define in the Result Set Variable Name will simply become a PHP variable we can use anywhere PHP tokens or raw values are accepted.

So for example, is the Result Set Variable Name is the default $fb_query, we could use the token ${fb_query}, or the raw PHP variable $fb_query.

This is an important distinction, because unlike database result sets which are indexed as arrays, this is always a one-off value.

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

ODBC Notice: Currently, only lastInsertID is supported.

MongoDB Notice: This feature works with MongoDB queries, though please note the native value returned will be an object in the form of:

MongoId Object ( [$id] => 5183c4b2dbc8bb880c000003 )

However, this object has a magic toString() method, which means we can simply use:

echo $fb_query;

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.

Create Dynamic Form Elements

When checked, will attempt to create form elements for each column returned by the query. This can be an incredibly powerful tool for some users, in that we can run a dynamic query and then have those elements display inline with the rest of our form.

• The fields themselves are treated like any other form element with a few important exceptions:
• These fields cannot be required.
• We can only safely process 1 row of data. That is, queries that return multiple rows of data will have fields created, but they may not process properly.
• RackForms will create most fields as text boxes, though setting a database column to BOOL will produce radio items with a Not Selected / Selected choice of options.
• Not all data types are supported or fully implemented. For example, any BLOB or TEXT field will not be displayed. Other fields, such as DATETIME and TIMESTAMP will show, but will not create calendar items. These limitations will be addressed in future builds.
• The name, id and label property of each item is the same as the database column name the field is being created for. This means when it comes to processing these results in say, an SQL+ item, we simple use the F{} token with the column name for that field.
• When using this feature, we must uncheck the Run In Page Top option, otherwise our created fields will be outside of the HTML:BODY element, and thus not remember properly.

By and large the ability to create dynamic form field elements form a query can be a huge time-saver if we know the limitations.

Dynamic Element Creation Mode

This feature allows us to set the type of dynamic form elements we create:

Basic - Pre-Populate Fields and Basic - Do Not Pre-Populate Fields - In these modes the Query module queries the tables meta properties, and creates form fields based on the column name, with the value of the form field being the current database fields value. The values are mapped as follows:

	Property
Database Column Name	Form Field Label
Database Column Value	The Form Fields name Property
Database Column Value	The Form Field Value

Complex List Mode - In this mode we create form fields based on the idea of each row mapping to a selectable option (using radio items). The Label of the form field is populated using the rows value, the values map as follows:

	Property
Database Column Name	Form Field name Property Indexed starting from 0
Database Column Value - Primary Key	The Form Fields value Property
Database Column Value	The Form Field Label

The main difference between the modes is Basic - Pre-Populate Fields and Basic - Do Not Pre-Populate Fields is only meant to query a single row of data, and then maps the values of the columns directly to their existing values. In other words we query the database and create a direct link between existing database values and form fields. Any form we submit using this method would be designed to update the existing values from that query (for Basic - Pre-Populate Fields) or INSERT a new record (for Basic - Do Not Pre-Populate Fields)

In stark contrast, the Complex List Mode performs a good bit of magic behind the scenes to create an abstract relationship between database fields and values. The reason this happens is this mode is built to allow the querying of a database that returns values the user selects, not values the user directly updates.

The specific use-case this feature was designed for was a table that contains products we wish to present to a user, the user then selects which items they want via checkbox items.

This mode is meant to return multiple rows of data, which means to create unique names for each form field element we must addend an index to each one, starting with 0.

Part of this logic also means we'll use the database tables primary key (if possible), to map to the fields value property. The reason this happens is so that any selection made by the user can easily map to a foreign table.

Thus, a database table with the columns and values:

id (primary key field)	product_title
1	Test Product 1000
2	Test Product 1001
3	Test Product 1002

Will create form fields as such:

Form Field	Label	Name	Value
1	Test Product 1000	product_title0	1
2	Test Product 1001	product_title1	2
3	Test Product 1002	product_title2	3

The primary key field becomes the value of the form field, the name an indexed version of the column name, and the label the columns value.

It should be said that while we don't need to provide a primary key field, we almost always should. This makes the logic needed to tie these selections into SQL+ INSERT blocks much easier, and much more flexible.

Inserting Dynamic Elements

Creating dynamic field elements is only half the battle, the next step is to make sure we can insert these records into a database. To that end we have a sample job called query-simple-list-mode that shows one way of doing so.

The basic idea is we create a simple list on the form page, then submit to the submission/confirmation page. We then run a SQL+ item to grab a ret_val value, which we use in a loop of the dynamic elements, accessible via fb_dynamic_elements. This item is treated as a standard form field element, which means an FR token can be used to access its value.

We call a function insert_sales_item, created by providing a value to the Query Modules Function Call Definition property. That is, we turn a Query module into a function we can then call in our code! As the Query module handles all the database logic, all we're concerned with is the core INSERT statement.

// INSERT each item
foreach(FR{fb_dynamic_elements} as $f){
  if($_SESSION["{$f}"] != "")
  	insert_sales_item($ret_val, $_SESSION["{$f}"]);
}

The end result of this code is we create dynamic form elements, which then populate a database table on submission!

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.

This block accepts the standard RackForms token set, please see the in-application tooltip for specifics.