RackForms v7.1
repeater
Site Tag Line
Latest Release: Build 922 - October 17, 2018
Contact Us  Purchase Options Purchase Options Contact Us
header-image


 
Top Link
Sortable Page Repeater

In Brief: The opposite of the rest of RackForms, this element lets users pull data from a data source.

New to Builder? Please watch this video to get a great head start on learning this powerful tool!

Dependencies: A valid SQL Database and table(s) to pull results from.

Hints & Tricks:

  • This module can handle all forms of result queries and logic, including detail pages, images, and all manner of sorting. This page type also support a full suit of design and layout modules, including text blocks and images. Let your creativity go wild!
  • The RackForms Builder Repeater Module is based on a Token system, where your form elements are represented in the RackForms editor as tokens; these tokens are transformed at runtime to the PHP and HTML elements that make your form return data to the user. Please see here for a listing of the tokens and their values.
  • When designing forms you may run into a situation where you want to test various settings for Default Page Size and Sort Criteria. The problem is RackForms needs to set these values into PHP Session Elements, which means only the first run of a form will use your default values anew. All subsequent funs of the form in the same borwser session will efectivly ignore any changes you make to these settings.

    We can get around this in development by adding the following code to the PHP Top Code block:

    <?php
    session_start();
    // clear paging session
    unset($_SESSION["{$_SESSION['paging_job_name']}"]["{$_SESSION['paging_field_name']}"]['PageSize']);
    // clear sort criteria
    unset($_SESSION["{$_SESSION['paging_job_name']}"]["{$_SESSION['paging_field_name']}"]['SortCriteria'])
    ?>
  • Starting with Build 695, the Builder module now handles MSSQL connectivity, including paging, updates, and delete.
  • The Builder module does NOT support column names with spaces. Please make sure all columns use dashes or underscores.
  • We can access raw template varables, that is, what PHP delivers as we repeate each row of data, as: $row['database_field_name']. Thus, $row is the variable we use to pluck raw database columns out. This is very handy if we need to add custom PHP code to our template to, for example, switch the display of an HTML <td> element to show a download link for a standard file or a thumbnail for images.
  • When updating data in Multi-Row Updates mode, any empty values are passed as null to the database. Thus, you'll want to make sure any fields that may get a null value are prevented from doing so either by checking the RackForms option: Prevent Null Updates, or by setting that field's default value as NULL.
  • If we ever get the error message of: Warning: include(builder/pager.php): failed to open stream: No such file or directory in /home/user/public_html/tbt/output/forms/form_name/page1.php on line 1305 This means we have included a paging token in the form but have not checked the Use Paging/Sorting button and supplied a Sort Criteria value (both just under the Repeater SQL box). Supply these value and the form will display normally.

With RackForms you can build complete update forms. As creating these elements can be quite involved, it is recommended that you check out this video for learn more!

Feature/Database Driver Support

This table gives a quick outline of the supported operations for each database driver used by the Repeater.

  MySQL MSSQL PostgreSQL ODBC - Generic MongoDB ODBC - MS Access
SELECT Rows X X X X   X
UPDATE Rows X X X X   X
DELETE Rows X X X X   X
Pagination X X X      
Images / Binary Data X X X      

Important Notice About Builder Pagination and Older Versions of MySQL

If using an older version of MySQL, generally around the first release of 5 (e.g. 5.0.xx), you may run into a situation where a Builder form that uses pagination will fail with an error similar to:

Error 1064: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '?, ?' at line 1.


The reason this happens is some older MySQL extensions do not handle the dynamic LIMIT clause properly, which in turn means the pagination logic fails. The solution is to force the use of the MySQLi extension, the PDO extension (both set in the Data Source properties box), or to upgrade your MySQL installation to a newer version.

Important Note About The Repeater Code Editor

We can view and edit our template HTML directly, adding custom styles and even inline PHP.

RackForms uses custom extensions to show PHP tags for inline PHP code, as well as for our Repeater tokens. The token codes usually work just fine, but the PHP tokens, because they have to work within the confines of the TinyMCE text editor, may throw up an occasional roadblock.

Specifically, we may find that code added to our template will disappear when we close the HTML editor. This happens because of the formatting rules TinyMCE (the code editor), follows, which are ironically based on "proper" HTML standards.

For example, if we have an empty td element and add PHP code to it as such:

<td><?php $counter = 1 ?></td>

TinyMCE will remove the PHP code when the editor is closed and opened again. This is because we cannot have an empty element. Add a simple space to the td element though as in:

<td>&nbsp;<?php $counter = 1 ?></td>

And the code loads just fine. In fact, this is a common theme for most fixes. If your code isn't "sticking", try wrapping it in a p tag, as that usually helps TinyMCE register the element.

The rule of thumb then is to always keep a backup of your code, paste it into the RackForms editor and see if anything is removed. These occurrences should be quite rare, but it never hurts to be cautious!

Important Notes On Stored Procedure Support

RackForms supports stored procedure calls on Builder pages. While a powerful addition, there are several important limitations that should be noted when using such calls:

1. Stored Procedure calls do not support parameters at design time when using the Template Generator. This means all calls must contain static, hard-coded values during form creation, then swap out to live values for form publishing.

For example, consider the following MySQL procedure call we'd enter into the Repeater SQL block:

call new_procedure_param(${t})

This means, use the raw value of a PHP variable $t at runtime. As dynamic values are not supported during the template creation process, we need to change this to:

call new_procedure_param(112)

That is, ${t} is replaced with a static value of 112.

Alternatively, if our procedure doesn't require parameters, we can simply use an empty () block.

Again, this restriction only applies to the the Builder template creation process. Once our template has been created we can swap out static values for tokens as needed.

2. Paging and Sorting is not supported. This means our result set should be small enough to fit on a single page.

Calling Stored Procedure Examples:

MSSQL and MySQL have slightly different calling syntax.

MSSQL uses EXEC followed by the proc name, then a comma delimited list of parameters. As our parameters will almost always be dynamic, this means one question mark for each UPDATE column.

EXEC update_name ?, ?;

Here, update_name is a procedure defined as:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		RackForms
-- Create date: 10/17/16
-- Description:	Update fb_demo with new name value.
-- =============================================
CREATE PROCEDURE update_name 
	-- Add the parameters for the stored procedure here
	@p1 varchar(50),
	@p2 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE fb_demo SET name = @p1 WHERE id = @p2;
END
GO
            


We can also call a MSSQL stored procedure using hard coded values. One big difference between RackForms and SQL Server Manager, is text values must be wrapped in single, not double, quotes.

For example this works in SQL Server Manager Studio:

EXEC update_name "Matt", 1;

But would need to be updated to this for RackForms:

EXEC update_name 'Matt', 1;

MySQL uses the CALL keyword followed by the procedure name, and then wrapped in parenthesis, our variable values / parameter placeholders. For example:

CALL new_procedure_param(?)

In both cases, (MySQL and MSSQL), we provide values for any parameter placeholders using the Repeater Update Variable List.


Options/Properties

Basic Attributes

id

The id attribute of the Repeater module must be unique across all pages and forms in your job. That means duplicate names are not allowed, and if trying to name the repeater node the same as any other in the same job, RackForms will prompt you to create a different name.

Data Source

DB vendor

Choose the database vendor you will be connecting to for the dynamic query. Unlike most RackForms Data Source fields, the repeater DB Vendor field allows us to select a vendor independent of providing other database details. That is, in all other Data Source fields (such as SQL+) unless we fill out DB Host Name the values defined in the jobs configure file are used, which means we cannot select a different database vendor unless all database connection details are provided. The reason the repeater field differs is because some minor difference may exist between how PDO and MySQLi handles the repeater element at run-time. Thus, should we ever run into a problems with one database vendor driver, we can easily switch to the other so long as the server has that driver installed.

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



SQL Repeater

Repeater SQL

The SQL code to run on your database. This code will be the default query used to initially populate the Repeater template. Code should be inline only.

Code Case
When writing our Repeater SQL code we'll want to use the convention of writing all SQL keywords in upper case. This is most important for the FROM keyword, but should be followed for all other keywords as well.

If we try and use a lower case from as in:

SELECT * from fb_demo

The editor will produce an error message prompting us to rewrite the SQL using an upper case FROM as in:

SELECT * FROM fb_demo.

Dynamic Variables

You can use dynamic variables in your Repeater SQL code, for example, if you have a page (page0.php) which can create a query string of: ./page1.php?id=1

When a user clicks this link, they will be taken to page1.php, which could be another Builder Repeater page. This page must then query the Database to retrieve the proper information for that particular variable.

Thus, we would create our Repeater node as normal, but in the Repeater SQL text area, we would add our dynamic variable to the query. We would create an SQL query that plucks the value of the _GET variable out like so:

SELECT * FROM cars WHERE id = " . $id ."

That said, it would be a VERY bad idea to use an unsanitized GET or POST variable inline. Thus, for our example above we would use $id, which itself comes from a bit of defensive PHP Top Code:

<?php
$id = 0;
$valid = isset($_GET['id']) ? $_GET['id'] : false;
if($valid != false && is_numeric($_GET['id'])){
$id = $_GET['id'];
} else {
// can die() here, or other security measures.
}
?>


To see an example of this in action, please see the sample Builder job: defensive-security-measures.xml

Inline Encryption
MySQL Supports inline encryption using, among other functions, AES_ENCRYPT and AES_DECRYPT. We can use these function in our Repeater SQL calls so long as we're sure to alias the result to a standard field name. For example:

SELECT m_caseload.id,
AES_DECRYPT(m_caseload.name_first, 'bSg12YtshR#ws') AS name_first,
AES_DECRYPT(m_caseload.name_last, 'bSg12YtshR#ws') AS name_last,
m_caseload.active
FROM m_caseload


PHP Variables

To retrieve PHP variables declared in the PHP Top Code section we use ${variable_name}

_GET Variables

G{get_variable_name}

_POST Variables

P{post_variable_name}

$_SESSION Variables

S{session_variable_name}

F{form_field_name}

We can use form fields to drive Builder queries. One thing to watch however, is that the form field in question is still active, in that the form sequence has not been submitted to a confirmation page. If it has, it's important to remember that RackForms automatically removes all session elements from that form sequence, and thus the element will not be available anymore.

