SIGN UP

How do I create view with SQL SELECT DISTINCT equivalence without PHP?

By: Michael Tidbury | Asked: 01/31/2023
Forums › Category: Code Help › How do I create view with SQL SELECT DISTINCT equivalence without PHP?
0 Vote Up Vote Down
Michael Tidbury asked 2 years ago

I have a simple view (PEOPLE VIEW) based on a Form called PEOPLE.

Within the PEOPLE form there is unnormalised data: the form contains people and each may or may not have a SUPERVISOR assigned. Thus the same SUPERVISOR can appear on many people's entries.

I want to extract a simple list of the supervisors. In effect an SQL SELECT DISTINCT for the SUPERVISOR from the PEOPLE entries.

The view presently says (I have simplified by removing the HTML and other tests): [if 948 greater_than=""] [948] [/if 948]    ---> where 948 is the field for the SUPERVISOR (I also sort the form by SUPERVISOR). This correctly lists SUPERVISORs but obviously names are duplicated since the same SUPERVISOR can appear on more than one entry. It is the equivalent of a simple SQL SELECT.

I can extract a CSV into Excel and remove the duplicates but it would be much better to be able to remove duplicates within Formidable.

I want to avoid PHP in this situation. Does anyone have any suggestions please?

Thank you, Michael.

1 Answers
0 Vote Up Vote Down
Victor Font answered 2 years ago
How are the supervisors being assigned? Are you using a lookup table or is it a direct entry field? If you're using a lookup table, the answer is different than direct entry. For direct entry, create your own shortcode and run a custom SQL to select distinct. If a lookup table, query it, the values should already be unique.
Victor Font replied 2 years ago

By the way, because Formidable uses a metadata table design, all entry data is "unnormalized" by definition.

Michael Tidbury replied 2 years ago

Ok, thanks Victor.

The shortcode/custom SQL is the option I would use.

However, I have not written SQL in a PHP shortcode and I do not know how the Formidable Forms forms/tables are structured.

Is there anywhere I could look for guidance on (a) writing SQL in PHP and (b) the FF table metadata table design structures?

I come from a long SQL background and do still struggle with unnormalised metadata.

All the best,

Michael.

Victor Font replied 2 years ago

Formidable's schema is here: https://formidableforms.com/knowledgebase/database-schema/

The SQL is "SELECT DISTINCT(meta_value) FROM `wp_frm_item_metas` WHERE `field_id` = '999' ORDER BY 1"

Change 999 to the field id for the field capturing the supervisor information.

Use the WordPress global $wpdb object to prepare and execute your SQL: https://developer.wordpress.org/reference/classes/wpdb/

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right