|
 SOBI2 (Sigsiu Online Business Index 2) allows you to configure custom fields for entries. It's very hard to write a generic SQL query which exports these custom fields that you might have. The sample report that you can download for SOBI2 has been defined for the default fields. This Tutorial will tell you how to export custom SOBI2 data into 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 SOBI2 sample report.
Installation Instructions
- The Sample Report has been configured for the default profile fields in SOBI2. 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 SOBI2 RC 2.9.2.2 and added just one new custom field called "field_hives" in the Custom Fields Manager.
Image 1
- The field data is stored in a database table called jos_sobi2_fields_data:
Image 2
- If you look at the content of the jos_sobi2_fields_data table you will see all the fields that you have defined in the "Custom Profile" screen in the Custom Fields Manager. There is one entry per field and item. Note the fieldid which is the Field ID's. You can see the field ID tin the rightmost column inside the Custom Fields Manager screen in SOBI2. Or you can look in the jos_sobi2_language table and locate the field names and ID's. 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, deleted or unpublished some fields. The sample SOBI2 report that you can download is written for the default fields. In this tutorial I have added one field called "field_hives". In the Custom Fields Managers screen (image 1) we can see that the field ID is 16 for this field.
- Go to SQL 2 Excel > Worksheets in the Administrator interface and locate the SOBI2 - Items Worksheet.
- Click on the title to edit the Worksheet.
- The SQL Query is defined like this:
SELECT a.itemid as `ID`,
a.title as `Title`,
GROUP_CONCAT(DISTINCT e.name ORDER BY e.name) AS `Categories`,
GROUP_CONCAT(DISTINCT e.catid ORDER BY e.name) AS `Category IDs`,
a.hits as `Hits`,
a.visits as `Visits`,
b.name as `Owner Name`,
b.email as `Owner Email`,
MAX(IF(c.fieldid=1,c.data_txt, NULL)) as `Street`,
MAX(IF(c.fieldid=2,c.data_txt, NULL)) as `Postcode`,
MAX(IF(c.fieldid=3,c.data_txt, NULL)) as `City`,
MAX(IF(c.fieldid=4,c.data_txt, NULL)) as `County`,
MAX(IF(c.fieldid=5,c.data_txt, NULL)) as `Federal State`,
MAX(IF(c.fieldid=6,c.data_txt, NULL)) as `Country`,
MAX(IF(c.fieldid=7,c.data_txt, NULL)) as `Email`,
MAX(IF(c.fieldid=8,c.data_txt, NULL)) as `Website`,
MAX(IF(c.fieldid=9,c.data_txt, NULL)) as `Contact Person`,
MAX(IF(c.fieldid=10,c.data_txt, NULL)) as `Phone`,
MAX(IF(c.fieldid=11,c.data_txt, NULL)) as `Fax`,
MAX(IF(c.fieldid=12,c.data_txt, NULL)) as `Hotline`,
MAX(IF(c.fieldid=13,c.data_txt, NULL)) as `Description`
FROM #__sobi2_item a
LEFT JOIN #__users b ON a.owner = b.id
LEFT JOIN #__sobi2_fields_data c on c.itemid=a.itemid
LEFT JOIN #__sobi2_cat_items_relations d on d.itemid=a.itemid
LEFT JOIN #__sobi2_categories e ON e.catid=d.catid
WHERE d.itemid=a.itemid
GROUP BY a.itemid
ORDER BY ID DESC
- If you have deleted or unpublished some fields then you have to remove them from the query. In this case we just want to add one new custom field to the report, "field_hives" / Hives Fan field. In the Custom Fields Manager we can see that the field_hives field has the ID=16 We just need to add one new line to the query to add this field, like this:
SELECT a.itemid as `ID`,
a.title as `Title`,
GROUP_CONCAT(DISTINCT e.name ORDER BY e.name) AS `Categories`,
GROUP_CONCAT(DISTINCT e.catid ORDER BY e.name) AS `Category IDs`,
a.hits as `Hits`,
a.visits as `Visits`,
b.name as `Owner Name`,
b.email as `Owner Email`,
MAX(IF(c.fieldid=1,c.data_txt, NULL)) as `Street`,
MAX(IF(c.fieldid=2,c.data_txt, NULL)) as `Postcode`,
MAX(IF(c.fieldid=3,c.data_txt, NULL)) as `City`,
MAX(IF(c.fieldid=4,c.data_txt, NULL)) as `County`,
MAX(IF(c.fieldid=5,c.data_txt, NULL)) as `Federal State`,
MAX(IF(c.fieldid=6,c.data_txt, NULL)) as `Country`,
MAX(IF(c.fieldid=7,c.data_txt, NULL)) as `Email`,
MAX(IF(c.fieldid=8,c.data_txt, NULL)) as `Website`,
MAX(IF(c.fieldid=9,c.data_txt, NULL)) as `Contact Person`,
MAX(IF(c.fieldid=10,c.data_txt, NULL)) as `Phone`,
MAX(IF(c.fieldid=11,c.data_txt, NULL)) as `Fax`,
MAX(IF(c.fieldid=12,c.data_txt, NULL)) as `Hotline`,
MAX(IF(c.fieldid=13,c.data_txt, NULL)) as `Description`,
MAX(IF(c.fieldid=16,c.data_txt, NULL)) as `Hives Fan`
FROM #__sobi2_item a
LEFT JOIN #__users b ON a.owner = b.id
LEFT JOIN #__sobi2_fields_data c on c.itemid=a.itemid
LEFT JOIN #__sobi2_cat_items_relations d on d.itemid=a.itemid
LEFT JOIN #__sobi2_categories e ON e.catid=d.catid
WHERE d.itemid=a.itemid
GROUP BY a.itemid
ORDER BY ID DESC
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 SOBI2 Workbook in SQL 2 Excel.
-
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