Security Concerns

As with any SQL interaction, it is important that we consider the security implications of opening data up to the public. To help secure your data RackForms runs a sanitize routine on all dynamic variables you set; this routine strips out any SQL code, as well as UNION and OR keywords. This routine should prevent most any SQL Injection attack, though it is important to make sure your data source is just as secure. Restrict access to tables, monitor access, and try to break into your own site to test your basic security setup.

As a general precaution, the Builder module is not meant to create login pages, or any other form type that gates sensitive information. It should only be used to access tables that you want the public to have full SELECT rights to.

You can always enable Error/Info Messages from the SQL Debug Mode select item to echo out the SQL statements being run against your data source.

All told, with the proper security measures in place this makes the Builder Repeater incredibly powerful, in that you can create entire logistical networks where one RackForms Builder page talks and reacts to others!

Other Important Notes

Token Chooser Notes & Limitations
The Repeater SQL box is used to define the SQL we use to drive the Repeater Query. The Token Chooser is used to act as a shortcut for adding tokens to our Repeater template based on this SQL query.

In the most basic case then we write something along the lines of:

SELECT * FROM fb_demo

...and when we click the Token Chooser link, RackForms displays the relevant tokens for that query in a floating window. We can then add these tokens to our template to create a working Builder Repeater.

Their are two important limitations however, when and if our queries contain dynamic variables and WHERE clauses:

Dynamic Variables

The token chooser ignores (does not process), dynamic variables in our SQL Query. This means if we have a query along the lines of:

SELECT ALL FROM table_1 LEFT INNER JOIN table_2 ON table_1.id = table.2.id AND table_2,name = S{name}

The s{name} token is ignored. Of course when we run the full form all tokens are processed provided they have runtime values.

WHERE Clauses

By extension, when using dynamic queries with token variables e.g.:
WHERE id = #{id}
Our calls to the Dynamic Variable Picker + Token Chooser will not show the results of this WHERE clause filter, nor, as per above, will the dynamic variable be processed.

This should be a non-issue for most queries, though if for some reason you have a query that returns an alias you'll need to supply hard-coded values until the form is ready for deployment.

The reason why this happens is the Token Chooser query ignores any SQL after the WHERE clause (technically, RackForms actually splits the query in two and only runs the first part of the query)

The result of this splitting means only the SQL before the WHERE is run used when using the token chooser, though the full query is run for the final form.

Complex SQL Considerations

By default RackForms attempts to rewrite your Repeater SQL queries to perform a count(*) query for various house-cleaning tasks. The limiting factor with this approach is that more complex queries such as the one below do not work with the default count(*) query rewrite, and hence, the query will fail as the rewritten SQL code is not valid. Thus, in the default mode your RackForms Builder only supports queries in the form of:

[ SELECT ] [ fields | * ] [ FROM ] . . . [ { JOIN } WHERE ]

All is not lost however, though before we look at the solution however, let's try to understand the problem.

Let's use the following query as an example:

SELECT DISTINCT
  U.user_id, U.db_email, D.counted
FROM
  users U
  LEFT OUTER JOIN
 (SELECT
   user_id, COUNT(user_id) AS counted
  FROM
   uploads
  GROUP BY
  user_id) D ON U.user_id = D.user_id
WHERE
  U.user_id = 1
ORDER BY
  U.user_id DESC;


Although this is a fairly complex query, the problems RackForms will have with it are pretty simple: The DISTINCT Keyword would not be compatible with the default RackForms Builder parsing engine, as noted above, RackForms expects the query to be in the form of:

SELECT * FROM table

The second potential problem is when you add paging to a form RackForms needs to append the relevant LIMIT and ORDER BY keywords to your query, which means the code above would not work, as we already have an ORDER BY clause. RackForms will dutifully add another ORDER BY thereby breaking the query.

So how do we run such a query? As already stated the first limitation has been addressed in Build 625 with the inclusion of a simple Checkbox item called: No Count(*) Rebuild. Checking this box means RackForms will not rebuild the query but rather leave it untouched.

The benefit of this simple change is drastic. Using some conditional code in the Builder page, RackForms will now count how many rows are returned by the query itself, eliminating the rewriting issue. Thus, queries that contain DISTINCT in the front part of the query will work.

For more information, please see the No Count(*) Rebuild section below.

Of course that's great, but it still leaves the issue of our paging and extra ORDER BY CLAUSE. The fix for this is simple:

Never include a ORDER BY or LIMIT clause in any query you intend to use with paging.

Of course this may still break some queries, though this is one of the limitations of using Builder with complex queries. At times them it may be beneficial to reconsider our approach to the problem, by, for example, rewriting to query to be less complex, or by using Views.

Using * JOINS With Tables That Have Duplicate Field Names

I'll admit this one is pretty obscure, and is only relevant to users who chose to use the MySQLi extension.

The basic issue is that when you perform a JOIN using * to select fields, MySQL will take any duplicate field names and append such duplicates with a 1. For example:

Table 1.
id, make, model, year, image

Table 2.
id, image_data, image_name

SELECT
  *
FROM
  cars
  INNER JOIN cars_images ON cars.id = cars_images.car_id
WHERE
  cars.id = '2'


Will return this field list:
id, make, model, year, image, id1, image_data, image_name

The id1 is because both tables have a field called id. Unfortunately, this will break the MySQLi query code, which means your form will not work.

The solution then is to not program like that! Any database expert will tell you that star (*) queries are evil for anything other than testing and quick prototyping.

Thus, our fix will be to not use a star, instead specifying each field as we go. Alternatively, we can also simply not use duplicate field names, for example, we can change the Table 2 id field to image_id.

MySQL DATE_FORMAT Issue

RackForms takes any parameters we pass in the Repeater SQL box and runs them through sprintf(). This is a necessary step, as it ensures our queries are safe from SQL Injection.

However, a potential downside is any percent signs we provide as parameters to MySQL functions, as is the case for DATE_FORMAT, will cause a sprintf() error when we run the form.

The solution is to simply escape any existing % signs in your code, done by adding a % in front of the existing one. For example:

SELECT
DATE_FORMAT(ts,'%m/%d/%Y') AS fdate
FROM
fb_demo
WHERE id = #{id}


Would need to be re-written as:

SELECT
DATE_FORMAT(ts,'%%m/%%d/%%Y') AS fdate
FROM
fb_demo
WHERE id = #{id}


Using Hyphenated Table Names
If your SQL statement includes a hyphenated tablename you'll need to be sure to wrap the table name with `` marks. If we do not the Repeater query will fail.

Token Chooser
This is an incredibly important part of the Repeater module. When you click this link, the SQL query you enter into the Repeater SQL text area is run against the RackForms data source, or if you have supplied a valid connection via the individual Data Source boxes or though a DB Connector File, though the specified external data source.

RackForms then creates a list of default and dynamic variables for your query. The default list come from the functions RackForms provides for interactions such as Paging and Total Results displays. The dynamic variables come directly from your SELECT field list. For example, if you input the following into the Repeater SQL text area:

SELECT name, birth_date FROM person;

The Dynamic variable list will display field variables in the following format:

Variable name -> Function -> Update -> Flat Image -> DB Image -> Link -> PK -> Hidden -> Sort -> Sort Direction -> Search -> SQL type

For example, the sample SELECT call above would produce (shortened list):

name -> Function -> Update -> Flat Image -> DB Image -> Link -> PK -> Hidden -> Sort -> Sort Direction -> Search -> SQL type-> VAR_STRING
birth_date -> Function -> Update -> Flat Image -> DB Image -> Link -> PK -> Hidden -> Sort -> Sort Direction -> Search -> SQL type-> VAR_STRING -> VAR_STRING

All items except for SQL Type are links. Each link is a token that performs a different task when the form is built and run. When you click a link, it sends the token text to the Repeaters text area box, which can be in simple or WYSIWYG mode.

Thus, the process of building a dynamically generated page is to create an SQL statement; open the Token Chooser window to check your statement (by clicking the Token Chooser link); then create a layout in the text area box which you plug the tokens in to by clicking their links.

Learning which links do what is really the key to using the Repeater. The best way to learn is to watch the Basic Repeater Video in the Help Videos section.

Generate Template

This feature allows us to generate our Repeater Template automatically, for both display and multi-update forms. This process also adds a block of custom CSS to the pages 'Page JavaScript/CSS Code' block, which ensures the resulting template looks great under any display device.

We have two main options for this generator, creating a display-only form, and multi-update.

If we click the first option a few things happen: The first is RackForms runs the SQL Code in the Repeater SQL block, checking it for errors. If the code executes without issue we return a block of meta-data on the queries expected columns, and then create a Repeater template that displays all of these columns. Thus, it's key to note we can run any valid SQL in this block and RackForms will create the proper template.

If we click the second option: RackForms runs the SQL Code in the Repeater SQL block, checking it for errors. If the code executes without issue we return a block of meta-data on the queries expected columns, and then create a Repeater template that displays all of these columns in multi-update format. That is, each column's data is displayed as in input text field the user can edit submit.

We also enable multi-update mode in the form when we select this option, which means the query being run must include a Primary Key field in it's selected columns. If no Primary Key is found the process will fail and no template will be created.

Finally, both methods append a block of CSS to your page's Page JavaScript/CSS Code block. This style block contains an assortment of styles that target the Repeaters many elements, and can be edited at any time to suite your needs.

Please see this video for a brief demonstration of this feature.

Alternate Row Color

Checking this box ensures each builder row alternates colors. Please note this option has no effect for templates created with the Generate Template option, as those rows are always alternated.

Use Paging/Sorting

Clicking this box will instruct RackForms to create paging and sorting code for your result sets. Checking this box is a must if you have paging or sorting tokens in your template.

Initial Page Size

This is how many results you want to display per page by default. If used in a searchable form where the Display All Rows By Default (Search Only) is checked, this will be how many rows are displayed when the search form is first visited.

Please note that for technical reasons only the first run of a form will respect this value. That is, if we set this to 1, run the form and close it, then set it to 10 and then run the form again, the form will still only show 1 result.

