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.

Querying the Gravity Form DB

Gravity Support Forums » Plugin Support » Gravity Forms

  1. mrkeroppi
    Member

    After scouring various poll and survey plugins to find what I needed I have decided because of GF's flexibility it's probably best to start with it and go from there. What i'm trying to achieve is a poll of sorts.

    Thanks to this post (http://www.gravityhelp.com/forums/topic/getting-values-from-the-database) I was able to figure out the way GF stores the data in the wp_rg_lead_detail.

    Here is an example of the the results taken from a simple form that asks what type of person you are and what's your favorite fruit. (Only example material, This site would be extremely boring if this was my goal.)

    id lead_id field_number value
    1 1 1 Angry
    2 1 2 Apples
    3 2 1 Angry
    4 2 2 Grapes
    5 3 1 Happy
    6 3 2 Apples
    7 4 1 Sad
    8 4 2 Oranges

    For each person type (field_number = 1) I have crated a page and would like to output fruit preferences in percentage format. Obviously my sql skills are lacking. Any help would be greatly appreciated. If there is a better way of doing this please let me know.

    Posted 14 years ago on Thursday June 16, 2011 | Permalink

  2. Alex Cancado
    Administrator

    Try the following. It is a little long, but should get you the information you need.
    You will need to adjust it to fit your real scenario. You might be able to do it yourself by just following the existing pattern in the query, but if you have problems doing that, just let me know and I will help you.

    [sql]
SELECT person, sum(is_apple) as apples, round((sum(is_apple)/count(*))*100) as percent_apple, sum(is_mango) as mangos, round((sum(is_mango)/count(*))*100) as percent_mango FROM(

    SELECT person.value as person, fruit.value as fruit, if(fruit.value = 'Apple', 1, 0) as is_apple, if(fruit.value = 'Mango', 1, 0) as is_mango FROM
    (
        SELECT lead_id, value FROM alien_rg_lead_detail
        WHERE form_id=173 AND field_number=1
    ) person
    INNER JOIN
    (
        SELECT lead_id, value FROM alien_rg_lead_detail
        WHERE form_id=173 AND field_number=2
    ) fruit ON person.lead_id = fruit.lead_id
) stat
GROUP BY person
    Posted 14 years ago on Thursday June 16, 2011 | Permalink

  3. mrkeroppi
    Member

    Thanks Alex, That was the push I needed. I really appreciate the hard work.

    Posted 14 years ago on Thursday June 16, 2011 | Permalink