How to create a denormalized view of form entries

Disclaimer: I am not a DBA or a developer so there may be a much more efficient way to structure this query. SQL Ninjas, here is your chance to shine.

Credits: I got the ORDER BY using the WHERE clause from one of Steph's queries she uses to drive FP. I got the "MAX(IF(..." from a variety of search results for "MySQL rows to columns."

 

If you have the need to access a form's entries programatically or to display them via means other than built-in FP capabilities, it can be very useful to create a denormalized view of them.

Operational systems such as FP use a normalized database table design. You can see FP's here: https://formidableforms.com/knowledgebase/database-schema/

Non-developers think about data and typically interact with it in denormalized form. Think of an Excel spreadsheet loaded with rows of information about pet food or cars. That is a denormalized view.

It may come as a surprise to you that there is no single table in the database named "pet food" or "cars" or "homes for sale" containing one row per entry from your forms. That approach could be convenient for occasional user that wanted to directly access their form entries but inefficient for the operational system.

If you want or need to view or access your live FP form entries in a denormalized view for table display, business intelligence (BI) or another operational system, this solution will help.

The goal here is to produce the same spreadsheet-like grid you see in the FP "Entries" view, but with an automated,  live connection to the database so that when you pull the data it is always up to date and contains the most recent entries. In addition, this provides a way for other programs, plugins, shortcodes, data queries, etc. to leverage your FP entries.

What you will need:

• Form ID of the form whose entries you want

 

This example uses a "Stores" form that collects information on store locations.

---

 

First a couple of short utility queries. These are not required but may come in handy as you edit this sample.

Field ID List

This query lists the fields used on your form. You can adjust the sort order to display them in your desired order.

You will need to change the form_id=15 to match your desired form number and change the field IDs in the IN (   ... ) list and the ORDER BY list.

 

SELECT
wp_frm_fields.id,
wp_frm_fields.name

FROM

wp_frm_fields

WHERE

wp_frm_fields.form_id=15

AND

wp_frm_fields.id

IN

( 198, 202, 203, 214, 204, 213, 208, 201, 200 )

ORDER BY

wp_frm_fields.id = 198 DESC,
wp_frm_fields.id = 202 DESC,
wp_frm_fields.id = 203 DESC,
wp_frm_fields.id = 214 DESC,
wp_frm_fields.id = 204 DESC,
wp_frm_fields.id = 213 DESC,
wp_frm_fields.id = 208 DESC,
wp_frm_fields.id = 201 DESC,
wp_frm_fields.id = 200 DESC

;

 

---

 

 

Form Entry IDs

This query lists the IDs of your form entries and a "name" of the entry that is used internally in FP. I included the name just to make the IDs meaningful to us humans.

You will need to change the form_id=15 to your form number.

 

SELECT
wp_frm_items.id,
wp_frm_items.name

FROM
wp_frm_items

WHERE
wp_frm_items.form_id=15
;

 

---

 

Denormalized view of form entries

This query produces a "spreadsheet" denormalized view of your form entries.

You will need to change all of the field IDs to match your desired form.

If you have additional subqueries that you need to run, you'll need to copy and paste the "states" and "countries" subqueries here and edit the new copy.

(The subqueries are required because FP stores all enty data for all forms in one table: wp_frm_item_metas. That includes the entries for country and state, so you need to pull those values from the same table you are querying for your pet food, cars or stores entries.)

Note: I left in the commented-out code for "WHERE item_id = 78110" as it is handy for debugging and testing. It limits the return set to a single row. Just remove the /* and */ to enable that code. You will need to change the 78110 to match an entry ID in your form entry data. You can pick one from the  results of the "Form Entry IDs" query or use the FP Entries UI to select one.

A formatted version of this query is attached, which may be easier to read and edit.

If you are new to SQL, do not forget the ; at the end and be careful that the last entry in some blocks does not have a trailing comma.

 

SELECT
wp_frm_items.id AS store_id,
MAX(IF(wp_frm_item_metas.field_id = 198, wp_frm_item_metas.meta_value,"")) store_name,
MAX(IF(wp_frm_item_metas.field_id = 202, wp_frm_item_metas.meta_value,"")) store_address,
MAX(IF(wp_frm_item_metas.field_id = 203, wp_frm_item_metas.meta_value,"")) store_city,
MAX(IF(wp_frm_item_metas.field_id = 214,

(
SELECT
meta_value

FROM

(

SELECT
wp_frm_item_metas.meta_value,
wp_frm_item_metas.item_id

FROM
wp_frm_item_metas

WHERE
wp_frm_item_metas.field_id = 120

) AS states
WHERE

item_id = wp_frm_item_metas.meta_value

)
,"")) store_state,

MAX(IF(wp_frm_item_metas.field_id = 204, wp_frm_item_metas.meta_value,"")) store_postal_code,
MAX(IF(wp_frm_item_metas.field_id = 213,

 

(
SELECT
meta_value

FROM

(

SELECT
wp_frm_item_metas.meta_value,
wp_frm_item_metas.item_id

FROM
wp_frm_item_metas

WHERE
wp_frm_item_metas.field_id = 115

) AS countries
WHERE

item_id = wp_frm_item_metas.meta_value

)

 

,"")) store_country,
MAX(IF(wp_frm_item_metas.field_id = 208, wp_frm_item_metas.meta_value,"")) store_phone,
MAX(IF(wp_frm_item_metas.field_id = 201, wp_frm_item_metas.meta_value,"")) store_web_site,
MAX(IF(wp_frm_item_metas.field_id = 200, wp_frm_item_metas.meta_value,"")) store_email

FROM

wp_frm_items,
wp_frm_item_metas

WHERE

wp_frm_items.id = wp_frm_item_metas.item_id

AND

wp_frm_item_metas.field_id

IN

( 198, 202, 203, 214, 204, 213, 208, 201, 200 )

/*
AND

wp_frm_item_metas.item_id = 78110
*/

GROUP BY

wp_frm_items.id

ORDER BY

wp_frm_item_metas.field_id = 198 DESC,
wp_frm_item_metas.field_id = 202 DESC,
wp_frm_item_metas.field_id = 203 DESC,
wp_frm_item_metas.field_id = 214 DESC,
wp_frm_item_metas.field_id = 204 DESC,
wp_frm_item_metas.field_id = 213 DESC,
wp_frm_item_metas.field_id = 208 DESC,
wp_frm_item_metas.field_id = 201 DESC,
wp_frm_item_metas.field_id = 200 DESC

;

 

---