You are here: Home / Fomidable Forms (Lite) / How FrmEntry::getAll() Works

How FrmEntry::getAll() Works

The Definitive Guide

By 4 Comments

Contents

Introduction

This article evolved from this thread in the Formidable Community.

When researching the Formidable KnowledgeBase for an example of FrmEntry::getAll() to answer the community question, I found an example: https://formidableforms.com/knowledgebase/php-examples/#kb-entries. To save time, here's the example in the KB article:

$entries = FrmEntry::getAll(array('it.form_id' => 5), ' ORDER BY it.created_at DESC', 8);

Formidable's documentation primarily shows basic usage as in the above example. If you look at the entry in our Codex, the FrmEntry::getAll() method actually accepts up to 5 parameters:

getAll( $where, $order_by = '', $limit = '', $meta = false, $inc_form = true )

This is where a developer has to read code to get a complete understanding about how to use an object-oriented public or public static function without having to spawn a child class.

If you open formidable/classes/models/FrmEntry.php in your development environment with a text editor, the getAll() function declaration is found at line 473 in Formidable Forms v6.4.2 and earlier.

The Codex example above is a programmatic extraction of line 473 in the code. You're seeing it exactly as the developer wrote it when you view it in the Codex.

Learning Prerequisites

To fully understand the concepts discussed in this article, you must be at least familiar with Formidable's metadata schema. Please review Formidable's table structures here: https://formidableforms.com/knowledgebase/database-schema/.

You must also have some familiarization with SQL query formation. Documentation for SQL queries is dependent on your host's "flavor" of database platform, i.e. MySQL, MariaDB, Percona, etc. If you aren't sure of the database your WordPress installation uses, please contact your host.

FrmEntry::getAll()

As already established, FrmEntry::getAll() expects up to 5 parameters. The $where parmeter is mandatory. The rest have default values that render them optional:

  • $where
  • $order_by
  • $limit
  • $meta
  • $inc_form

All of these parameters have something to do with the way Formidable builds its SQL query to return the requested data to you in a form usable by your code.

The first parameter to be examined is $limit because it's used in the third line of code. This means it's the first parameter used even though it's the third parameter received in order. In PHP programming, there is no correlation between parameter order and their usage in code. The function declaration sets the default value for $limit to an empty string ($limit = '').

If you pass a value for $limit, the resulting SQL query's LIMIT is set and will return the corresponding amount of records. To illustrate, if you pass a limit of 10, only the first 10 records that match the $where will be returned. The default empty string means "return all matching records".

In the KB example, $limit is set to 8. Only eight records will be returned.

The global $wpdb object is opened in line 1 and FrmDb::esc_limit( $limit ) at line 536 in formidable/classes/models/FrmDb.php is called. FrmDb::esc_limit( $limit ) returns the LIMIT clause that gets appended to the SQL query.

Next, getAll() does some background cache functions. If the entries you are asking for aren't in Formidable's entries cache, execution continues and the SQL query gets built.

The first step in building the query starts with a SELECT statement that includes all 13 fields from the frm_items table. You can see all the frm_item table fields in the database schema documentation.

Next, the $inc_form parameter is checked. This parameter's default is $inc_form = true. When set to true, Formidable includes the name and form_key fields from the frm_forms table. It does this by creating a LEFT OUTER JOIN with frm_forms with the alias of fr connected to the form id fields in both tables.

If you don't need the form name or key, you can exclude them from the query by passing false as the 5th parameter in your code. Make certain it's passed in the 5th parameter position, otherwise results can be unexpected.

After $inc_form, there is a regular expression preg_match on $order_by.

The $order_by argument can be include any field(s) requested in the SELECT statement. Use the proper table alias and field name as shown in the SQL statement.

If you want to sort your results by a metadata field, write the ORDER BY clause as " ORDER BY meta_1234".

If the preg_match is true, meaning the $order_by argument includes the word "meta_", execution is routed to the private sort_by_field() function where you can manipulate the returned data through the frm_handle_field_column_sort filter before the final query is constructed.

Regex Explained

The regular expression is "/ meta_([0-9]+)/". This is the explaination:

  • meta_ matches the characters meta_ literally (case sensitive)
  • 1st Capturing Group ([0-9]+):
    • Match a single character in the range between 0 and 9
    • + matches the previous token between one and unlimited times, as many times as possible

