Preamble - I've created a form using a WP plugin, it allows my client to easily add/remove fields and so on.
Form submissions go into a table with the following columns:
| submission_id | key | value |
submission_id is used to group together the values of submissions. Key is the field id, value is the value submitted by the user.
The client would like to be able to add /remove fields, as they're currently doing market research on what's best to include in the form. Likely start point is around 30 fields, so a large form that will probably be reduced down as a result of market research.
I want to query the table to give an output per submission. So for example,
| submission_id | key1 | key2 | keyN |
I have managed to do this based on a simple example of 3 fields, here's the script:
SELECT submission_id,
MAX(CASE WHEN `Key` = 'name' THEN Value END) AS **field1**,
MAX(CASE WHEN `Key` = 'email' THEN Value END) AS **field2**,
MAX(CASE WHEN `Key` = 'message' THEN Value END) AS **field3**
FROM 69_e_submissions_values GROUP BY submission_id;
As you can see, I have to define each column header. If possible, I'd like the column header to be defined dynamically by the key value so I don't have to change the script every time the client changes the form...
Is this possible? And if so... can I then format the results into json grouped by the submission id?
