Get data from “contact-form-7-to-database-extension” plugin

Get horizontal data from “contact-form-7-to-database-extension” plugin

WordPress “contact-form-7-to-database-extension” plugin stores data in two tables:

  • wp_cf7dbplugin_st ———- stores submit_time as the key
  • wp_cf7dbplugin_submits — stores data

The data fields stored in the table is in vertical form.

cf7-vertical-data

Transform data into horizontal form

Get field_value as column, and join them together to get horizontal data.

SELECT Name,concat(RIGHT(year,2),' ',major) Class,Phone,Email,Wechat
FROM (SELECT field_value name,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='your-name') t1
JOIN (SELECT field_value phone,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='phone') t2 on t1.submit_time=t2.submit_time
JOIN (SELECT field_value year,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='start-year') t3 on t1.submit_time=t3.submit_time
JOIN (SELECT field_value major,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='major') t4 on t1.submit_time=t4.submit_time
JOIN (SELECT field_value wechat,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='wechat') t5 on t1.submit_time=t5.submit_time
JOIN (SELECT field_value email,submit_time FROM wp_cf7dbplugin_submits WHERE form_name='会员登记' AND field_name='your-email') t6 on t1.submit_time=t6.submit_time

Result in horizontal data

displayed-data

45 total views, 1 views today

Leave a Reply