0

Using Drupal 8.9.20 and CiviCRM 5.39.1 (on Ubuntu 18.04 LTS). I need to write a query in MySQL to retrieve the values of an optiongroup.

The OptionGroup is used to assign keywords to a contact record and the number of keywords is different for each record.

There are some examples on StackOverflow for how to create a function to split the string but most / all of them require an "index" parameter to indicate which substring you want to pull out after the ASCII 001 character (that Civi uses to separate the values), but I can't find anything that would return all of them. MySQL returns something like:

------------------
+ keywords_56    +
------------------ 
- 63210013056479 -
------------------

which in reality is 632 100 130 564 79

Thanks in advance A.

2
  • What are you trying to have returned from the SQL? Are you looking for the query result to be 632 100 130 564 79?
    – Demerit
    Feb 23, 2023 at 0:53
  • Hi Demerit, this will be a subquerry of a bigger one. These number correspond with desciptions on another table (where the name of the keyword is stored). I need to build a report that displays the title of the publication, the authors, the abstract, the keywords etc. In CiviCRM DB table naming I am trying to pull the values out of the customfield (above) and match them to civicrm_option_value table where field value = 632 (for example). So I guess have a return in rows so I can do a join or some other match. Does this help?
    – ArisK
    Feb 23, 2023 at 11:27

1 Answer 1

1

The answer at https://stackoverflow.com/a/17942691/8332458 works you just need to have as many "n"s as the maximum number of possible keywords. Below there are 5 but just add more if you need more, e.g. add union all select 6. I'd also just slightly change it to add a WHERE to remove the extra blanks it creates because civi stores the ascii 1 as bookends, like so:

select
  civicrm_value_XXX.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) singlevalue
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN civicrm_value_XXX
  on CHAR_LENGTH(civicrm_value_XXX.keywords_56)
     -CHAR_LENGTH(REPLACE(civicrm_value_XXX.keywords_56, CHAR(1), ''))>=numbers.n-1
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(civicrm_value_XXX.keywords_56, CHAR(1), numbers.n), CHAR(1), -1) <> ''
order by
  id, n

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.