I'm working with a Drupal site and I need to pull a report of webform submissions.
The Table structure is such:
webform_submissions
- sid
- nid
- serial
- uid
- is_draft
- submitted
- remote_addr
webform_submitted_data
- nid
- sid
- cid
- no
- data
So when a submission is made something like this goes into each table: webform_submissions
sid: 1, nid:100, serial: 1, uid: 0, is_draft: 0, submitted: [timestamp], remote_addr: 0.0.0.0
webform_submitted_data
nid: 100, sid: 1, cid: 1, no: 0, data: John
nid: 100, sid: 1, cid: 2, no: 0, data: Doe
There is also another table that maps the cid to the form field name but I'm not worried about that.
for every row in the webform_submissions table that matches a nid I need a row with all of the corresponding data in the webform_submitted_data.
result_set nid: 100, cid_1: John, cid_2: Doe
the actual form data I need has about 30 fields.
This is the best query I have been able to come up with:
SELECT ws.nid, wd_c2.data, wd_c3.data
FROM webform_submissions ws,
webform_submitted_data wd_c2,
webform_submitted_data wd_c3
WHERE ws.nid = 168
AND ws.sid = wd_c3.sid
AND wd_c3.cid = 3
AND ws.sid = wd_c2.sid
AND wd_c2.cid = 2;
That query takes nearly 20 seconds on my local mysql to pull 8 records. I can only assume my approach is not a good one. What is a better way to get all of the rows related to a submission from the webform_submitted_data table into a single row of columns in my result set.
FULL QUERY updated with Joins (returns empty set).
SELECT ws.nid,
wd_c2.data as 'First Name',
wd_c3.data as 'Last Name',
wd_c4.data as 'Email Address',
wd_c5.data as 'Primary Phone',
wd_c6.data as 'Secondary Phone',
wd_c7.data as 'Address',
wd_c8.data as 'City',
wd_c9.data as 'State',
wd_c10.data as 'Zip Code',
wd_c11.data as 'Cf',
wd_c12.data as 'AR',
wd_c13.data as 'G',
wd_c14.data as 'PE',
wd_c15.data as 'PD',
wd_c16.data as 'AN',
wd_c17.data as 'V',
wd_c18.data as 'S',
wd_c19.data as 'V2',
wd_c20.data as 'S',
wd_c21.data as 'Si',
wd_c22.data as 'D',
wd_c23.data as 'M',
wd_c24.data as 'L',
wd_c25.data as 'V',
wd_c26.data as 'C',
wd_c27.data as 'O',
wd_c28.data as 'M',
wd_c29.data as 'P',
wd_c30.data as 'C',
wd_c31.data as 'C',
wd_c32.data as 'C',
wd_c33.data as 'E',
wd_c34.data as 'Ey',
wd_c41.data as 'TD',
wd_c42.data as 'RN',
wd_c43.data as 'TP',
wd_c45.data as 'LM',
wd_c47.data as 'PIp',
wd_c48.data as 'wd',
wd_c49.data as 'Cr',
wd_c50.data as 'N',
wd_c51.data as 'A'
FROM webform_submissions ws
INNER JOIN webform_submitted_data wd_c2 on ws.sid = wd_c2.sid
INNER JOIN webform_submitted_data wd_c3 on ws.sid = wd_c3.sid
INNER JOIN webform_submitted_data wd_c4 on ws.sid = wd_c4.sid
INNER JOIN webform_submitted_data wd_c5 on ws.sid = wd_c5.sid
INNER JOIN webform_submitted_data wd_c6 on ws.sid = wd_c6.sid
INNER JOIN webform_submitted_data wd_c7 on ws.sid = wd_c7.sid
INNER JOIN webform_submitted_data wd_c8 on ws.sid = wd_c8.sid
INNER JOIN webform_submitted_data wd_c9 on ws.sid = wd_c9.sid
INNER JOIN webform_submitted_data wd_c10 on ws.sid = wd_c10.sid
INNER JOIN webform_submitted_data wd_c11 on ws.sid = wd_c11.sid
INNER JOIN webform_submitted_data wd_c12 on ws.sid = wd_c12.sid
INNER JOIN webform_submitted_data wd_c13 on ws.sid = wd_c13.sid
INNER JOIN webform_submitted_data wd_c14 on ws.sid = wd_c14.sid
INNER JOIN webform_submitted_data wd_c15 on ws.sid = wd_c15.sid
INNER JOIN webform_submitted_data wd_c16 on ws.sid = wd_c16.sid
INNER JOIN webform_submitted_data wd_c17 on ws.sid = wd_c17.sid
INNER JOIN webform_submitted_data wd_c18 on ws.sid = wd_c18.sid
INNER JOIN webform_submitted_data wd_c19 on ws.sid = wd_c19.sid
INNER JOIN webform_submitted_data wd_c20 on ws.sid = wd_c20.sid
INNER JOIN webform_submitted_data wd_c21 on ws.sid = wd_c21.sid
INNER JOIN webform_submitted_data wd_c22 on ws.sid = wd_c22.sid
INNER JOIN webform_submitted_data wd_c23 on ws.sid = wd_c23.sid
INNER JOIN webform_submitted_data wd_c24 on ws.sid = wd_c24.sid
INNER JOIN webform_submitted_data wd_c25 on ws.sid = wd_c25.sid
INNER JOIN webform_submitted_data wd_c26 on ws.sid = wd_c26.sid
INNER JOIN webform_submitted_data wd_c27 on ws.sid = wd_c27.sid
INNER JOIN webform_submitted_data wd_c28 on ws.sid = wd_c28.sid
INNER JOIN webform_submitted_data wd_c29 on ws.sid = wd_c29.sid
INNER JOIN webform_submitted_data wd_c30 on ws.sid = wd_c30.sid
INNER JOIN webform_submitted_data wd_c31 on ws.sid = wd_c31.sid
INNER JOIN webform_submitted_data wd_c32 on ws.sid = wd_c32.sid
INNER JOIN webform_submitted_data wd_c33 on ws.sid = wd_c33.sid
INNER JOIN webform_submitted_data wd_c34 on ws.sid = wd_c34.sid
INNER JOIN webform_submitted_data wd_c41 on ws.sid = wd_c41.sid
INNER JOIN webform_submitted_data wd_c42 on ws.sid = wd_c42.sid
INNER JOIN webform_submitted_data wd_c43 on ws.sid = wd_c43.sid
INNER JOIN webform_submitted_data wd_c45 on ws.sid = wd_c45.sid
INNER JOIN webform_submitted_data wd_c47 on ws.sid = wd_c47.sid
INNER JOIN webform_submitted_data wd_c48 on ws.sid = wd_c48.sid
INNER JOIN webform_submitted_data wd_c49 on ws.sid = wd_c49.sid
INNER JOIN webform_submitted_data wd_c50 on ws.sid = wd_c50.sid
INNER JOIN webform_submitted_data wd_c51 on ws.sid = wd_c51.sid
INNER JOIN webform_submitted_data wd_c52 on ws.sid = wd_c52.sid
WHERE ws.nid = 16818
AND wd_c2.cid = 2
AND wd_c3.cid = 3
AND wd_c4.cid = 4
AND wd_c5.cid = 5
AND wd_c6.cid = 6
AND wd_c7.cid = 7
AND wd_c8.cid = 8
AND wd_c9.cid = 9
AND wd_c10.cid = 10
AND wd_c11.cid = 11
AND wd_c12.cid = 12
AND wd_c13.cid = 13
AND wd_c14.cid = 14
AND wd_c15.cid = 15
AND wd_c16.cid = 16
AND wd_c17.cid = 17
AND wd_c18.cid = 18
AND wd_c19.cid = 19
AND wd_c20.cid = 20
AND wd_c21.cid = 21
AND wd_c22.cid = 22
AND wd_c23.cid = 23
AND wd_c24.cid = 24
AND wd_c25.cid = 25
AND wd_c26.cid = 26
AND wd_c27.cid = 27
AND wd_c28.cid = 28
AND wd_c29.cid = 29
AND wd_c30.cid = 30
AND wd_c31.cid = 31
AND wd_c32.cid = 32
AND wd_c33.cid = 33
AND wd_c34.cid = 34
AND wd_c41.cid = 41
AND wd_c42.cid = 42
AND wd_c43.cid = 43
AND wd_c45.cid = 45
AND wd_c47.cid = 47
AND wd_c48.cid = 48
AND wd_c49.cid = 49
AND wd_c50.cid = 50
AND wd_c51.cid = 51
AND wd_c52.cid = 52
LIMIT 20
Be sure you have proper index on webform_submissions(nid, sid), webform_submitted_data(sid, cid) (and for a better readiing use join notation instead of where/and clause )
SELECT ws.nid, wd_c2.data, wd_c3.data
FROM webform_submissions ws
INNER JOIN webform_submitted_data wd_c2 on ws.sid = wd_c2.sid
INNER JOIN webform_submitted_data wd_c3 on ws.sid = wd_c3.sid
WHERE ws.nid = 168
AND wd_c3.cid = 3
AND wd_c2.cid = 2;
eg for the index
CREATE INDEX ws_id on webform_submissions(nid, sid)
try using left join (so if a value don't match the query work however)
SELECT ws.nid,
wd_c2.data as 'First Name',
wd_c3.data as 'Last Name',
wd_c4.data as 'Email Address',
wd_c5.data as 'Primary Phone',
wd_c6.data as 'Secondary Phone',
wd_c7.data as 'Address',
wd_c8.data as 'City',
wd_c9.data as 'State',
wd_c10.data as 'Zip Code',
wd_c11.data as 'Cf',
wd_c12.data as 'AR',
wd_c13.data as 'G',
wd_c14.data as 'PE',
wd_c15.data as 'PD',
wd_c16.data as 'AN',
wd_c17.data as 'V',
wd_c18.data as 'S',
wd_c19.data as 'V2',
wd_c20.data as 'S',
wd_c21.data as 'Si',
wd_c22.data as 'D',
wd_c23.data as 'M',
wd_c24.data as 'L',
wd_c25.data as 'V',
wd_c26.data as 'C',
wd_c27.data as 'O',
wd_c28.data as 'M',
wd_c29.data as 'P',
wd_c30.data as 'C',
wd_c31.data as 'C',
wd_c32.data as 'C',
wd_c33.data as 'E',
wd_c34.data as 'Ey',
wd_c41.data as 'TD',
wd_c42.data as 'RN',
wd_c43.data as 'TP',
wd_c45.data as 'LM',
wd_c47.data as 'PIp',
wd_c48.data as 'wd',
wd_c49.data as 'Cr',
wd_c50.data as 'N',
wd_c51.data as 'A'
FROM webform_submissions ws
LEFT JOIN webform_submitted_data wd_c2 on ws.sid = wd_c2.sid
LEFT JOIN webform_submitted_data wd_c3 on ws.sid = wd_c3.sid
LEFT JOIN webform_submitted_data wd_c4 on ws.sid = wd_c4.sid
LEFT JOIN webform_submitted_data wd_c5 on ws.sid = wd_c5.sid
LEFT JOIN webform_submitted_data wd_c6 on ws.sid = wd_c6.sid
LEFT JOIN webform_submitted_data wd_c7 on ws.sid = wd_c7.sid
LEFT JOIN webform_submitted_data wd_c8 on ws.sid = wd_c8.sid
LEFT JOIN webform_submitted_data wd_c9 on ws.sid = wd_c9.sid
LEFT JOIN webform_submitted_data wd_c10 on ws.sid = wd_c10.sid
LEFT JOIN webform_submitted_data wd_c11 on ws.sid = wd_c11.sid
LEFT JOIN webform_submitted_data wd_c12 on ws.sid = wd_c12.sid
LEFT JOIN webform_submitted_data wd_c13 on ws.sid = wd_c13.sid
LEFT JOIN webform_submitted_data wd_c14 on ws.sid = wd_c14.sid
LEFT JOIN webform_submitted_data wd_c15 on ws.sid = wd_c15.sid
LEFT JOIN webform_submitted_data wd_c16 on ws.sid = wd_c16.sid
LEFT JOIN webform_submitted_data wd_c17 on ws.sid = wd_c17.sid
LEFT JOIN webform_submitted_data wd_c18 on ws.sid = wd_c18.sid
LEFT JOIN webform_submitted_data wd_c19 on ws.sid = wd_c19.sid
LEFT JOIN webform_submitted_data wd_c20 on ws.sid = wd_c20.sid
LEFT JOIN webform_submitted_data wd_c21 on ws.sid = wd_c21.sid
LEFT JOIN webform_submitted_data wd_c22 on ws.sid = wd_c22.sid
LEFT JOIN webform_submitted_data wd_c23 on ws.sid = wd_c23.sid
LEFT JOIN webform_submitted_data wd_c24 on ws.sid = wd_c24.sid
LEFT JOIN webform_submitted_data wd_c25 on ws.sid = wd_c25.sid
LEFT JOIN webform_submitted_data wd_c26 on ws.sid = wd_c26.sid
LEFT JOIN webform_submitted_data wd_c27 on ws.sid = wd_c27.sid
LEFT JOIN webform_submitted_data wd_c28 on ws.sid = wd_c28.sid
LEFT JOIN webform_submitted_data wd_c29 on ws.sid = wd_c29.sid
LEFT JOIN webform_submitted_data wd_c30 on ws.sid = wd_c30.sid
LEFT JOIN webform_submitted_data wd_c31 on ws.sid = wd_c31.sid
LEFT JOIN webform_submitted_data wd_c32 on ws.sid = wd_c32.sid
LEFT JOIN webform_submitted_data wd_c33 on ws.sid = wd_c33.sid
LEFT JOIN webform_submitted_data wd_c34 on ws.sid = wd_c34.sid
LEFT JOIN webform_submitted_data wd_c41 on ws.sid = wd_c41.sid
LEFT JOIN webform_submitted_data wd_c42 on ws.sid = wd_c42.sid
LEFT JOIN webform_submitted_data wd_c43 on ws.sid = wd_c43.sid
LEFT JOIN webform_submitted_data wd_c45 on ws.sid = wd_c45.sid
LEFT JOIN webform_submitted_data wd_c47 on ws.sid = wd_c47.sid
LEFT JOIN webform_submitted_data wd_c48 on ws.sid = wd_c48.sid
LEFT JOIN webform_submitted_data wd_c49 on ws.sid = wd_c49.sid
LEFT JOIN webform_submitted_data wd_c50 on ws.sid = wd_c50.sid
LEFT JOIN webform_submitted_data wd_c51 on ws.sid = wd_c51.sid
LEFT JOIN webform_submitted_data wd_c52 on ws.sid = wd_c52.sid
WHERE ws.nid = 16818
AND wd_c2.cid = 2
AND wd_c3.cid = 3
AND wd_c4.cid = 4
AND wd_c5.cid = 5
AND wd_c6.cid = 6
AND wd_c7.cid = 7
AND wd_c8.cid = 8
AND wd_c9.cid = 9
AND wd_c10.cid = 10
AND wd_c11.cid = 11
AND wd_c12.cid = 12
AND wd_c13.cid = 13
AND wd_c14.cid = 14
AND wd_c15.cid = 15
AND wd_c16.cid = 16
AND wd_c17.cid = 17
AND wd_c18.cid = 18
AND wd_c19.cid = 19
AND wd_c20.cid = 20
AND wd_c21.cid = 21
AND wd_c22.cid = 22
AND wd_c23.cid = 23
AND wd_c24.cid = 24
AND wd_c25.cid = 25
AND wd_c26.cid = 26
AND wd_c27.cid = 27
AND wd_c28.cid = 28
AND wd_c29.cid = 29
AND wd_c30.cid = 30
AND wd_c31.cid = 31
AND wd_c32.cid = 32
AND wd_c33.cid = 33
AND wd_c34.cid = 34
AND wd_c41.cid = 41
AND wd_c42.cid = 42
AND wd_c43.cid = 43
AND wd_c45.cid = 45
AND wd_c47.cid = 47
AND wd_c48.cid = 48
AND wd_c49.cid = 49
AND wd_c50.cid = 50
AND wd_c51.cid = 51
AND wd_c52.cid = 52
LIMIT 20