Author Topic: Custom multi select field values in database (Read 1325 times)

travis_aws · October 12, 2012, 05:26:40 am

Hi, I have a custom field of type "AdvMulti-Select" in a custom group (called "contract_information") attached to the membership page.
Im using php to pull these values from the db depending on the member ID.

In the table "civicrm_option_value" I can see the options, eg. option_group_id:84, label:CSIR, value:CSIR

The table that stores the contract_information values is: civicrm_value_contract_information_7 and the selected options are in the member_33 field, however
the value saved here is just a string of the multiselect values concatenated together, ie CSIRNWUUPNRFZoo.

I cant find a table where there's a relationship between the Option ID and the civicrm_value_contract_information_7, is there one?
Or else is there a function I can call to fetch the the chosen select values based on a membership ID

Thanks

totten · October 12, 2012, 06:12:42 am

Quote from: travis_aws on October 12, 2012, 05:26:40 am

I cant find a table where there's a relationship between the Option ID and the civicrm_value_contract_information_7, is there one?

Perhaps you're looking for "civicrm_custom_field"? Given that "civicrm_value_contact_information_7" has the field "member_33", there should be a row in "civicrm_custom_field" with id=33 and option_group_id=84.

travis_aws · October 12, 2012, 06:44:33 am

Hi Totten, there is 1 reference to to the custom field in civicrm_custom_field, but I need to find where all the chosen values are stored.

totten · October 12, 2012, 07:56:44 am

I'm not sure I follow. You've already pointed out "civicrm_option_value" which stores the list of available options (using option_group_id/label/value columns) -- and the "civicrm_value_contact_information_7.member_33" which stores the list of selected options (using concatenated "value"s). What do you mean by "find where all chosen values are stored"? What is an example use-case or query that's presenting a problem?

A couple tangential points which can cause confusion. You may have seen/inferred these already but it's helpful to cover our bases:

* The column "civicrm_option_value.id" is not normally used -- instead, one uses the "civicrm_option_value.value".

* "Concatenated" isn't the best word for "civicrm_value_contact_information_7.member_33". A better term is "delimited". (Many systems use comma or semicolon as a delimiter; this field uses a control character, "^A" aka ASCII value 001.) Depending on what tool you use to query the table, the ^A may render differently -- some tools hide it; other tools display a question mark; others display the letter "A" with a different color. In Civi code, we refer to delimiter with the constant CRM_Core_DAO::VALUE_SEPARATOR.

* The "member_33" field is also padded -- there's an extra ^A at the start and end.

* Example: "^A^A" (empty list)
* Example: "^Afoo^A (list with one item, "foo")
* Example: "^Afoo^Abar^A" (list with two items, "foo" and "bar")

* To search for all contacts whose multi-select includes "foo", use something like this pseudocode:
db_query("SELECT entity_id FROM civicrm_value_contact_information_7 WHERE member_33 like "%s", "^Afoo^A")

travis_aws · October 14, 2012, 09:41:48 am

The values in civicrm_value_contact_information_7.member_33 look like this: "CSIRNWUUPNRFZoo" in both Mysql and PHP.
However when displayed in CiviCRM the do look like this: CSIR, NRF-ZOO, NWU, UP
Is there a delimiter that both PHP and Mysql are not showing, I tried explode("^A", $value) in php but no luck.

travis_aws · October 14, 2012, 09:43:23 am

Found a solution, I used the API:

Code: [Select]

require_once 'api/api.php';
 $result = civicrm_api( 'membership','get',array('version' => 3, 'id' => 5388 );

thanks for the help

CiviCRM Community Forums (archive)

News:

Author Topic: Custom multi select field values in database (Read 1325 times)

travis_aws

Custom multi select field values in database

totten

Re: Custom multi select field values in database

travis_aws

Re: Custom multi select field values in database

totten

Re: Custom multi select field values in database

travis_aws

Re: Custom multi select field values in database

travis_aws

Re: Custom multi select field values in database