This is normal behavior and is the result of the caching system Builder must use to present a proper end-user experience after the development process.

To get around this limitation simply add a Results Per Page token item to the form, as that way we can change the value as needed.

Sort Order

This is the default sorting order of your results.

Sort Criteria

Initially blank, if you activate paging, you will need to set a value in this box or an error will be triggered when you try to run your page. This simply needs to be the field name of one of your result set items. For example, name, or birth_date from the example above.

Please note we can populate this field with a comma delimited list of columns to sort by. This is of course handy for forms where we need to sort by more than one column. Please note however, that if your form can be sorted (by adding the sort tokens), unless you specify the same multiple columns in your sort token, the initial sorting will be lost. Please see the sort token documentation below for more detailed information on making sure your tokens are set up properly.

It's also important that our column names do not have spaces. If they do, the Builder form may fail, or at minimum, the sorting logic will not work.

Relaxed Search

If you are creating a searchable form, checking this box means when the form is first visited all rows will be displayed to the user. When used in conjunction with the Enable Paging and Initial Page Size values you can create forms that act like automotive site search forms, in that the user need not fill in all fields to return search results.

Conversely, you may want to create a form that starts off blank; the only rows returned to the user should be ones that have been specifically searched for. If that is the case, leave this box unchecked. When a user first loads the form, the search form will be empty. A classic example of this type of form would be an employee lookup form, where the user is required to input valid search data to return any results.

A few points of note: First, the term Relaxed Search is used to imply that the standard for returning information in this mode is not as tight as if this mode was enabled. This has consequences for multi-field search forms, in that the Join condition (see below under The Repeater Token Set > Dynamic Field Variables > Search > join) is ignored if only 1 field has an entered value. For example, a car search form might have Make, Model, and Year. The joins could be:

WHERE make= :make AND model= :model AND year= :year

Relaxed Search means that even though our model field is joined with an AND, the user could search for Corvette and still return results. In many instances this is the preferred behavior, though not in all. If you want to make sure your logical joins are respected, you need to make sure this checkbox is unchecked.

Second, By default all rows will be returned unless you Enable Paging for the form. For small data sets returning all rows is fine, but larger ones it is not. If your form has more than 50 rows you should probably use paging. Please keep in mind if we enable paging we must also set a value for Sort Criteria. By default you should simply make this the primary key of the table being queried.

No Count(*) Rebuild

This checkbox item is important if you plan on using complex queries that contain qualifiers such as DISTINCT in the SELECT cause.

To help you understand why this option is important, it helps to know a little background. When you run a Repeater SQL query, RackForms, by default, will attempt to rewrite the query with a count(*) clause to retrieve the total row count*. This row count is used for several operations including paging and the #{*T_total_results} token. While this automatic rewrite allows you to create complex paging elements with almost no effort, the downside to this operation is the SQL query has to fit a pretty rigid mold. As already stated, no extra keywords are allowed in the SELECT statement, and so on.

*In fact, you can see exactly how RackForms is rewriting your queries by enabling the SQL Debug Mode (see below). Viewing this output shows you exactly how the *default* rewrite affects your SQL code.

The solution to this issue is the No Count(*) Rebuild checkbox. By checking this box you tell RackForms NOT to rewrite your query but instead run it untouched and retrieve the row count by issuing a count($result->fetchAll()) operation.

The potential downside to this method is if your result set is large you may run into PHP memory issues. However, this should not be an issue for the vast majority of users.

No Results Message

This is the text that appears when a search or SQL query does not return any results. If you are creating a search form, the text should be along the lines of "Please Refine Your Search To Return More Results". If you are creating a form that does not Display All Rows By Default, the message should be of an explanatory nature, letting your users know how to use the form to return results.

Alternate Rows

Check this box to apply alternating row colors to your fields. This color is controlled via the Alternate Color attribute.

In terms of template implementation, alternating row colors are only applied to tr elements that have a class="alt" definition. That is, when we export the form RackForms looks for and applies PHP code to any block that has this option checked, as well as a class definition of alt.

By default all standard RackForms templates already have this class definition, so no extra work is needed. However, if we create our own templates, or we modify an existing template and accidentally erase this class attribute, alternating row colors will not be applied.

Alternate Color

This is the background color of the rows if Alternate Rows is checked.

Alternate Color 2

This is the secondary row color. Is blank (unset) by default, but can be defined as needed.

Paging Link

This is the color of the text that is not reversed but is still a link. For example, the BACK and NEXT links, along with the page numbers when not being hovered over.

Paging Background

This is the color of the solid box each active page and hover uses. It is best to make this the same color as the Paging Link.

Paging Outline

This is the color of the active page's boxed outline.

Paging Non-Link

This is the color off text that is not active. For example, the BACK text when you're on page 1, and hence, cannot go BACK one page.

Paging Active

This is the color of the current links page number, and the hover color of the text that uses a solid Paging Background. For example, if you're on page 1, the 1 is this color. Similarly, if you hover over the NEXT link, the NEXT text will turn this color. Best if this color is a strong opposite of the Paging Background Color, such as #fff (white)

Handling and Displaying Images

There are several ways to display images in Repeater items. The biggest consideration is whether our images are flat files or stored in the database.

Flat File Images

If they are flat files (images stored on the harddrive just like other standard files), the same technique desribed in the Flat File module documention is a good solution:

Assuming a file upload item Name/Value of file_1, The most common technique for this would be to set your Flat File Directory to a folder one level up from the form with:

../upload_images

Then in the SQL+ Module, set the file column to the name of the file with:

INSERT INTO entries (file1_name) VALUES (?)

And this for variable value:

file_1_name

Now in your Builder Repeater, create an image item, and set the fully qualified link to the file1_name database field with this as the images path:

http://domain.com/RackForms/output/forms/standings/#{file1_name}

Thus, you would now have images being uploaded to your server file system, a record of the file name in the database, and in the Builder Repeater token, a link between those two items in the form of the image link which looks in the folder of images for the image name saved in the file1_name database column.

Database Images

Grabbing images from a database is similar to flat files in how you construct the elements in RackForms, but differs fundamentally from how it actually works behind the scenes.

** Please note that in RackForms we now have a token for grabbing Database images, the DB Image token. This section should still be read for background info, though for most uses the DB Image token is the fastest and most simple way to grab database images for display in a Builder Repeater. **

Although this technique can be preferable to flat files for security purposes, it is a bit more involved. Thus, if you are a beginner, it may be best to stick with the flat file method, or at least give that a shot first.

To grab database images, you will need a table that accepts images and a PHP function that returns a header() call with the images appropriate mime information.

We include a helper file for this task in the /lib directory of your job called get_image.php, though in order to use this table you need to store your images in the pre-installed fb_images table.

The basic workflow is to use two tables at minimum, one for the text info, one for the image data. We tie the two tables together with an index created using the Set lastInsertId() = $ret_val feature of the first SQL+ item (the text INSERT), this lastInsertID is then INSERT'ed as a standard field element to the entry_id field of the fb_images table. Hence, our id of the text field becomes the entry_id of the fb_images table, we now have a common connection between the two tables for that entry.

So long as we have proper image data in the fb_images table, to get the image data out we query the get_image.php helper file with this value, which is placed in the Image URL field of the WYSIWYG editor:

lib/get_image.php?id=#{id}

In other words, we pass an id parameter of the current rows id field to the get_image.php function. Becuase the current rows id should match the entry_id of the fb_images table, the get_image.php function will go ahead and grab the image and display it.

Important Note On get_image.php, get_single_image.php, and get_file.php*

For security and logistical reasons, get_image.php, get_single_image.php, and get_file.php in versions 694 and lower only used the database information stored in the jobs config.php file. This meant if you moved a job to a clients server, you also had to update the config.php file for that job that match the clients database.

As of Build 695 however, this is no longer true, as the process by which we set the connection information of the get_image, get_file, and get_single_image files has been improved.

Specifically, RackForms will now automatically update each of these files with the same connection data we set in the Data Source properties box of the main Repeater element.

In the old way we were forced to always use the connection details defined in the jobs config.php file. This meant if we defined a different database for images, we needed to manually update the helper files.

The new way means that any connection details we define in the Data Source for the Repeater element box are the default values used for all image and file grabbing operations.

To 'catch' is we now have a query string parameter we can activate to revert to the old method if needed:

By default, RackForms will check for a GET variable of:

$_GET['connector']

If this is set with any value other than:

default

RackForms will not use the inline connection data which matches the Repeater element, but will instead use the values set in config.php.

Allow User Updates Mode

This item allows us to set the update mode for the current repeater. The default mode is to not allow user updates, which is of course the normal for most forms.

However, if we wish to enable user update we have two modes:

Single Row Updates: This mode is based on the idea that we only want to update one row at a time. This is very useful for situations where we want maximum control over the update process. This is because in this mode we are responsible for setting the values of Repeater Update SQL and Repeater Update Variable List. Because of this we can thus mix and match which fields are available to be updated, as well as write the exact SQL code used to perform that task.

Multi-Row Updates: This mode is more convenient in many ways that Single Row Updates, as to use this mode we just supply the table name we wish to update via the Multi-Update Table Name property, and the Primary Key of the updating table via the Multi-Update Table Primary Key Name property.

This method of course differs from Single Row Updates, as here we process all rows on the page at once.

The downside to this convenience is we have little to no control over the update process. That said, for most uses this is a non-issue, as our update logic is almost always quite simple: "update this text field with this new value".

This is a general overview of the update modes, for a more complete picture please see the yellow call out directly below.

UNDERSTANDING THE UPDATE/TEMPLATE PROCESS

The first step to creating any update form is to apply an 'Update' template to your Repeater WYSIWYG work area.

With that done you create a SQL query in the Repeater SQL text box which will allow you to create a Token Chooser, this token chooser then lets you place the various tokens which will be transformed by RackForms when you save the form into the various update items such as buttons, radio groups, and so on.

It is important to remember that you have lots of control over this process, most importantly concerning the templates.

UNDERSTANDING TEMPLATES AND COMMENT TOKENS

The Update templates are just standard HTML, but specific to RackForms, include four or more HTML Comment tokens which are replaced during form creation with HTML or PHP code. For the UPDATE operations, these comment tokens are:

