1

I have a mysql table which is filled with inputs from a webform on my website. The form has fields for last name, surname, email, phone, address, etc.... and when a user submits the form these data are stored in a mysql table in a rather strange way. my table looks like this:

subission# | value                | field   | tstamp    | and   |many |more |columns
=====================================================================================
1          |[email protected]       |email    |1448898875 |       |     |     |
1          |john                  |firstname|1448898875 |       |     |     |
1          |doe                   |lastname |1448898875 |       |     |     |
1          |london                |city     |1448898875 |       |     |     |
2          |[email protected]          |email    |1448898870 |       |     |     |
2          |jane                  |firstname|1448898870 |       |     |     |
2          |doe                   |lastname |1448898870 |       |     |     |
2          |new york              |city     |1448898870 |       |     |     |
3          |tim @aol.com          |email    |1448838571 |       |     |     |
3          |tim                   |firstname|1448838571 |       |     |     |
3          |smith                 |lastname |1448838571 |       |     |     |
3          |paris                 |city     |1448838571 |       |     |     |

I need to export these data to a csv file in order to import it to a newsletter script on some other server, but the server expects these data in a different format:

submission#,email,firstname,lastname,tstamp,.....
1,[email protected],john,doe,london,1448898875,,,,
2,[email protected],jane,doe,1448898870,,,,

The export as csv is not the problem, but how do I get all the data of one submission# into one row? Can anyone please point me into the right direction, how to accomplish this with SQL?

Improve this question
2
  • If I were you, I'd used the field values as columns instead of values. Now you have got to have at least one value that is the same in all the rows that you want to combine. I'd say that using timestamp isn't very save, cause what will happen if two users were added add the same timestamp? Commented Dec 9, 2015 at 16:19
  • I think the subission column serves that purpose, unfortunately you're going to have to joint the table to itself once per field to get all the results with MySQL. Then just select ... into outfile.... Commented Dec 9, 2015 at 16:31

1 Answer 1

Reset to default
2

You can achieve the desired output, if you concatenate the field contents into a single field using concat() and group_concat() functions, where the values are separated by comma.

The only issue can be if for a particular submission any of the properies is missing. If that's the case, then you will need a helper table which lists all properies and you need to left join on that table. Since this is not the case for your sample data, I'm not providing the code for this scenario.

select concat(submission, ',', group_concat(`value` order by `field` asc), ',',tstamp)
from table group by submission, tstamp

If you need the field names in the 1st row, then create a separate query that conatenates the field names separated by commas and combine the 2 with union.

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

1 Comment

thank you so much, this works like a charm. You just saved my day!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.