Urgently need a way to export user data into usable format!
Our nonprofit organization is using Buddypress for our Student Program’s online community. We are also using it to gather all of the student members’ vital information—contact info, university, major, etc.—using many extended profile fields, some required, some not. We’ve put many hours/weeks/months into creating and promoting this online community, but have come to realize that there is apparently no way to export the data we collected into a usable format.
The users’ data is stored in three different tables in the SQL database: wp_users (with the basic login info), wp_bp_xprofile_fields (with the labels of the extended profile fields), and wp_bp_xprofile_data (with the actual extended profile field data). We need the data in either a single Excel spreadsheet or Access database (ultimately the data goes into our Raiser’s Edge and Convio databases). Currently we can export the three tables as individual spreadsheets, but have not been able to then combine the data into one usable document, with the Profile/Extended Profile Field labels across the top row, and all the data (from the wp and bp data tables) for each user across one line in the spreadsheet.
In the absence of any built-in functionality or plugin, I assume we need to write a SQL query of some kind that does the following:
1 – copies wp_users into a new table.
2 – adds new columns to this table for every field from wp_bp_xprofile_fields where wp_bp_xprofile_fields/type value is other than “option” (“textbox”, “selectbox”, etc.).
3 – takes value from wp_bp_xprofile_data/value and adds it to the correct user in the correct column:
3a – wp_bp_xprofile_data/user_id corresponds to wp_users/ID
3b – wp_bp_xprofile_data/field_id corresponds to wp_bp_xprofile_fields/id
3c – Whenever wp_bp_xprofile_fields/type value is “option”, wp_bp_xprofile_data/field_id will instead correspond to wp_bp_xprofile_fields/parent_id
However, I don’t know much at all about SQL queries. Has anyone had any luck exporting the user data in this (or any other) way? Does anyone have any ideas that could point me in the right direction?
You must be logged in to reply to this topic.