<!-- UPDATE START -->

<!-- UPDATE END -->

Along with:

<!-- REPEAT START -->

<!-- REPEAT END -->

In other words, when you look at the HTML source of an update template you will find those four comment token blocks sitting within a <tr> block surrounding all form update elements (the tokens you adding using the token chooser). When you build your form, these comment tokens are automatically replaced by RackForms with the proper UPDATE and REPEAT code, which in the case of UPDATE comment tokens is a HTML <form> tag, and in the case of the REPEAT comments, SQL repeater code.

It is imperative that these tokens remain in your HTML code! If you remove them, RackForms will not be able to process your form correctly. If ever in doubt, you can always create a new Repeater module and add a update template to it to see the default set of tokens.

As they are within the <tr> block, the <form> html code will also sit within a the <tr> row, which means when the Builder Template parsing engine creates and runs the form, every row of data sits within its own form.

You will also notice that just outside of the <tr> block sits another set of comment tokens*:

<!-- REPEAT START -->

<!-- REPEAT END -->

These comment tokens are what tell RackForms to insert the SQL iteration logic which will create the "rows" of results on an exported form. Again, this means these comment tokens are replaced with PHP iteration code when you save the form, and hence never "show up" in a final form. They only exist to allow for easier to manage template code.

Knowing about these comment tokens is important because you are by no means limited to using the templates supplied with RackForms. You can create your own in any program you like, so long as you remember to include the proper comment tokens in the proper place.

That said, the various comment tokens are added for you in any template with the corresponding feature in its name. For example, a Template called 'Repeater - 3 Column + Update' will already include the proper repeater and update comment tokens.

Please be aware though, that unless your template has the correct comment tokens the operation will not work. This is a security feature! RackForms gives you total control over how and what features are available for any form.

INCLUDING AND USING A PK TOKEN/FIELD

To help ensure you're not going to update or delete from your database by accident, RackForms requires that each Single Row Update or Single Row Delete operation consists of at least 1 Primary Key (PK) field. A PK field is simply the database field which will act as the unique index (identifier) for any SQL code you write. While we typically see the PK referenced in the WHERE clause, it can be used anywhere we need in addition to the WHERE clause.

The key is that not only should we include the PK in our SQL WHERE clause, we need to physically add a PK token to our form for any single-field UPDATE or DELETE operation to work. As the PK token is actually a hidden field it is not necessary to worry about styling it--just be sure to include it!

Please also note that we can only include 1 PK per Builder form. Including more than 1 PK token will lead to failed updated.

For multi-update forms we have a more relaxed PK requirement, in that an actual database field designated as a PK is not required. However, we must be very careful when not using such a PK field, as any multi-update form that runs into a NULL field via the 'Multi-Update Table Primary Key Name' setting will fail. Thus, the rule of thumb is to always use a NOT NULL Primary Key field for any and all RackForms update operations.

Allow File Uploads

By checking this box you will allow your users to upload files to your database.

We can collect meta information on the file upload and pass it to the SQL statement. We have three fields to choose from:

_mime - The MIME Type of the file.

_size - The size in Bytes of the file.

_name - The original name of the file.

To pass these values to your SQL statement add an underscore and the shortcut name to the file item name. For example, if we had a file upload item named image_data we would use this for the SQL Statement:

UPDATE fb_images SET image_data = ?, image_name = ? WHERE entry_id = ?

And this for the Parameters List:

image_data, image_data_name, id

Again, notice how we use image_data, then add an underscore plus the shortcut name, in this case, name, to the variable. This is almost identical to the process we use for the SQL+ Module.

One very important note on mixing and matching file/image uploads with non-file upload fields
RackForms was designed to only update a file field if a user has actually selected a file to load for that record. This means so long as the user doesn't select a new file to upload, the file row will never be touched upon submission. This is so because technically speaking, RackForms automatically terminates any file update SQL statement that would place null data into the existing file slot. In short, we are not allowed to upload 'nothing' into an existing slot.

This is fine for forms that only have file data, but most of our forms will mix and match file and non-file data.

To address these types of forms, we must break the Repeater Update SQL statement into two logical components: one SQL statement for each file update, and one SQL statement for everything else.

How do we do this? Simple: replace logical components with multiple SQL statements, then take advantage of RackForms' ability to handle multiple SQL statements in one block.

So for example, if we had a builder form that had image and text update in one block, we would rewrite this single SQL UPDATE statement:

UPDATE fb_images SET image_caption = ?, image_description = ?, image_data = ? WHERE image_id = ?;

as this:

UPDATE fb_images SET image_caption = ?, image_description = ? WHERE image_id = ?;

UPDATE fb_images SET image_data = ? WHERE image_id = ?

Likewise, we would also make sure to update the Repeater Update Variable List from this:

image_caption, image_description, image_data, image_id;

To instead be:

image_caption, image_description, image_id;

image_data, image_id

It should be noted that from an architectural standpoint it is highly recommended that your file data table only hold the base file data and the name, mime, and size properties. Any other meta information such as upload date, file meta data (such as description, caption, etc) and security settings should be placed into a separate table joined by a foreign key. Doing so will ensure that your database has the maximum portability and redundancy. A good example of this is the fb_demo and fb_images tables.

Prevent Null Updates

This option, used only with Multi-Row Updates, tells the update engine to skip this row if the value being passed is empty or null. Generally speaking for checkbox items this means no item has been checked (and is thus null), and for text items the value being passed is an empty string.

If either of these conditions is met that row's update is skipped. However, this does not mean any other items are skipped, even those on the same row as the skipped update. For example, if we have two columns being updated, name and age; if the name update fails but the user selected a valid age, the age update will still be processed.

Repeater Update SQL

This is the meat and potato's of the UPDATE feature, in that regardless of what token items you supply to a form template, the code you type in this box will decide what and how your database is updated.

The format is standard UPDATE syntax, as in:

UPDATE fb_demo SET name = ?, email = ? WHERE id = ?

RackForms will take any fields supplied in the template, match them with the repeater update variables, and post the change to your database.

Of course most importantly we need to supply a proper set of variables including the value(s) passed to the WHERE clause!

Multiple SQL Statements In One Box
We can include multiple SQL statements in one block. The syntax for this is the same as what you would type for normal SQL, only you must separate each SQL statement with a semicolon. Please note you must also separate the variables with a semicolon that matches the corresponding SQL. For example, this SQL:

UPDATE cars SET year = ?, make = ? WHERE id = ?; UPDATE fb_images SET image_data = ? WHERE entry_id = ?

Would work with this variable list:

year, make, id; db_image, id

Notice how year, make, id; match the first SQL statement of:

UPDATE cars SET year = ?, make = ? WHERE id = ?;

And how the same is true of the second statement.

Where Multiple Statement are Required

Multiple statements are required for UPDATE operations where we want to update text and file data, and it's a possibility that the user could leave the file element untouched. This is because for security, RackForms short circuits any file upload statement where the file data is empty.

Repeater Update Variable List

Just as important as the Update SQL are the variables you pass to it. In this box we define which template tokens are used as the values for the fields defined in the SQL statement.

It must be noted that you can also include PHP variables in this area, the format is:

variable_name

That is, unlike most other RackForms fields, you do not need to add a dollar sign or any token characters such as the braces or pound sign.

A common way to do this then is to create a variable such as a DATETIME in the PHP Top Code section:

<?php
session_start();
// Your Code Below...
$datetime = date('Y-m-d H:i:s', time());
?>

Then include the variable in the Variable list as just: datetime

Multi-Update Table Name

When Allow User Updates Mode is set to Multi-Row Updates we must set this property to the name of the table we wish to update.

Please note that if using PDO or MySQLi RackForms will automatically set this value and Multi-Update Table Primary Key Name for us based on our Repeater SQL statement and a query of the database for the Primark Key. It only performs this for us however, if these fields are blank.

Multi-Update Table Primary Key Name

Along with Multi-Update Table Name, when using Multi-Row Updates we must tell RackForms the column name of the primary key the table we wish to update uses.

For example, to update the fb_demo table we would use fb_demo and id as the Multi-Update Table Name and Multi-Update Table Primary Key Name fields, respectively.

Key to this process is the column name we specify should ALWAYS be a real, AUTOINCREMENT, NOT NULLPrimary Key. RackForms does not check this at form creation time however, which means if we do not use such a field we risk running into a situation where the field is empty or null (has no value), at which point RackForms will simply issue a PHP die() statement killing the current repeater rendering process.

If this happens and we have debug mode enabled you will get a message in the form saying this has happened, if we do not have debug mode on the form will simply stop being rendered.

Update This TIMESTAMP Column

This option allows us to have RackForms perform an update on this rows TIMESTAMP field, if present, after an update operation.

The main use for this type of logic will be when we have a table that needs to have the most recent operation's time of action saved. We could create a database trigger ON UPDATE to complete this step, but having the option in RackForms is generally much easier.

Please note by default this field is blank, which means no update operation will take place. However, if we place a value this field must exists in the same table as the update operation, and must be of type TIMESTAMP.

Auto-Redirect After Update?

If this box is checked RackForms will automatically handle the logic needed to redirect your users to the same page they were just on, that is, the update repeater page. However, as this is usually handled anyway, it is redundant. However, if you want to create your own custom redirect, we would need to uncheck this box, otherwise RackForms will ignore your custom redirect call created in the PHP Code To Run After Update box.

Please note that if our form has User Delete Mode set to Multi-Row Delete this setting is ignored. This is simply because if we want to process any delete item logic we cannot skip out (via the redirect) of the page until the delete logic has completed.

In practice this effect is going to mean very little to us as form builders, and is only mentioned here for the sake of completeness.

PHP Code To Run After Update

This handy box allows us to run code after our UPDATE statement. Common examples would be logging code, access checks, redirects, and so on. Please note you do not need to supply PHP open and close tags.

Using this area let's us create all sorts of cool logic. For example, let's say we wanted to redirect to a new page after the update, but we also wanted to use the id of the last updated item to create a query string. To do so we would create a PHP header() call like so:

header("Location: http://www.rackforms.com/index.php?id={$_POST['id']}");
exit;