If you decide to use a meta value to match the regex, make sure you include the leading space before the word "meta_" like this: " ORDER BY meta_1234", otherwise the preg_match() will fail. Replace 1234 with your field's id. Make certain to include a leading space before " ORDER BY", as well.

frm_handle_field_column_sort filter

The frm_handle_field_column_sort filter accepts three arguments: $sort, $field_id, $field_options.

Filters always return an argument to the calling method. In this case, $sort is returned. $field_id and $field_options are passed to the filter's callback function as seen in this Kint rendering"

→$sort string(99) ", (SELECT meta_value FROM wp_frm_item_metas WHERE field_id = 1271 AND item_id = …"
, (SELECT meta_value FROM wp_frm_item_metas WHERE field_id = 1271 AND item_id = it.id) as meta_1271
→ Called from <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:575 [FrmEntry::sort_by_field()]
  1. <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:491 [FrmEntry::sort_by_field()]
  2. <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:813 [FrmEntry::getAll()]
  3. <ROOT>/public_html/wp-settings.php:600
  4. <ROOT>/public_html/wp-config.php:122
  5. <ROOT>/public_html/wp-load.php:50
  6. <ROOT>/public_html/wp-admin/admin.php:34
$field_id integer1271
→ Called from <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:576 [FrmEntry::sort_by_field()]
  1. <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:491 [FrmEntry::sort_by_field()]
  2. <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:813 [FrmEntry::getAll()]
  3. <ROOT>/public_html/wp-settings.php:600
  4. <ROOT>/public_html/wp-config.php:122
  5. <ROOT>/public_html/wp-load.php:50
  6. <ROOT>/public_html/wp-admin/admin.php:34
