需要一个mysql查询来从mm表单中提取数据

Question

http://cl.ly/201a11b430bf60ef9e97

is what the rows look like, after I clean them up a bit:

SELECT fk_form_joiner_id,form_key,value FROM wp_contactform_submit_data WHERE form_key!='page_post_id' AND form_key!='captcha-802' AND form_key!='page_post_title' AND form_key!='user_ID'

What I need is a query where it would transform this into a structure like:

id | First-name | Surname-name | your-email | dob | address-1 | address-2 | county | postcode | phone
-------------------------------------------------------------------------------------------------------
88 | jack       | Somethin     | [e-mail]    | dob | (etc)

I think I know a way to do this through php, but I think there must be a way to do it as a mysql query.

Ultimately, I need to export this (into a csv), but if you could give me a query that would output this into a mysql table (or at least a workable select query, that I could then turn into an insert query loop, to create a table), I'd do it form there.

Thank you.

Answer 1

SELECT DISTINCT(fk_form_joiner_id) AS jid, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'First-name') AS first_name, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'Surname-name') AS last_name, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'dob') AS dob, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'address-1') AS address1, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'address-2') AS address2, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'town') AS town, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'county') AS county, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'postcode') AS postcode, (SELECT value FROM wp_contactform_submit_data WHERE fk_form_joiner_id = jid AND form_key = 'phone') AS phone FROM wp_contactform_submit_data ORDER BY jid INTO OUTFILE '/var/www/vhosts/domain.co.uk/httpdocs/exp/output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
' 

BOOM.