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 12 years ago on Monday March 19, 2012 |
Permalink