The most important part of this example is that so long as we include the proper name of the POST field our UPDATE form contains, we can use the live value in our header() call.

User Delete Mode

Get to know the two delete modes in RackForms with this introduction video!

Deleting records from a database is a very common task. However, it's also one that carries with it a great deal of responsibility from the users we entrust with this task.

Select the type of user deletes we want the form to support.

Single Row Delete is a mode where we write the SQL and define the parameters to support the DELETE action. This is the preferred mode for power users, as it gives us total control over the DELETE process.

The one big drawback to this mode however is we can only DELETE one row at a time.

Multi-Row Delete is made for quickly adding the ability to delete multiple rows in one shot. The downside is unlike Single Row Update mode we cannot define the SQL that's run against the database. Instead, we define two fields to tell RackForms the table to work with (via Multi-Delete Table Name), and the primary key (via Multi-Delete Table Primary Key Name). RackForms takes these two pieces of information and creates all the back end code to handle the DELETE operation.

While it's true we do not have direct control over the SQL being written in multi-delete mode, the system is robust enough to handle most DELETE operations. When you combine the fact then that this mode requires no SQL and handles multiple row deletes, this is the mode you'll usually want to choose -- it's easy and powerful.

User Interface Note: Just like User Updates Mode we can select from single or multi-delete mode at will. If we select Single Row Delete the interface will only show those elements that are used for that mode. If we choose Multi-Row Delete the interface will show the Multi-Delete Table Name and Multi-Delete Table Primary Key Name fields, which are described below.

Implementing Multi-Row Delete forms is quite simple:

1. Start by selecting a template with Multi-Delete in the name.

2. Change the Repeater SQL block to match your table and then use the token chooser to set the proper fields.

A Multi-Delete template is similar to most other templates, the two big differences are the use of the *F_multi_delete_checkbox|| and *M_multi_update_button|,Update/Delete Record(s),Multi-Delete Confirmation Message| tokens.

These two tokens work together by placing checkboxes on the form so the user can select which rows they want to delete, and a submit button so that when we're ready to remove our rows the user can do so.

Confirm Record Delete?

If checked this box will create a JavaScript based prompt before any record is removed. Be careful with this setting, as generally speaking we always want to have this enabled!

The only exception to this rule is when we allow multi-deletes. In these cases because we're already providing such massive power to the user it may simply be because we, the form designers, are the only ones who will ever use the form anyway (or some other high-trust user).

In these cases it may be easier to disable any prompt in the first place.

Please also be aware that when using a form that has multi-updates and multi-deletes, if we have this setting enabled even if we do not delete a record we will still get a prompt asking if we're sure we wish to continue.

In other words this setting, when used with a multi-update forms, creates a prompt even though multi-update forms do not support prompts. This can be handy for added security, though again, by their very nature multi-updates are hugely destructive if in the wrong hands, so care should be taken at all times regrdless!

Repeater Delete SQL

Just like the Repeater Update SQL box, this code determined what and how your database is updated. Normal DELETE syntax is used as in:

DELETE FROM fb_demo WHERE id = ?

Of course of paramount concern is the WHERE clause.

Multiple SQL Statements In One Box
We can include multiple SQL statements in one block. The syntax for this is the same as what you would type for normal SQL, only you must separate each SQL statement with a semicolon. Please note you must also separate the variables with a semicolon that matches the corresponding SQL. For example, this SQL:

DELETE FROM cars WHERE id = ?; DELETE FROM fb_images WHERE entry_id = ?

Would work with this variable list:

id; id

As a general rule though, although you can use multiple SQL statements in RackForms for DELETE queries, it may be worth looking into Foreign Keys for handling cascading deletes to implement a more robust solution.

Repeater Delete Variable List

This is the list of variables which will be used to determine which rows are removed. Again, chief among these variables is the value passed to the WHERE clause, which needs to be a proper PK field.

By proper we mean when we remove a record from a database we need to be very careful how this is done. The best and most common way to handle this is to use a Primary Key (PK) on the table in question, then use this unique PK value to tell the WHERE clause exactly which rows gets removed.

We can include PHP variables in this area, the format is:

$variable_name

A common way to do this then is to create a variable such as a DATETIME in the PHP Top Code section:

<?php
session_start();
// Your Code Below...
$datetime = date('Y-m-d H:i:s', time());
?>

Then include the variable in the Variable list as just: $datetime

As noted above, in Build 641 we can now use multiple SQL statement in one box. If we do so, we must be sure to separate each statements parameters with a semicolon.

Multi-Delete Table Name

This field only shows if User Delete Mode is set to Multi-Row Delete. The purpose of this field is to tell RackForms the table name it will apply the DELETE logic to for the form being built.

Multi-Delete Table Primary Key Name

This field only shows if User Delete Mode is set to Multi-Row Delete. Its purpose is to tell RackForms the Primary Key of the field we wish to delete in multi-row delete operations. While this field need not be defined as an actual PRIMARK KEY field in the database, it is strongly advised that it is. This is because an AUTOINCREMENT PRIMARY KEY will always be unique in your table, which is exactly what we want to have when performing delete operations.

PHP Code To Run After Delete

This handy box allows us to run code after our DELETE statement. Common examples would be logging code, access checks, redirects (to the main result listing page), and so on. Please note you do not need to supply PHP open and close tags.

SQL Debug Mode

Choose from No Debug or Error Messages. No Debug is what should always be used for production forms, Error Messages when developing.

If running multi-update or multi-delete queries, this option shows a good bit of information on the query that was run against the database server.

The Repeater Token Set

The RackForms Builder Repeater works by transforming tokens into code when you run your form. Tokens allow you to create complex form logic with the minimum amount of coding, all while providing a powerful interface for creating more complex logic should you need it.

Tokens come in many forms, from field items to search buttons. Token are divided into two main categories, Built-In Variables and Dynamic Field Variables. Built-In Variables are those tokens which are constant across all forms you build. Dynamic Field Variables are custom created for each SQL query you place into the Repeater SQL box.

In this list we shall list out the tokens available to you, and how to use each.

IMPORTANT NOTE: Prior to Build 640 each token must be separated by a space.

 

Built-In Variables

 

Token Name Token Description
*P_pager|BACK,NEXT|

Creates a pager element in your form. This pager will allow users to scroll back and forth through a result set, this result set being the result of a search or a simple one-off query.

As our two token parameters we can set the BACK and NEXT text used in the final display.

Please note in order for the pager token to work (and in fact, not create an error message when running the form), we must now check the Use Paging Sorting checkbox and supply a Sort Criteria value.

This is required as when we have paging in a form RackForms must include the paging help file, but also know what the primary key of the table is.

*R_results_per_page||

Creates a select menu which lets users select how many results to display per page. This requires that you have checked the Enable Paging box, but does not require the *P_pager.

We can specify an option set to make RackForms use a custom list of values. This list must be comma delimited, with any numeric value being used as is, and text elements being used as an alias for all.

For example, a token of:

#{*R_results_per_page|1,2,All|}

Will make RackForms produce a select set of:

1 > 2 > All

Where all is a hard-coded value of 50000. This is very handy for times when we want to export all records from a database, and the default 50 is not enough to show all rows.

As a further example, a token of:

#{*R_results_per_page|5,10,15,20|}

Will make RackForms simply display a select item with the values of: 5 > 10 > 15 > 20

Thus, the rule is any numeric value used as is, text based values are always treated as 50000.

If you want to use the standard set, simply leave the options block blank, as is the default:

#{*R_results_per_page||}

*T_total_results Displays the total number of results your query has produced as a simple text field. This token is always available to you whether you enabled paging or not. If you are using this is a search form, this number will reflect the total number of results of the search query.
 *O_sort_order Creates a drop-down manu which lets users select the sort order of the form. Requires the Enable Paging checkbox to be checked, and that you have provided a value for the Sort Criteria box.
 *X_row_index|1| Displays the current row index for that page's repeater item. Useful for showing numbered rows to the user.

It's one option is the start index. As PHP arrays are 0 index based we set this to 1 as a default, as using 0 would be confusing to most end-users. That is, we always start counting from 0 but add the start index to that number.

We could then change this to 0 to start counting from 0, 10 to start counting from 10, and so on.

*U_submit_button|,Search|

Creates a submit button. Only used for forms where you allow searching. For advanced users, this button simply calls a $_SERVER['PHP_SELF'] reference within a form tag.

This field takes two parameters.

The first parameter is by default blank, and is the path to the image we want to use in place of a standard HTML submit button element. If no value is set a standard button is used.

The second parameter has a default value of 'Search', and as you can see, is the text located after the comma. This field can be changed as needed.

It's important to note that the parameters must remain comma delimited in order for this field to work. That is, the first parameter can be blank, but if we want to use custom text for the submit button, we must still have the comma in between the pipes.

*N_update_button|,Update|

Creates an update button for use with templates that update data.

This field takes two parameters.

The first paramter is by default blank, and is the path to the image we want to use in place of a standard HTML submit button element.

If you place a path to an image file in between the two vertical pipes (|), this will tell RackForms to use that image file in place of a standard submit button.

We can specify the text to use for the actual search button. By default this is 'Update', but can be changed as needed.

*N_multi_update_button|,Update Records| The button is used for Multi-Update templates. It's options are the same as the *N_update button, in that the blank first parameter is used for an image path, the second parameter is for the standard button text.
*F_multi_delete_checkbox|| This item places a checkbox in the form for multi-delete forms.
*B_delete_button|redir,,Delete Record| Creates a delete button for use with forms that allow users to delete form data.

As of Build 699 this button has three parameters, which when using the default values in english means:

Do not redirect, do not use a custom image, and set the text of the delete button to 'Delete Record'

If you change these values however, the first parameter you specify will be the page you're redirected to after the delete process is complete. You can also specify a query string in this field which means you could provide the receiving page a note of what action was taken, for example you could use this:

#{*B_delete_button|index.php?delete=true,,Delete Record|}

To redirect to the index page with a query string of delete=true.

Why use this option? By default, after any delete operation you will automatically redirect back to the same page you're currently on. This works fine for pages where you have multiple rows that can be updated, but not as well if you have a specific detail page where just one row is shown. In those cases using the redirect variable is desirable.