→$field_options array(19)
'size' => string(0) ""
'max' => string(0) ""
'label' => string(0) ""
'blank' => string(0) ""
'required_indicator' => string(1) "*"
'invalid' => string(15) "Text is invalid"
'separate_value' => integer0
'clear_on_focus' => integer0
'classes' => string(0) ""
→'custom_html' => string(514) "<div id="frm_field_[id]_container" class="frm_form_field form-field [required_cl…"
<div id="frm_field_[id]_container" class="frm_form_field form-field [required_class][error_class]">
    <label for="field_[key]" id="field_[key]_label" class="frm_primary_label">[field_name]
        <span class="frm_required" aria-hidden="true">[required_label]</span>
    </label>
    [input]
    [if description]<div class="frm_description" id="frm_desc_field_[key]">[description]</div>[/if description]
    [if error]<div class="frm_error" role="alert" id="frm_error_field_[key]">[error]</div>[/if error]
</div>
'minnum' => integer1
'maxnum' => integer10
'step' => integer1
'format' => string(0) ""
'placeholder' => string(0) ""
→'admin_only' => array(1)
string(0) ""
'in_section' => string(1) "0"
'auto_address' => integer0
'geo_show_map' => integer0
→ Called from <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:577 [FrmEntry::sort_by_field()]
  1. <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:491 [FrmEntry::sort_by_field()]
  2. <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:813 [FrmEntry::getAll()]
  3. <ROOT>/public_html/wp-settings.php:600
  4. <ROOT>/public_html/wp-config.php:122
  5. <ROOT>/public_html/wp-load.php:50
  6. <ROOT>/public_html/wp-admin/admin.php:34

After you've completed whatever data manipulation you've planned through the filter, here is the query as Formidable designed it:

→$fields string(262) "it.id, it.item_key, it.name, it.ip, it.form_id, it.post_id, it.user_id, it.paren…"
it.id, it.item_key, it.name, it.ip, it.form_id, it.post_id, it.user_id, it.parent_item_id, it.updated_by, it.created_at, it.updated_at, it.is_draft, it.description, (SELECT meta_value FROM wp_frm_item_metas WHERE field_id = 1271 AND item_id = it.id) as meta_1271
→ Called from <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:495 [FrmEntry::getAll()]
  1. <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:813 [FrmEntry::getAll()]
  2. <ROOT>/public_html/wp-settings.php:600
  3. <ROOT>/public_html/wp-config.php:122
  4. <ROOT>/public_html/wp-load.php:50
  5. <ROOT>/public_html/wp-admin/admin.php:34
  6. <ROOT>/public_html/wp-admin/index.php:10
→$query string(339) "SELECT it.id, it.item_key, it.name, it.ip, it.form_id, it.post_id, it.user_id, i…"
SELECT it.id, it.item_key, it.name, it.ip, it.form_id, it.post_id, it.user_id, it.parent_item_id, it.updated_by, it.created_at, it.updated_at, it.is_draft, it.description, (SELECT meta_value FROM wp_frm_item_metas WHERE field_id = 1271 AND item_id = it.id) as meta_1271 FROM wp_frm_items it  WHERE  it.form_id=84 ORDER BY meta_1271 LIMIT 2
→ Called from <ROOT>/public_html/wp-content/plugins/formidable/classes/models/FrmEntry.php:499 [FrmEntry::getAll()]
  1. <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:813 [FrmEntry::getAll()]
  2. <ROOT>/public_html/wp-settings.php:600
  3. <ROOT>/public_html/wp-config.php:122
  4. <ROOT>/public_html/wp-load.php:50
  5. <ROOT>/public_html/wp-admin/admin.php:34
  6. <ROOT>/public_html/wp-admin/index.php:10

At this point, the query is built and runs through $wpdb->get_results(). The results are returned as an associative array of row objects keyed by the value of each row's first column's value (id). These results are written to Formidable's cache and processing continues.

Including FrmEntry::getAll() Metadata

If you're not asking for metadata, (default: $meta = false) , or the query returns an empty result set, the function ends execution and returns execution to the calling program. If you pass true to as the 4th parameter, FrmEntry::getAll() continues to build the SQL to add the metadata.

This is when $where parameter is checked for whether it has been passed as a string or an array(). If sent through as string, it is converted into the first element of an array(). This means it's best to send $where through as an array() in the first place, which is exactly what the Formidable KB article shows us with array('it.form_id' => 5).

At this point, being familiar with SQL table aliases is essential. Arrays are represented as key/value pairs. The key in the KB example is it.form_id and the value is 5. The it. prefix is how Formidable's SQL identifies the frm_items table. In a metadata schema, the frm_items table may be regarded as a header record. All field data is metadata captured frm_item_metas.

When you write your own code, follow the KB example by including the form's id. Just replace the '5' with your form's id. This returns all entries entered for that form.

FrmEntry::getAll() Results with LIMIT 2

Executing the following line of code:

$results = FrmEntry::getAll(array("it.form_id" => 84), "", "2");

Produces this output in Kint when we examine the $results associative array(). Click the + sign to reveal the content:

→$results array(2)
→'6735' => stdClass(15)
public id -> string(4) "6735"
public item_key -> string(5) "ewnzx"
public name -> string(21) "Formidable Forms Lite"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public is_draft -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:12"
public updated_at -> string(19) "2022-12-23 12:58:12"
public form_name -> string(22) "Codex Add-on/Plugin LU"
public form_key -> string(13) "codex-add-ons"
→'6736' => stdClass(15)
public id -> string(4) "6736"
public item_key -> string(5) "rlr6p"
public name -> string(20) "Formidable Forms Pro"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public is_draft -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:25"
public updated_at -> string(19) "2022-12-23 13:14:38"
public form_name -> string(22) "Codex Add-on/Plugin LU"
public form_key -> string(13) "codex-add-ons"
→ Called from <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:814
  1. <ROOT>/public_html/wp-settings.php:600
  2. <ROOT>/public_html/wp-config.php:122
  3. <ROOT>/public_html/wp-load.php:50
  4. <ROOT>/public_html/wp-admin/admin.php:34

FrmEntry::getAll() Results with LIMIT 2 and $meta = true

When you include true as the 4th parameter, you are requesting all entry metadata to be included in the results set. When the results are returned to your calling function, you can loop through the associative array() to filter the results.

Executing this line of code:

$results = FrmEntry::getAll(array("it.form_id" => 84), "", "2", true);

Produces this result when examined with Kint:

→$results array(2)
→'6735' => stdClass(16)
public id -> string(4) "6735"
public item_key -> string(5) "ewnzx"
public name -> string(21) "Formidable Forms Lite"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public is_draft -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:12"
public updated_at -> string(19) "2022-12-23 12:58:12"
public form_name -> string(22) "Codex Add-on/Plugin LU"
public form_key -> string(13) "codex-add-ons"
→public metas -> array(1)
'1271' => string(21) "Formidable Forms Lite"
→'6736' => stdClass(16)
public id -> string(4) "6736"
public item_key -> string(5) "rlr6p"
public name -> string(20) "Formidable Forms Pro"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public is_draft -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:25"
public updated_at -> string(19) "2022-12-23 13:14:38"
public form_name -> string(22) "Codex Add-on/Plugin LU"
public form_key -> string(13) "codex-add-ons"
→public metas -> array(1)
'1271' => string(20) "Formidable Forms Pro"
→ Called from <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:814
  1. <ROOT>/public_html/wp-settings.php:600
  2. <ROOT>/public_html/wp-config.php:122
  3. <ROOT>/public_html/wp-load.php:50
  4. <ROOT>/public_html/wp-admin/admin.php:34

Take notice of the "metas" array.

FrmEntry::getAll() Results with LIMIT 2, $meta = true, and $inc_form = false

Executing this line of code:

$results = FrmEntry::getAll(array("it.form_id" => 84), "", "2", true, false);

Produces this result when examined with Kint. Notice the absence of the form name and key.

→$results array(2)
→'6735' => stdClass(14)
public id -> string(4) "6735"
public item_key -> string(5) "ewnzx"
public name -> string(21) "Formidable Forms Lite"
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:12"
public updated_at -> string(19) "2022-12-23 12:58:12"
public is_draft -> string(1) "0"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
→public metas -> array(1)
'1271' => string(21) "Formidable Forms Lite"
→'6736' => stdClass(14)
public id -> string(4) "6736"
public item_key -> string(5) "rlr6p"
public name -> string(20) "Formidable Forms Pro"
public ip -> string(14) "174.109.56.176"
public form_id -> string(2) "84"
public post_id -> string(1) "0"
public user_id -> string(1) "1"
public parent_item_id -> string(1) "0"
public updated_by -> string(1) "0"
public created_at -> string(19) "2022-12-23 12:58:25"
public updated_at -> string(19) "2022-12-23 13:14:38"
public is_draft -> string(1) "0"
→public description -> array(2)
→'browser' => string(84) "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/1…"
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:108.0) Gecko/20100101 Firefox/108.0
→'referrer' => string(100) "https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&fr…"
https://formidable-masterminds.com/wp-admin/admin.php?page=formidable-entries&frm_action=new&form=42
→public metas -> array(1)
'1271' => string(20) "Formidable Forms Pro"
→ Called from <ROOT>/public_html/wp-content/themes/genesis-sample/functions.php:814
  1. <ROOT>/public_html/wp-settings.php:600
  2. <ROOT>/public_html/wp-config.php:122
  3. <ROOT>/public_html/wp-load.php:50
  4. <ROOT>/public_html/wp-admin/admin.php:34

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *

