|
 JomSocial allows you to configure custom fields for your user profiles - Custom Profiles in JomSocial. That is great since you have total flexibility to setup what data you want to capture for each user on your site. Exporting the data can be a little tricky since you can customize JomSocial so much. This Tutorial will tell you how to export your custom user profile data to an Excel spreadsheet with SQL 2 Excel.
- Download and install SQL 2 Excel component, if you haven't done that already.
- Download and install the JomSocial - User Profiles sample report.
Installation Instructions
- The Sample Report has been configured for the default profile fields in JomSocial. If you have added or removed some fields then you will need to modify the SQL query from the Sample Report to match your configuration. Follow the steps below to customize the report.
- For this tutorial, I installed JomSocial 1.2.206 and added just one new custom field called "Credit Card" to the Custom Profile in JomSocial.
Image 1
- The field definitions are stored in a database table called jos_community_fields:
Image 2
- If you look at the content of the jos_community_fields table you will see all the fields that you have defined in the "Custom Profile" screen in JomSocial. Use phpMyAdmin or something similar to examine the content of your database. Most ISP's provide you with phpMyAdmin through the control panel of your website.
You may have added some new fields and unpublished some fields. Click on the thumbnail below to see the content of the jos_community_fields table on my server. Note the Credit Card field that I added to the default Jom Social fields at the bottom of the list. Also note the ID's for each field. We need these ID's later when we write/modify the SQL query.
Image 3
The values for each user is stored in another table called jos_community_fields_values. If you look in that table you'll see the entries. One row per parameter. This makes it a little tricky to write a SQL query so that you get one clean table with a user and all the fields on teh same row. But it's not so complicated. You can use an aggregate function in MySQL and group the results per user.
- Go to SQL 2 Excel > Worksheets in the Administrator interface and locate the JomSocial Sample Worksheet.
- Click on the title to edit the Worksheet.
- The SQL Query is defined like this:
SELECT a.userid as `User ID`,
u.name as `Name`,
u.email as `Email`,
u.registerDate as `Register`,
u.lastvisitDate as `Last Visit`,
u.activation as `Activated`,
a.status as `Status`,
a.points as `Points`,
a.friendcount as `Friend Count`,
MAX(IF(b.field_id=2,b.value, NULL)) AS 'Gender',
MAX(IF(b.field_id=3,b.value, NULL)) AS 'Birth Day',
MAX(IF(b.field_id=4,b.value, NULL)) AS 'Hometown',
MAX(IF(b.field_id=5,b.value, NULL)) AS 'About me',
MAX(IF(b.field_id=7,b.value, NULL)) AS 'Mobile',
MAX(IF(b.field_id=8,b.value, NULL)) AS 'Land Phone',
MAX(IF(b.field_id=9,b.value, NULL)) AS 'Address',
MAX(IF(b.field_id=10,b.value, NULL)) AS 'State',
MAX(IF(b.field_id=11,b.value, NULL)) AS 'City / Town',
MAX(IF(b.field_id=12,b.value, NULL)) AS 'Country',
MAX(IF(b.field_id=13,b.value, NULL)) AS 'Website',
MAX(IF(b.field_id=14,b.value, NULL)) AS 'Education',
MAX(IF(b.field_id=15,b.value, NULL)) AS 'College / University',
MAX(IF(b.field_id=16,b.value, NULL)) AS 'Graduation Year'
FROM jos_community_users a
LEFT JOIN jos_community_fields_values b ON a.userid=b.user_id
LEFT JOIN jos_community_fields f ON f.id = b.field_id
LEFT JOIN jos_users u ON u.id = a.userid
GROUP BY a.userid
ORDER BY u.name, a.userid
- In this case we just want to add one new custom field to the report, the "Credit card" field. If you look in the jos_community_fields table (Image 3) you'll see that the ID for the Credit Card field is 17. We just need to add one new line to the query to add this field, like this:
SELECT a.userid as `User ID`,
u.name as `Name`,
u.email as `Email`,
u.registerDate as `Register`,
u.lastvisitDate as `Last Visit`,
u.activation as `Activated`,
a.status as `Status`,
a.points as `Points`,
a.friendcount as `Friend Count`,
MAX(IF(b.field_id=2,b.value, NULL)) AS 'Gender',
MAX(IF(b.field_id=3,b.value, NULL)) AS 'Birth Day',
MAX(IF(b.field_id=4,b.value, NULL)) AS 'Hometown',
MAX(IF(b.field_id=5,b.value, NULL)) AS 'About me',
MAX(IF(b.field_id=7,b.value, NULL)) AS 'Mobile',
MAX(IF(b.field_id=8,b.value, NULL)) AS 'Land Phone',
MAX(IF(b.field_id=9,b.value, NULL)) AS 'Address',
MAX(IF(b.field_id=10,b.value, NULL)) AS 'State',
MAX(IF(b.field_id=11,b.value, NULL)) AS 'City / Town',
MAX(IF(b.field_id=12,b.value, NULL)) AS 'Country',
MAX(IF(b.field_id=13,b.value, NULL)) AS 'Website',
MAX(IF(b.field_id=14,b.value, NULL)) AS 'Education',
MAX(IF(b.field_id=15,b.value, NULL)) AS 'College / University',
MAX(IF(b.field_id=16,b.value, NULL)) AS 'Graduation Year',
MAX(IF(b.field_id=17,b.value, NULL)) AS 'Credit Card'
FROM jos_community_users a
LEFT JOIN jos_community_fields_values b ON a.userid=b.user_id
LEFT JOIN jos_community_fields f ON f.id = b.field_id
LEFT JOIN jos_users u ON u.id = a.userid
GROUP BY a.userid
ORDER BY u.name, a.userid
If you have deleted or unpublished any of the default fields, just remove them from the Sample Query.
-
Save the Worksheet, then try to download the JomSocial Workbook in SQL 2 Excel.
Here's a sample spreadsheet that was exported from one of my development servers. No real data, but you see how it is formatted.
-
You may need to adjust column widths to fit your custom field information. The column widths are specified as a comma delimited string:
Add/remove/edit values as needed to make your spreadsheet look nice.
Please note that the screenshot is from SQL 2 Excel Pro which has a few more controls, but the Column Widths are settable in the free version as well.
 |
Subscribe to this comment's feed