Finally, you can always use the PHP Code To Run After Delete to issue any redirect code you need, as that code always runs before the redirect specified in this token. The benefit of this approach is it keeps your template code a bit cleaner.

The second parameter is used to specify a path to a custom image you want to use as the button graphic.

The third parameter is the text we use for the delete button, as well as the text we show to the user if Confirm Record Delete? is checked (which by default always is). Thus, generally speaking the default is what we want, as RackForms automatically adds a question mark to the JavaScript confirmation box. If we add a new value it needs to work in both forms--that is with and without a question mark.

*E_export_button|
,tab,export.xls,Export Data,|

IMPORTANT EXPORT TOKEN USAGE NOTES!
Please note that in order to use the export button your form must have Use Paging/Sorting checked, along with a valid value for the Sort Criteria field. Not adding these settings will cause the export process to fail.

Also note we cannot issue PHP headers from the builder page, or any included page to that builder page. For example, we may have an include script set in a different page that includes a header call, which is then set to 'Page Export Mode' -> PHP Head Code & Include On Every Page Of This Job.

This field creates a button on the form that when clicked, exports the current dataset to a format of your choosing.

Like all other Builder tokens, options are contained within the || block, with each individual option (parameter) being comma separated.

The first field allows us to set an image path to be used for the update button. Please note that by default it is left intentionally blank, as this means we just use a standard html button.

tab = This is the format of the export. By default it is tab delimited, but we can also use csv. Please note that if you use csv, you will want to name your file with the .csv extension, as that way Excel will open it correctly.

export.xls = The file name used when the save dialog box pops up. You can change this to anything you want, but it is generally most convenient to leave the extension as .xls, as that way the saved file is recognized by Excel in your users desktop environment. The only downside is upon opening the file, Excel will pop up a message saying the format is not correct. If this is a problem for your users, you may want to change the extension to .csv or .txt.

Export Data

This is the name of the button if using a standard HTML button element.

Custom Export List

The final option is left blank by default, but is very handy--basically, if left blank RackForms will simply export all fields contained in the SQL Repeater area code box. However, this is usually not what you want, as many times the form will have hidden fields and the like, such as id tokens for updates. Thus, this area lets us create a comma delimited list of the database fields we want the export to contain!

User Defined Functions

Just like the Builder function() token, you can append the name of a valid PHP function to your custom field list in the format of:

function_name()database_field

So for example, we could have:

#{*E_export_button|csv,export.csv,Export Data,upper()name,Active Ethernet,PON,SONET/SDH|}

The first field, name, has a function that will run against it called upper(). We place this function in the PHP Top Code block as such:

<?php
	function upper($v){
	return strtoupper($v);
}
?>

When run, this code simply takes text input and makes it all upper case.

 
Dynamic Field Variables (as they appear from left to right in the RackForms Variable List)
Field Name

This is the most common field, when clicked it places a token in your template in the format of #{field_name}#.

The behavior of this token is to simply place the value of this field as seen on the database in your form. That is, if in your database you have a column `name` with the value of Matt, Matt is what will show in the finished form.

Of course we're not limited to just showing raw values, the function token, for example, allows us to run the raw database value through a function we define in the PHP Top Code block. This is a common need when dealing with dates, for example.

However, you'll notice if any DATETIME or TIMESTAMP fields are present in your form they get appended with two clickable values:

- A | E

These values mean American and European, respectively, and when used will automatically transform a date value to a formatted version of that value. This is needed because in a DATETIME and TIMESTAMP field the values are not human readable in the traditional sense.

Sort

Used for creating a sort link in the header of a form. Assuming this field was for a database column named vehicle_year, the code created looks like: #{*S_Title|vehicle_year}

We ignore the #{*S_, as that used internally by RackForms to identify the type of token we are using. We are concerned however, with the Title|vehicle_year part. Title is what the text will be for the sort link. As stated above, this token is used to create sort links in the header of a form. That means we could replace Title with Vehicle Year if we were creating an automotive search form. When we build the form, you would see the word Vehicle Year as a link that when clicked on, would sort the entire form by that database field.

Multiple Column Sort

New to build 671, we can now specify a comma delimited list of columns to sort by. To do so, we simply need to add a comma after each sort field. So for example, when we create a sort tokens for a field called id we get:

#{*S_ID|id}

To make this token work for multiple columns, add a comma and the field name as in:

#{*S_ID|id,name}

Now when the form is run and we click the sort link, we will sort by id and name.

Note! - Please do keep in mind that per SQL specifications, when we sort by the first parameter, only if the table has multiple instances of this first parameter will you see a difference due to sorting on the second parameter. So for example, if we have multiple instances of the same id would our sorting on name come into effect.

Sort Direction This token is similar to Sort, only instead of creating a header element with text which sets which column to sort by, it creates a visual icon which allow us to set the sort direction of the forms data. We will generally place this token next to the column header, and should note that only the column which is currently being sorted by will have this icon displayed.
Flat Image *G{}

In some cases you will wish to display images in your forms result set. This token allows for this by creating a code snippet as such: <img src="<?php echo $row['image']; ?>" alt="" />

Thus, if your database had a column called 'image' where a directory of images contained the appropriate name stored in 'image', that image would be displayed in your form.

It should be noted that their are other ways to display images, you could create your own image tag in the TinyMCE editor and assign the img src attributes to the Field Name Property with #{image}.

DB Image *J{}

This token allows us to pull images from any table so long as that table is the same database your Datasource properties define.

The token supports several parameters, all of which are important. A typical usage case would be:

*J_photo|employment,id,true,200,400|

In this case, we say the column that has the photo is photo, the table name to pull the record from is called employment, the identifier for the row, that is, the WHERE param in the database call is id, we say true to creating a thumbnail, and because we are creating thumbnails, the max width is 200 and the max height is 400.

In unset form the token is:

#{*J_photo|table,id,true,200,400|}#

With the editor parameter explanations as:

|table name, id column name, use thumbnail [true|false], max x size, max y size (only used if thumbnail set to true)|

Of key importance in this process is setting correct values for table name and id column name.

The table name will usually be the same as the main table our repeater is being drive from, but it can be different if needed.

The id column name tells RackForms what the WHERE parameter will pull from the table. This means our table must have a Primary Key, or can at least guarantee unique values for this row. Generally speaking then this should simply be id, as is the case with the fb_demo table, and the sample above.

To aid in this process RackForms will automatically fill in these two values by examining the table being called in the Repeater SQL block. If the SQL Block is empty, missing a table, or the table doesn't include a primary key, this process will simply use default values.

Regardless, this row MUST, and this is important, MUST be included in the Repeaters Repeater SQL block. if it's not the image retrieval process will fail.

This token employs security measures in the form of unique tokens which are assigned to each image request. To not use this token for image calls in builder forms may open your form up to security issues. Thus, please pay close attention to any custom code you deploy.

Please see this link for a more detailed description on advanced image techniques.

DB File *Q{}

This token allows us to pull files from any table and database we choose.

The token supports several parameters, all of which are important. When first added to a template, the token takes the form of:

*Q_Name|insert_demo,id,,|

Name is the column we wish to pull data from. It's key to note this value changes based on the field we've chosen from the Token Chooser. That is, if the database column is Uploaded_File, the token would start off as:

*Q_Uploaded_File|insert_demo,id,,|

The token above thus says: "download the Uploaded_File column of data from the insert_demo table using id as the primary key.

It's very important to note the Name, and indeed, all default options, can be overridden by our properties block below. The reason we'd want to do this is for security, as we'll discuss below. Speaking of which then, the optional values these items define are:

|table name, id column name, file name column name, file data column name|

table name - This is the name of the table we will too pull the downloaded data from. The value given when we first add the token is simply the name of the table your Repeater pulls from. We can change this value, however, to any table name your repeaters SQL connection has access too.

id column name - This value defaults to id, and is the most common way we'd locate a specific record in a database. However, this is not always a safe value to use, simply because when we use numbers to identify records it's easy to input numbers to pull different records. If we're building a public system to show say, images, this isn't an issue. However, it very much is an issue when we have secure file downloads. We'll pick this topic up again shortly after we've finished describing the file name and file data columns.

Thus, this value can be set to any other identifier you wish to create in your database, such as a UUID.

file name column name - We use this column to define the column in the database that tell us what the name of the file the user downloads is. By default this is "File Download"

file data column name - This value defines which column in the database contains the actual file data the user will download.

File Download Security

The trick with allowing users to download files from your server is making sure they only have access to data they're supposed to. As noted above, the default mode of the file download token is to simply provide a column name indexed by the id column, which is usually a number. This is rarely safe however, so for secure downloads we'll need a better way.

The easiest way we can create safe downloads is to ditch the id column as the index, and instead use a UUID.

Thus, our database table would become:

id - Type int UUID - 50 character index file - The actual file data for download
1 c640968b-2a9e-4f77-b6f2-71967e096110 [blob data]

We'd open the Token Chooser and click the UUID column, to add that token to our template. The token will appear as:

*Q_UUID|insert_demo,id,,|

Our goal is to update the token's properties to say:

Download the file column using the UUID value as the index. This would accomplish that:

*Q_UUID|insert_demo,UUID,,file|

We're now overriding the default options to say: Fetch the file column using UUID as the index.

The increased security comes from the fact that in the first version of the token, RackForms creates a link in your published form of:

http://demo/output/forms/insert_demo/lib/get_single_file_token.php?id=1&token=20aee3a5f4643755a79ee5f6a73050ac

http://demo/output/forms/insert_demo/lib/get_single_file_token.php?id=c640968b-2a9e-4f77-b6f2-71967e096110&token=20aee3a5f4643755a79ee5f6a73050ac

The difference is instead of a simple number, we now use the UUID.

Link *L{}

This token creates links that can be used in Master/Detail jobs.

The standard token format is:

|Link Destination, [Link Text / @Dynamic Link Value@] or ALT Text If Using Images, Link Image Source, Text Link ALT text, [0|1]Opens Inline Or Full Screen, Custom JS Call, Query String Name|

Which means the first parameter should be the link address, which can be a simple value like:

detail.php

...or a fully qualified url.

