PLEASE NOTE: These forums are no longer utilized and are provided as an archive for informational purposes only. All support issues will be handled via email using our support ticket system. For more detailed information on this change, please see this blog post.

How to query database

  1. placson
    Member

    I've looked at the wp_rg tables and I found that this query will return all the fields and their values. However the field_number is just a number. Where are the values for the field_number stored?

    select id, field_number, value from wp_rg_lead_detail where form_id=1;

    I have 3 forms and the 1st form has form_id = 1. The values returned are:

    | 17971 |5.3| Stockton
    | 17972 |5.4 | California
    | 17973 |5.5 | 95212

    Where are my field_number values?

    Posted 8 years ago on Monday March 12, 2012 | Permalink
  2. placson
    Member

    Hi there.. is this possible to do from SQL or do I have to deserialize the strings stored in the form table?

    Posted 8 years ago on Tuesday March 13, 2012 | Permalink
  3. placson
    Member

    Hello? Any help out there?

    Posted 8 years ago on Thursday March 15, 2012 | Permalink
  4. mobilus
    Member

    I'm also interested in learning how to generate a list of my entries. If you get help, can you post your findings. thanks, Mike

    Posted 8 years ago on Monday March 19, 2012 | Permalink
  5. placson
    Member

    I had to do a complicated subquery in order to get my results. There's no native way to read the field_number values from the wp_rg_form table to get the column names. Essentially the problem is that Gravity Forms allows for dynamic tables by creating rows in the wp_rg_lead_detail table. To display your rows as a column you have to do a row to column, pivot table. To achieve this a sub query is required. My query ended up looking like this mess:

    1) I created a view called 'bjj'

    create view bjj as select * from wp_rg_lead_detail where form_id=1

    2) My query looked like this madness:

    select lead_id as entryId
    ,(select value from bjj where field_number=1 and lead_id = entryId) as email
    ,(select value from bjj where round(field_number,2) = 2.3 and lead_id = entryId) as first_name
    ,(select value from bjj where round(field_number,2) = 2.6 and lead_id = entryId) as last_name
    ,(select value from bjj where round(field_number,2) = 5.1 and lead_id = entryId) as address1
    ,(select value from bjj where round(field_number,2) = 5.2 and lead_id = entryId) as address2
    ,(select value from bjj where round(field_number,2) = 5.3 and lead_id = entryId) as city
    ,(select value from bjj where round(field_number,2) = 5.4 and lead_id = entryId) as state
    ,(select value from bjj where round(field_number,2) = 5.5 and lead_id = entryId) as zip
    ,(select value from bjj where round(field_number,2) = 5.6 and lead_id = entryId) as country
    ,(select value from bjj where round(field_number,2) = 63.0 and lead_id = entryId) as phone
    ,(select value from bjj where round(field_number,2) = 66.0 and lead_id = entryId) as age
    from bjj
    group by entryId order by entryId

    Since GF uses floats for the field_number you have to accomodate by doing the ugly round(...). You have to know firsthand what your field_numbers map to. In my case I had to manually examine the field_number/values pair to find out what they were mapped to (e.g. 66.0 mapped to the age column).

    Hope that helps you out.

    Posted 8 years ago on Monday March 19, 2012 | Permalink
  6. mobilus
    Member

    Thanks for taking the time to detail your solution. This will definitely help. Cheers,
    Mike

    Posted 8 years ago on Monday March 19, 2012 | Permalink