Comments

    • The $where clause is your filter, but you need to examine the SQL statement as Formidable generates it when $meta= true. That’s the only way to know the alias Formidable gives to frm_item_metas in the LEFT JOIN.

      You can also filter after the fact by looping through the associative array and filtering it by the meta value.

      Reply

  2. Looks like I’m lost here trying to filter a lookup field in Form A, from a data in Form B.

    Using FrmEntry::getAll(), I’m trying to fetch all entries from form B. Don’t know if I’m doing this right or there’s a better approach?

    add_filter(‘frm_setup_new_fields_vars’, ‘filter_lookup_options_for_returned_cylinders’, 20, 2);
    add_filter(‘frm_setup_edit_fields_vars’, ‘filter_lookup_options_for_returned_cylinders’, 20, 2);

    function filter_lookup_options_for_returned_cylinders($values, $field){
    if($field->id == ‘LookupFieldID’){ // Replace ‘LookupFieldID’ with the actual ID of your lookup field in Form A
    $filtered_options = array();

    //Fetching all entries from Form B
    $entries = FrmEntry::get_all( array(), ”, ”, true, false);

    foreach($entries as $entry){
    $entry_values = maybe_unserialize($entry->metas);
    if(isset($entry_values[‘StatusFieldID’]) && $entry_values[‘StatusFieldID’] == ‘Returned’){ // Replace ‘StatusFieldID’ with the ID of your status field in Form B
    // Add only “Returned” cylinders to options
    if(isset($entry_values[‘CylinderNumberFieldID’])){ // Replace ‘CylinderNumberFieldID’ with the ID of cylinder # field in Form B
    $filtered_options[$entry->id] = $entry_values[‘CylinderNumberFieldID’];
    }
    }
    }

    // Override the lookup field options
    $values[‘options’] = $filtered_options;
    }

    return $values;
    }

    Reply

  3. Thank you a lot for this article!

    Is there a way to sort by multiple strings like ‘meta_1854 ASC, meta_254 DESC’

    Reply