The next parameter sets the text for the link, this can be static or dynamic. If it's static it just means any text we create here will be the link display of the element. If it's dynamic, and thus wrapped in ampersands (@name@), the dynamic value of that field will be used. For example, in the fb_demo table we have an id and name field. We could create a link like so:

*L_id|detail.php,@name@,|

Which means the link value is the id column, but the text is the name column.

The next parameter is the image source if we want to create an image based link. Please note that if we do create an image based link the second parameter becomes the ALT text field for the image.

It's important to understand RackForms takes the column name we've used to create this token and creates a link in the format of:

detail.php?id=2

This means the column name becomes the GET parameter, and the current value of the column at runtime becomes the GET value.

We can change the query string items name value by supplying the 7th parameter a value other than the default, which is the base column name. For example, we would use this token value to change id to use a query string value of student-id:

*L_id|edit-student.php,Details,,,1,,student-id|

This will create a link in the form of: edit-student.php?student-id=9

To make use of these links we would have a job that's detail page retireves the GET parameter in the repeaters SQL Code block as in:

SELECT * FROM cars WHERE id = G{id}

The next paramter allows us to set a ALT property on a text link.

The next parameter in position 5 is a 0 or 1, which means, respectively, do we open the link inline or in a new window. Key to this is this is setting to a 1 is only useful for forms within iFrames. This is because when set to a 1, a click on that link will open the url by taking over the entire window, not just the content of the iFrame. This can be useful in many cases, such as complex jobs where we have several nested iFrame items.

Custom JS Call

If supplied, this value will override any link properties and instead, turn this element into a simple onclick enabled text element. This Is very handy for creating logic that performs actions on specific rows, such as duplication or calling custom AJAX logic.

Example:

*L_id|detail.php,Duplicate,,,0,onclick="dup(id); return false; "|

In this example, we'll call a custom JavaScript function called dup(), passing the raw value of our repeated row (id), to the function at run time.

Key to note is by passing the id string into the dup function, RackForms will automatically transform this into a dynamic PHP echo block at run time. This way we'll get dynamic values passed to the function call we've created. Also note how, to prevent the page from navigating when the link is clicked, we've added a return false to the end of the onclick event.

Function *C{function_name} In many cases you're data should be acted upon before display to the end-user. For example, a price field in the database might be a simple FLOAT value, which means it would not display well as a dollar value. For example, 17995.00 would be the raw value in the database, but we want to display it as $17,995 to the end user.

The Function token allows us to accomplish this. When added the token will look similar to:

#{*C_function_name()price}

To use this field we would replace function_name with the name of a function we define in the Page PHP Head Code or Page PHP Top Code blocks of our page.

For example, we could place:

<?php
function format_price($num){
return '$' . number_format($num, 0, '.', ',');
}
?>

In our Page PHP Head Code text area, and use this function token:

#{*C_format_price()price}

To echo out our price as $17,995, just as we wish our viewers to see.

Cool Tricks and Hints

Passing Multiple Values To Our Functions

We can pass a comma delimited list of field names for the active query to our function. These variables can be other fields from the database repeater query in the format of just the field name, or raw PHP variables in the form of: $variable_name

For example, lets imagine a scenario where we wish to display an 'add user' link if the number of volunteers for an event has not been met.

Our function call in the repeater template would be:

#{*C_func_openings()job_openings,job_id}

And the function, now that we can accept more than one parameter, would be:

session_start();

function func_openings($ct,$job_id){
  if($ct !== 0 && is_numeric($ct)){
    echo "<a href=\"add_volunteer.php?job_id={$job_id}\">Sign Up</a>"; 
  }
}

This is a very handy feature, and allows us great flexibility when creating 'smart' forms that react to our users input and other such conditions.

Passing PHP variables would be done like:

PHP Top Code:

<?php
  $id = 10;
  
  function test($val, $in = ''){
   echo $val . $in;
  }
?>

Reepater Function Token:

*C_test()id,$id

In other words we pass PHP variables as we normally would in standard PHP, using a dollar sign and the variable name.

Search *H_{}

The search token is used for creating text boxes that allow users to input search terms which are then queried against your data source. The search token is the most complex token, though in most cases you will not need to change most parameters.

Please note that the search token must be used in conjunction with a search compatible template! Such templates are readily identified by the inclusion of + Search in the template name. However, you can always add your own search capability by including this code in your own templates:

<form id="RackForms_search" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<p>[Search Fields Go Here]</p>
<p>#{*U_submit_button}</p>
</form>

The basic search token look like this:

#{*H_Search|id,=,OR,INT,textbox,15,,|}

As with most tokens, the #{*H_Search part can be ignored, as that is only needed for RackForms to identify what type of token this is. You only need concern yourself with the area in between the Pipe (|) symbols.

The values of each comma delimited item are:

field, comparison, join, datatype, display type, item width, item height, default value, value array, column alias

Let's take each in turn:

field

This is the field to search against, in other words, the database column. This is automatically created for you when you add the token to the template. Thus, in most cass you will not need to change this value.

comparison

This is the SQL Comparison to run against the field. The default value is =, though you can use others such as < > <> etc.

IMPORTANT: Text comparison values MUST be UPPER CASE. For example, to use LIKE be sure to use:

*H_Search|name,LIKE,OR,STRING,textbox,15,5,,,|

Please note that as of Build 710 we should now use LESS, LESS_OR_EQUAL, GREATER and GREATER_OR_EQUAL instead of <, <=, >, >=, respectively. This is because the TInyMCE text editor we create our templates in may end up transforming these symbols into HTML entities, breaking the query logic in the process. Thus, to be on the safe side use the new items!

We can also use LIKE, though prior to Build 700 a limitation with the MySQLi driver prevented us from using this term, and thus it only worked with users employing the PDO extension. We chose our database extension when we install RackForms. The good news again, is that as of Build 700 this is no longer an issue.

If we do use LIKE, at this time we use a wild card search on both ends of the search term as in:

%search_term%

join

When creating a search with more than one term, you need to tell the database how this term relates to the others, and the query as a whole. The default is OR, which means that multiple search terms would be strung together as SELECT * FROM cars WHERE val = 'val' OR val2 = 'val2'

Of course their are other logical operators such as AND and NOT.

In RackForms, your main concern is to make sure your logical operators and comparisons create queries that make sense for your form.

For example, let's say we are searching for cars. We have three search fields, the first is Make, the second is Model, the last is Year. Someone searching for cars is often going to want to search by Make first, returning as many results for that make as possible. After they are happy with the make, they may want to narrow down their search by model and return only the makes + models of their choice. Recall that by default the join (logical operator) is OR. That means your search tokens would be:

Make: #{*H_Search|make,=,OR,STRING,textbox,15,,|} Model: #{*H_Search|model,=,OR,STRING,textbox,15,,|} Year: #{*H_Search|year,=,OR,INT,textbox,15,,|}

This presents a problem, because now when a user searches for say, Chevrolet and Corvette, your SQL will return just that, all the Chevrolets, and all the Corvettes. Our user only wants Corvettes, they are still getting all Chevrolets. In English, the query would be: Give me all Chevrolets or Corvettes.

To fix this we need to change our join on model to be AND:

Make: #{*H_Search|make,=,OR,STRING,textbox,15,,|} Model: #{*H_Search|model,=,AND,STRING,textbox,15,,|} Year: #{*H_Search|year,=,OR,INT,textbox,15,,|}

In English, this query will now read: Give me all cars that are Chevrolet AND also Corvettes.

If you've never written search queries before they can be a bit tricky. Take your time and experiment!

data type

This value is created for you, you generally do not need to change it.

display type

This value lets you define what type of search field you want to use for this field. By default this is textbox, though you can also specify:

  • radio
  • select
  • checkbox
  • textarea

Populating Radio, Select, and Checkbox Values

If you specify a field type of select, checkbox, or radio, we'll need to define an array of values to provide that field with the items it will display to the user.

We can create two types of arrays for this purpose: PHP Arrays and SQL Result Arrays.

PHP Arrays

PHP Arrays come in two forms: Simple and 2-Dimensional.

Simple Arrays
Simple arrays are ones that only have 1 value, and thus the HTML label and value are the same. And example of such an array would be:

<?php
$ages = array(1,2,3);
?>

For example, in a form where we want to populate a list of values using the above array, an example search token would be:

*H_Search|name,=,OR,STRING,select,15,5,,$ages,|

A field update token would be functionally identical, in that the $ages value is used in the same way:

*I_Update|active,checkbox,INT,15,5,,$ages,|

2-Dimensional Arrays
Unlike Simple (1-dimensional) arrays, a 2-dimensional array creates two values: one for the fields label, and one for the fields value. This has very real and practical uses in many forms. Consider the following array in PHP:

<?php
$ages = array(array('All',''), array('32',32), array('21',21));
?>

This array will use the first sub-array element as the label of the item, and the second sub-element as the value. One useful way to remember this ordering is to think of the word love, the l comes before the v. Label first, then Value.

Why is this important? Having a label and value differ means we can accomplish all manner of tasks that would be impossible or impractical if we only had one value. For example, in the example above, the array('All', '') sub-element means a select item would show the text 'All' to the user, but pass an empty string as the value. This is a very common method to create a search form that, when it passes an empty string shows all results to the user. Thus, the user sees the word All as an option, but behind the scenes we're actually passing an empty string to our search query.

Another example is when we want to create a checkbox item for an update form where the user understands the checkbox is simply a toggle for some value. For example, a form managing user status of active or inactive would not need to show that text next to each checkbox, as doing so would introduce visual clutter and be redundant. Thus, we'd create an array in the form of:

$active_array = array(array("" => 0), array("" => 1) );

Our token for an update form, would be:

*I_Update|active,checkbox,INT,15,5,,$active_array,|

The end result is a form that only shows a checkbox to the user, but behind the scenes, passes the proper 1 or 0 to the database for updating user status.

Database Arrays

The other common way we'll populate checkbox, select and radio items in our Builder forms is using a database query. In these cases our task is a bit easier. Instead of manually creating arrays by hand, as we've done above, we simply query a database and the returned results will be used to populate our items. The key here is the value we us in our tokens will be the query items Result Set Variable Name property.

