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, FormBoss will prompt you to create a different name.

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 would 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 (old syntax):

SELECT * FROM cars WHERE id = " . $_GET['id'] ."

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

_GET Variables
#{get_variable_name}

_POST Variables
*{post_variable_name}

$_SESSION Variables (Starting in Build 623)
^{session_variable_name}

F{form_field_name} (Starting in Build 701)
New to Build 701, we can now 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 FormBoss 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 FormBoss 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 almost any 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.

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 FormBoss Builder page talks and reacts to others!

Other Important Notes

Dynamic Variable Limitations
When using dynamic queries with token variables e.g.:
WHERE id = #{id}
Your calls to the Dynamic Variable Picker + Token Chooser will not show the results of this WHERE clause filter. This should be a non-issue, 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.

Complex SQL Considerations
By default FormBoss 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 FormBoss Builder only supports queries in the form of:

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

All is not lost however. As of build 625 their is a new option to overcome these limitations. 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 FormBoss will have with it are pretty simple: The DISTINCT Keyword would not be compatible with the default FormBoss Builder parsing engine, as noted above, FormBoss expects the query to be in the form of:

SELECT * FROM table

The second potential problem is when you add paging to a form FormBoss 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. FormBoss 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 FormBoss 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, FormBoss 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
FormBoss 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 FormBoss 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.

FormBoss then creates a list of default and dynamic variables for your query. The default list come from the functions FormBoss provides by default 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 -> sort -> sort direction -> image call -> function place holder -> sql type

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

name -> Sort -> Sort Direction -> Flat Image -> Function -> VAR_STRING
birth_date -> Sort -> Sort Direction -> Flat Image -> Function -> VAR_STRING

The items in blue 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 Dynamic Variable Picker window to check your statement; 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.

Use Paging/Sorting
Clicking this box will instruct FormBoss 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.

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.

New to Build 671, we can now 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
New to build 625, 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, FormBoss, 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 FormBoss 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 FormBoss 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 FormBoss 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 FormBoss 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.

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)

READER NOTE: The next 11 fields only apply to builds higher than 640.

Allow User Updates
New to Build 640, checking this box will enable users to update records.

As creating Builder Update Repeater Elements can be quite involved, it is recommended that you check out this video for learn more!

However, for a brief look at the Builder Repeater Update process, please check out the yellow call out box directly below.

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

Starting with Build 642.r4 and Build 646, you can now collect meta information on your file upload and pass it to your SQL statement. You have three fields to choose from:

MIME - The MIME Type of the file. Shortcut: mime

Size - The size in Bytes of the file. Shortcut: size

Original File Name - Shortcut: name

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
FormBoss 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, FormBoss 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? Simple: replace logical components with multiple SQL statements, then take advantage of FormBoss' 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.

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 = ?

FormBoss 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
New to Build 641, you 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, FormBoss 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 FormBoss 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

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.

Auto-Redirect After Update?
If this box is checked FormBoss 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 FormBoss will ignore your custom redirect call created in the PHP Code To Run After Update box.

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.formboss.net/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.

Allow User To Delete Records
If checked FormBoss will allow a user to delete records.

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!

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
New to Build 641, you 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 FormBoss 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.

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

variable_name

That is, unlike most other FormBoss 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

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.

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.

The Repeater Token Set

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

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

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

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>"; 
  }
}