mysql insert data from multiple select queries

Question

What I've got working and it's what I need to improve on:

INSERT form_data (id,data_id, email) 
SELECT '',fk_form_joiner_id AS data_id
     , value             AS email 
  FROM wp_contactform_submit_data 
 WHERE form_key='your-email'

This just gets the emails, now this is great, but not enough as I have a good few different values of form_key that I need to import into different columns, I'm aware that I can do it via php using foreach loops and updates, but this needs to be done purely in mysql.

So how do I do something like: insert form_data(id,data,email,name,surname,etc) Select [..],Select [..]....

The data is stored in the most ridiculous way possible, in one table, IN ONE CELL, with a different cell telling what the actual data is: http://drp.ly/Jhx3J (screenshot)

Please help

Answer 1

In your question it isn't obvious where the other data you want is stored but perhaps you are looking for something,like this:

INSERT form_data (id,data_id, email,city) 
SELECT A.fk_form_joiner_id AS data_id
     , A.value             AS email 
     ,B.value               AS city
  FROM wp_contactform_submit_data A,
       wp_contactform_submit_data B
  WHERE A.form_key='your-email' AND 
        B.form_key='your-city' and
        A.fk_form_joiner_id=B.fk_form_joiner_id

One could extend this logic to handle a smallish number of multiple fields quite easily - if one or more data value sometimes missing you would have to do a outer join between A and B in order to get all records.

Obviously you have to make a new join for each column but this is required in some form to complete your task anyway.

A index on wp_contactform_submit_data(fk_form_joiner_id,form_key) will make this query fairly efficient as well.

Answer 2

That is not a problem, you just neet to join to wp_contactform_submit_data several times (one for each value):

INSERT INTO form_data (data_id, email, name)
SELECT fk_form_joiner_id as data_id, email, name
FROM (SELECT fk_form_joiner_id, value as email
      FROM wp_contactform_submit_data
      WHERE form_key='your-email'
      ) as emails
JOIN (SELECT fk_form_joiner_id, value as name
      FROM wp_contactform_submit_data
      WHERE form_key='your-name') as names
  USING (fk_form_joiner_id)
;