A Very Important Note On Variable Names And Builder Forms

A very important point should always be remembered when using array names: the name of our array cannot be the same as a database field in the form. An easy way to make sure this doesn't happen is to always name your arrays as such:



names_array

That is, append the word array or query to the variable name.

item length

This is the size attribute of the text field. Specify any number in this area to set the size to that value.

Please note that for text areas this value has two parts: width and height By default these values are 15 and 5.

item height

The height of the display item, can be left blank in most cases.

default value

Default value is the text value set for the element. It is not acted on unless the user performs a search. For text fields this is of limited use, though it becomes more important when we use select, radio, or checkbox items, as this value will set the matching value as default.

value array

This field, as described above, defines a per-existing array of values to populate select, radio, and checkbox items. As the name suggests, we need a valid PHP array to drive this value. This array comes from us hand-coding one, or from a database query via the Query Module.

column Alias

This property is used to create search forms where we need to search a range of values using the same column more than once.

This is needed for such forms because RackForms will name the search fields the same as the column being searched. If we use this option however, we essentially override the search fields id and class attributes, which can make applying JavaScript logic to the element a touch more consistent.

Update This field type lets you insert the value of the field as an updatable entry field.

The syntax of this token field is a little more advanced than others:

A typical token will look like:

*I_Update|id,textbox,INT,15,5,,|

The descriptions of these fields follows:

|field,type,DATATYPE,x,y,default, item $array item, upload size in bytes for file items|

field = the database field, this is created for you.

type = The type of form field element to display to our users. Can be:

  • textbox - shows a simple text box.
  • password - shows a password field, where input is masked with dots.
  • textarea - Shows a text area object with multiple rows.
  • radio - Shows standard radio items.
  • checkbox - Shows a checkbox item.
  • select - These can also be called spin boxes
  • selectmultiple - Show multiple items in one select box.
  • file-db - Used if we want to allow file uploads.
  • calendar - New to build 695, this field will show as a standard text box, but when clicked in, displays a jQuery Calendar UI widget. Please note that in order for this field to work, we need to include the jQuery UI - Core Module JavaScript library via the page's Page JavaScript/CSS Code area. If we do not do this the calendar field will not work.

    Please note that as of Build 701, the jQuery UI - Core Module include is handled for us automatically when we add a native update field for a TIMESTAMP or DATETIME field.

    Regardless of our native date formats, we'll want to read thorough this special section for all the details on handling date-time and timestamp data.

DATATYPE = prefilled datatype of field, no need to edit.

x,y = For text fields x is the length of the field, for text areas x is the columns and y is the rows.

default = the default value to provide, blank by default

Checkboxes, Radio items, and select (combo) boxes

For checkbox, radio, or select items this is an array of values to prefil the field with on page load.

In many cases, you will want to make the name and value different, as would be the case with a security form where level 1 security would be labeled 'Guest', but in the the database, have a value of 1.

To handle this, we must first consider the source of the data.

If using a Query Module item to grab values from a database, we would write a query where we would grab the two values in the form of:

label:value

That is, the first field item will become the label used in the form item, the second would be the value. If using a raw array, we would construct our array such that the first index is the label, and the second is the value.

Thus, we would create the array like so:

$ages = array(array('mine',31), array('dads',60));

Please see this section for a more detailed look at populating the values array.

File Uploads

For file-db items (file uploads) this value is the size in bytes of the max upload parameter, the default is 1 megabyte.

A convenient way to calculate sizes in bytes is to say:

1024*1024 * the number of megabytes you want the field to be.

So for example, to handle 5 megabytes we would:

1024*1024*5 = 5242880 bytes.

Handling File Upload Data In Update Forms

One of the most important parts of updating existing file data using a Builder form is to recall that most file uploads consist of two parts: a) the file data b) the files meta-data

File Data

Handing file uploads is pretty strait forward. If RackForms finds a file upload field in the token chooser, when we click the Update token in the token chooser we'll automatically get a token in the format of:

#{*I_Update|rawdata,file-db,STR,15,5,,1048576|}

That is, a standard update token but set with file-db as the data type, and instead of an array value for the last parameter, the number 1048576, which means 1 megabyte maximum upload size.

When we create our UPDATE SQL, the token chooser will automatically add this field to the list of variables, and when we choose it, the file upload should work as expected.

File Meta-data

Most of the time when we update a file upload we also want to update its associated meta-data. In RackForms we always get three meta fields we can use for this purpose:

_mime

_filename

_size

Note the underscores: we includes those because when we want to access a file uploads meta fields we do so by taking the name of the database tables file field column (e.g., the BLOB field, the ones that holds the actual file data), and appending the _mime, _filename, or _size keyword to get that property.

So for example, if the form has a database column called:

rawdata

To get the file uploads mime type and UPDATE the columns mime field, we would use:

rawdata_mime

In the Repeater Update Variable List. This is very important because it touches on a naming convention issue you should be aware of.

Lets say we have a database table that saves the raw file data to the column rawdata. Now lets say we name the mime column for this item mime.

In RackForms then, doing this creates a slight disconnect, because now the Repeater Update SQL and Repeater Update Variable List areas will look slightly out of balance.

This is because to update the file data we place the value rawdata in the Repeater Update Variable List. This matches the field name in the Repeater Update SQL block.

However, the mime field, while shown as mime in the Repeater Update SQL block, shows as rawdata_mime in the Repeater Update Variable List.

I know this is subtle, but had we named the database field rawdata_mime right off the bat, our two blocks would 'match', which makes creating and updating this form easier down the road.

It's not of course required that we name fields this way, but it sure helps!

Handling Checkbox Data In Update Forms

Abstract

Unlike other fields, checkbox items in PHP are considered arrays, which means we have to treat these fields differently than others in our Builder forms. Our biggest concern is to make sure that when we send and retrieve checkbox data that we do so in the most convenient manner possible. In short, we do not want to fight PHP, we want to work with it. The easiest way to work with it then, is to make sure that any fields we will use with checkbox's are defined in our database as a pipe delimited list.

The reason why we would do this is as far as RackForms is concerned, any data being set as a checkbox item in a builder Update form will have this code run against it:

$row = explode('|', $row['fieldName']);

In short, we try to extract an array of values from the data. If this call succeeds, we will have a nice list of checkboxes that have matching values pre-checked.

More Detail

For example, let's say we have a user table where each user can have several email addresses, as in:

support@sample.com and/or sales@sample.com

In the database, we would insert the data as:

support@sample.com|sales@sample.com

Notice the two email value as are separated by a pipe (|)

Entering your data in this way means RackForms Builder Update fields will automatically populate the update field as two separate values, as well as automatically 'check' any active values.

Of course as a checkbox field we need to populate the checkbox options with an array, which we would do via a Query element or an array in the PHP Top Code block as in:

<?php
$emails = array('support@sample.com', 'sales@sample.com');
?>

Our token would thus be:

#{*I_Update|email,checkbox,STRING,15,5,,$emails|}

When run, our checkbox options would be the two email address from the array, and if either of those values is already set in the pipe delimited list on our database, the checkbox for that item would be checked automatically.

One final note, you will notice that by default, the SQL+ modules 'Array Based Variables Separator' value is set to the pipe (|) symbol for seamless integration with any Builder forms.

Handling UPDATES for native TIMESTAMP and DATETIME fields.

New in Build 701 are several improvements of how native datetime and timestamp fields are handled. Please read below for full details and information.

When dealing with MySQL or MSSQL DATETIME or TIMESTAMP fields we are in essence dealing with a very specific SQL data format that must be met or our updates will not work.

Specifically, a DATETIME or TIMESTAMP field sent to the database for an update must be in the format of:

2012-02-15 14:14:05

The trick is that this is not the most convenient format to view a date. Many visitors will instead prefer a more traditional 02/15/2012.

Because of this extra complexity one might be tempted to ignore the native date formats and instead use standard VARCHARS. We could, but then we loose all the benefits that come with the date formats, such as easy sorting and searching.

Thus, for most uses the native date formats are a fact of life. However, if we do not need to sort or search based on a date and instead simply need a date as a display element, by all means feel free to use simple text fields.

If however, we need to sort or search based on dates, or we simply have no choice in the matter and must use a TIMESTAMP or DATETIME field, read on!

Implementing a Logical Date Solution

One of the biggest hurdles in dealing with native data formats, as already mentioned, is the native data type is not very user friendly. This is true both from a viewing and updating (typing) perspective.

In RackForms then we attempt to address both sides.

Viewing DATETIME/TIMESTAMP Values

From the perspective of viewing date values we utilize helper functions in a file called: builder/utility.php. These short function calls convert our native data to user friendly formats. We say formats because we have two options: American and European.

By default we use American, and we can see this via the token RackForms creates for us:

#{*I_Update|ts,calendar,TIMESTAMP_A,25,5,,|}

The important part is the TIMESTAMP_A bit. TIMESTAMP is the field type, and is set by RackForms automatically. The_A part though, that's of interest to us if we're in Europe.

If we are in Europe, we want to change this to be _E, as in TIMESTMP_E. The same is true for the DATETIME token. If we need European formatting change the _A to be _E.

Updating (typing) DATETIME/TIMESTAMP Values

We should now be displaying TIMESTAMP and DATETIME values is a user-friendly way, so what about updating them?

By default, when we add a token of a DATETIME or TIMESTAMP database field RackForms will alert us that it's gone ahead and enabled the jQuery UI - Core Modules include under Page Properties > Page JavaScript/CSS Code.

We do this as now we'll get a nice datepicker for that field automatically. This means a user can easily select date values from a calendar instead of hand typing them.

Behind the scenes, RackForms will now turn the form field being used to display the date into a proxy, in that the real date value being sent to the database is now stored in a hidden field just below the form field. That way the user only experiences the 'pretty' dates, but the database gets valid values.

PK *K_{} Used in conjunction with the UPDATE and DELETE feature, this value must be supplied to any form wishing to use either of those operations. This value is best defined by the PK of your database table, if available.
Hidden *Z{} This field is simply a hidden field item which can help you create custom update or delete logic.
Data Type This is the internal data type of the column as defined in the database. It is more for reference than anything, and is not used as a token in RackForms.

So many features, So little time