How to write MySQL query for SELECT data on formidable form

By: Sina Pars | Asked: 12/22/2022
ForumsCategory: Code HelpHow to write MySQL query for SELECT data on formidable form
Sina Pars asked 1 year ago
Hi There, I need to use a query to select and display all the entries forms with their own column headers, because the data is stored as metadata. I have a problem with the query. Can you help me? BR
5 Answers
Best Answer
Victor Font answered 1 year ago
You're creating a flat table of some sort that you want to use instead of the standard metadata format. How you do this depends on when you want to insert the record into the normalized table. You can use the frm_after_create_entry hook to insert records into the table at the time of entry creating. In this case, you would only use $wpdb->insert(). The data will come from the $_POST associative array. If you do it after the fact, you could use the Formidable getone() function to extract all field data. You would need to format it and use $wpdb->insert(). Last if you write a SQL SELECT, you have to create a statement that uses multiple LEFT JOINS where the target of each LEFT JOIN is a data element from the frm_item_metas table.
Victor Font answered 1 year ago
When you say "all the entries forms", do you mean entries or forms and fields? If entries, may I ask why you want to construct your own SQL instead of using a View? If you mean forms and fields, then may I suggest this shortcode: https://formidable-masterminds.com/downloads/display-edit-forms-and-fields/
Sina Pars answered 1 year ago
Hi @vfontjr0618 Thanks for response According to the project that I am doing, I need to select the entries data of the forms and insert them in another table with the desired structure of the project. To explain more precisely: Suppose I have a form with 4 fields in this plugin: name last name Job Description All its data is stored as metadata Now I want to select these data using our SQL query So that I can insert in another table with this structure: https://imgur.com/Sm29TKT I just need a select query :( BR  
Sina Pars answered 1 year ago
Hi Again Victor Although I was able to write the query I wanted... But thank you very much for offering me a more standard and optimal way to save a copy of the submitted data in another table with my own structure using the hook below.

Insert form data into second database table

Start your code here
add_action('frm_after_create_entry', 'copy_into_my_table', 20, 2); function copy_into_my_table($entry_id, $form_id){ if($form_id == 4){ //change 4 to the form id of the form to copy global $wpdb; $values = array('col_name1' => $_POST['item_meta'][25], 'col_name2' => $_POST['item_meta'][26]); //replace 25 and 26 with the field ids of the Formidable form. Change col_name to the column names in your table $wpdb->insert('table_name', $values); } }
Source:
https://formidableforms.com/knowledgebase/frm_after_create_entry/#kb-insert-form-data-into-second-database-table Finally, I must say that this plugin is a wonderful plugin for complex and enterprise needs BR  
Nick AmisNick Amis answered 1 year ago
To go down the route of creating a separate table then you can use a SQL of views instead. Here's an example.  
create view lp_match_pivot as
select
    wp_frm_items.id AS match_id,
    (
        case
            when (
                wp_frm_item_metas.field_id = 349
            ) then wp_frm_item_metas.meta_value
        end
    ) AS man_user_id,
    (
        case
            when (
                wp_frm_item_metas.field_id = 330
            ) then wp_frm_item_metas.meta_value
        end
    ) AS man_status,
    (
        case
            when (
                wp_frm_item_metas.field_id = 333
            ) then wp_frm_item_metas.meta_value
        end
    ) AS man_status_date,
    (
        case
            when (
                wp_frm_item_metas.field_id = 350
            ) then wp_frm_item_metas.meta_value
        end
    ) AS woman_user_id,
    (
        case
            when (
                wp_frm_item_metas.field_id = 332
            ) then wp_frm_item_metas.meta_value
        end
    ) AS woman_status,
    (
        case
            when (
                wp_frm_item_metas.field_id = 334
            ) then wp_frm_item_metas.meta_value
        end
    ) AS woman_status_date
from
    wp_frm_item_metas
    join wp_frm_items on (
        wp_frm_item_metas.item_id = wp_frm_items.id
    )
    and (
        wp_frm_items.form_id = 38
    )
where
    (wp_frm_items.is_draft = 0)
    and (
        wp_frm_item_metas.field_id in (349, 330, 333, 350, 332, 334)
    )



create view lp_match as
select
    lp_match_pivot.match_id AS match_id,
    max(lp_match_pivot.man_user_id) AS man_user_id,
    max(lp_match_pivot.man_status) AS man_status,
    max(lp_match_pivot.man_status_date) AS man_status_date,
    max(lp_match_pivot.woman_user_id) AS woman_user_id,
    max(lp_match_pivot.woman_status) AS woman_status,
    max(lp_match_pivot.woman_status_date) AS woman_status_date
from
    local.lp_match_pivot
group by
    lp_match_pivot.match_id
Sina Pars replied 1 year ago

Thanks Nikcs

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