Export Custom Field Data

Administration
1

Greetings!

I am working with the Query Builder module and would like to export data from my custom fields, but in sql I see that all those custom fields are loaded into a single field called “field” in the gibbonPerson table. Can someone help me build a query that pulls just the “Orchestra” portion of the following field entry?

a:8:{s:3:“001”;s:9:“Orchestra”;s:3:“003”;s:3:“CHS”;s:3:“004”;s:11:“Check #1658”;s:3:“005”;s:3:“$40”;s:3:“007”;s:0:“”;s:3:“008”;s:9:“Full Camp”;s:3:“009”;s:4:“Bass”;s:3:“010”;s:0:“”;}

Thanks!

2

Hello & welcome to the community :smiley:

This one can be a bit tricky because the data is serialized, but if you only need to pull out a few fields at a time there is a way:

SELECT (CASE WHEN (@find := LOCATE(‘s:3:“001”;s’, gibbonPerson.fields)) > 0 THEN REPLACE(LEFT( @var := SUBSTRING(gibbonPerson.fields, @find + 15), LOCATE(‘";’, @var)-1), ‘"’, ‘’) ELSE ‘’ END) AS Your Custom Field FROM gibbonPerson`

What this will do is extract a single value by locating the start and end of the string (with a couple variables to help). You can add this statement as part of any query on the gibbonPerson table to get custom field values.

The important part of the above statements is s:3:“001”, where the 001 is the ID of the custom field. When you're editing the custom field in Gibbon you can see the ID number in the url bar as <code class="CodeInline">gibbonPersonFieldID=001. If you need a different custom field you can replace the 001 with the ID of the field you need. You can repeat this statement if you need to pull out a few different custom fields, just change the ID value for each one (and the Your Custom Field part).

Hope this helps! I think it would certainly be great if Query Builder had a way to pull out this data built-in, it’s possibly something we can look into.
3

Hi Sandra, thanks so much for jumping in and providing what is hopefully a workable solution. As you say, one for Query Builder to do natively at some point…as time permits! Thanks again : ) Ross.

4

Fantastic–thank you so much for this walk through of the process!

5

I tried this out and got the return that included my custom field, but it also included all blank cells from other rows in the table. I just copied and pasted, so I’m not sure what went wrong.

I’m also trying to figure out the syntax to join this query to another one. If anyone can help with that, it’s always appreciated.

6

Feel free to share your SQL snippet that isn’t working, and/or the query you’re wanting to join it to.

7

I actually got it figured out, Sandra, but thanks again for being willing to help!