RackForms v7.2
query
Site Tag Line
Latest Release: Build 923 - November 25, 2018
Contact Us  Purchase Options Purchase Options Contact Us
header-image


 
Top Link
Text Query

In Brief: Query a Database for a result set, to INSERT or UPDATE -- or any other type of SQL query. You can then take the result (if any) and use it in your page.

Dependencies: Database Table.

Hints & Tricks: This field effectively turns RackForms into an IDE, where you can mix and match SQL queries with your Form or Builder page data for complex, powerful interactions.

Return Values Notice *important!*: If your query has no errors but doesn't return any rows, the PHP variable that gets the result of the Query call (Result Set Variable Name) will be == (boolean) false. All valid results are stored in the form of: $fb_result[n], where n is the row number. Individual fields are then accessed by index or field (associative format) after the row number.

Thus, if your result only returns one row, you'll use $fb_result[0]['field_name'].

The second row would be: $fb_result[1]['field_name']

Table Names That Contain The Dash Character (-)

If a table name contains a dash MySQL requires the table name be wrapped in back ticks `table-name`, and MSSQL requires they be wrapped in braces as in: [table-name]

Options/Properties

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.

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 DSN

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 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 = 'localhost';
$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 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();

Important Note Regarding SQL Wildcards (%)
We can use wildcards in a query, but not in the SQL Code area. Any wildcards must be provided in the actual variable being passed. Thus, we could have this SQL statement:

SELECT * FROM fb_demo WHERE name LIKE ?


Using this $_GET variable:

G{name}

And to filter using the wildcard logic our query string would need to use:

page.php?name=%tt

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.

Understanding MongoDB and NoSQL
MongoDB is a NoSQL solution, and as such acts quite differently than standard SQL. For one, we do not define a database or its collections (think tables in standard SQL), in some editor, we do so on the fly.

Further, we query and write using essentially the same syntax, which is to say simply passing a set of values in an associative array.

This is a good primer on learning more about the terminology and thought process behind MongoDB and other NoSQL solutions.

 

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.

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.



So many features, So little time