Display rows based on sql results

Question

I am using caldera forms in my WordPress site to collect data for new admissions for our club. Form functions as expected. I am trying to view a list of open applications and a page to view them. Following is my SQL table tor entries (cf_form_entries).

+----+-----------------+--------+
| ID | Form ID         | Status |
+----+-----------------+--------+
| 1  | CF5852c23e56b1d | active |
+----+-----------------+--------+
| 2  | CF5852c23e56b1d | active |
+----+-----------------+--------+
| 3  | CF5852c23e56b1d | active |
+----+-----------------+--------+

Following table contains all the information submitted by the form (cf_form_entry_values);

+----+----------+---------------+--------------------+
| id | entry_id |     slug      |       value        |
+----+----------+---------------+--------------------+
|  1 |        1 | branch        | Branch A           |
|  2 |        1 | full_name     | asdasd asdasd      |
|  3 |        1 | email_address | [email protected] |
|  4 |        1 | phone         | 111111111          |
|  5 |        2 | branch        | Branch A           |
|  6 |        2 | full_name     | Full Name          |
|  7 |        2 | email_address | [email protected]   |
|  8 |        2 | phone         | 111111111          |
|  9 |        3 | branch        | Branch A           |
| 10 |        3 | full_name     | Namwe              |
| 11 |        3 | email_address | [email protected]  |
| 12 |        3 | phone         | 111111111          |
+----+----------+---------------+--------------------+

I can run a simple select query and get the open application details of a given branch, inner joining tables.

SELECT cf_form_entries.id,
       cf_form_entries.form_id,
       cf_form_entries.status,
       cf_form_entry_values.slug,
       cf_form_entry_values.value
FROM cf_form_entry_values
  INNER JOIN cf_form_entries
     ON cf_form_entry_values.entry_id = cf_form_entries.id
WHERE cf_form_entry_values.slug = 'branch'
   AND cf_form_entry_values.value LIKE '%Branch A%'

Above query results in the following table;

+----+-----------------+--------+--------+----------+
| id |     form_id     | status |  slug  |  value   |
+----+-----------------+--------+--------+----------+
|  1 | CF5852c23e56b1d | active | branch | Branch A |
|  3 | CF5852c23e56b1d | active | branch | Branch A |
+----+-----------------+--------+--------+----------+

My question is, How can I display (echo) the other details such as the name, email address, etc. of the selected tables?

As such my end result shall display all details of the open applications in a table. (not just the branch name)

I tried a while loop. But I can only echo the branch names as they are the only data selected in my inner joined table.

@AmitKumarSahu, Like I said, they are in the cf_form_entry_values — Sid
– Sid, Commented Dec 17, 2016 at 12:30

Gordon Linoff · Accepted Answer · 2016-12-17 12:47:15Z

2

One method is conditional aggregation:

SELECT fe.id, fe.form_id, fe.status,
       MAX(CASE WHEN fev.slug = 'branch' THEN fev.value END) as branch,
       MAX(CASE WHEN fev.slug = 'full_name' THEN fev.value END) as full_name,
       . . .
FROM cf_form_entries fe INNER JOIN
     cf_form_entry_values fev
     ON fev.entry_id = fe.id
GROUP BY fe.id, fe.form_id, fe.status;

The nice thing about this approach is that adding a new value only requires adding a new expression in the SELECT.

answered Dec 17, 2016 at 12:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sid Over a year ago

Seems fine. How can I add WHERE, so that I can select results owing to one branch?

Sid Over a year ago

I tried adding where and ended up with an empty result. Considering using HAVING instead

Gordon Linoff Over a year ago

@Sid . . . HAVING branch = ??.

Sachin Sanchaniya · Accepted Answer · 2016-12-17 12:31:41Z

1

Try Following Query By Using LEFT JOIN ON Two Tables.

SELECT cfev.*,cfm.Form ID as entry_id 
     FROM cf_form_entry_values cfev 
         LEFT JOIN cf_form_entries cfm ON cfm.id = cfev.entry_id 
             WHERE cfev .slug = 'branch' AND vfm.value LIKE '%Branch A%'

This Might Be Helpful. And You Get Proper Output...

answered Dec 17, 2016 at 12:31

Sachin Sanchaniya

1,0541 gold badge9 silver badges19 bronze badges

Comments

Jeremy Gurr · Accepted Answer · 2016-12-17 12:38:28Z

This should give you the first two values, and you can use the same pattern for the rest:

SELECT entries.id,
       entries.form_id,
       entries.status,
       (SELECT value FROM cf_form_entry_values as v 
        WHERE slug='branch' and entry_id=entries.entry_id) as branch,
       (SELECT value FROM cf_form_entry_values as v 
        WHERE slug='full_name' and entry_id=entries.entry_id) as full_name
FROM cf_form_entry_values
INNER JOIN (
  SELECT * FROM cf_form_entry_values
  INNER JOIN cf_form_entries
    ON cf_form_entry_values.entry_id = cf_form_entries.id
  WHERE cf_form_entry_values.slug = 'branch'
    AND cf_form_entry_values.value LIKE '%Branch A%'
  ) as entries
  ON cf_form_entry_values.entry_id = entries.id
GROUP BY cf_form_entries.id

Collectives™ on Stack Overflow

Display rows based on sql results

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related