How are checkmark fields delimited

[vmantese]

Hi guys,
I'm trying to figure out how i can sql insert checkbox values into civicrm's database.
Whenever I try to extract a checkbox value it shows that each checkbox entry is surrounded by \u0001 unicode characters.

EXAMPLE:

Custom data: elephant status

custom field:scar_locations
checkbox options:head,back,rump,legs,belly.probuscus

I want to be able to MYSQL update the scar_locations field for an elephant, say bruno(id=12)

EG

UPDATE civicicrm_value_elephant_stuff_1
SET scar_locations= 'WHAT GOES HERE' WHERE elephant_id=12

what do I put inside WHAT GOES HERE so that if i navigate inside civicrm bruno will have the checkboxes head and back ticked.
I already tried '\u0001head\u0001back\u0001' which just added the actual text into the field, I want to add the actual escaped delimiter so that it is recognized civicrm.


Thanks guys,
V

[Coleman Watts]

See CRM_Core_DAO::VALUE_SEPARATOR
and CRM_Utils_Array::implodePadded

[vmantese]

Thanks for the quick reply Coleman.
I actually did look at VALUE_SEPARATOR prior to posting but figured I must be mistaken since in my distro

  DB_DAO_NOTNULL = 128,
  VALUE_SEPARATOR = "
",
  BULK_INSERT_COUNT = 200,

and yet while looking at the database itself(via phpmyadmin) I do not see a " " separating the checkbox values. Therefore the space that I'm seeing in my IDE (Netbeans) must be an escaped character.
In fact, when copy pasting the code you can see it must be as the civi forums do not recognize it as a space.

(value separator) "
" vs " " (regular whitepace).

So my question is what is the escape value for "
" that I can use in mysql.

[Coleman Watts]

It sounds like your text editor just isn't showing it to you. VALUE_SEPARATOR is "^A" aka ASCII value 001.

[vmantese]

OK- That checks out. Thanks for the clarification.
The next step is to somehow insert that value into my MYSQL database.

according to MYSQL's documentation, only the following can be escaped in strings.

Table 9.1 Special Character Escape Sequences

Escape Sequence   Character Represented by Sequence
\0   An ASCII NUL (0x00) character.
\'   A single quote (“'”) character.
\"   A double quote (“"”) character.
\b   A backspace character.
\n   A newline (linefeed) character.
\r   A carriage return character.
\t   A tab character.
\Z   ASCII 26 (Control+Z). See note following the table.
\\   A backslash (“\”) character.
\%   A “%” character. See note following the table.
\_   A “_” character. See note following the table.


Thus I found myself unable to escape '^A'.

I tried out various methods and initially thought none of them worked.
Then I found something odd. When i used the \0 delimiter ie \0head\0back\0
the checkboxes are actually ticked inside civiCRM HOWEVER they are not displayed until i click to edit that custom field area.

For example say i insert \0head\0back\0 for bruno.
If i got to bruno's civicrm page and go under elephant status I see nothing under scar locations.
IF I click to edit, I can see that head and back are ticked. Then, if I save the field displays properly.
Essentially this method works for my purposes but I would like to have it so that when I update via MYSQL it also displays right away on the frontend as well.

Thanks for your help coleman.

[Coleman Watts]

Depending on the encoding of your script file, you can probably just copy and paste the character from the civicrm source code into your script. Note that civicrm does not use any escape characters to represent it, just the literal ascii character (which often appears invisible) and that works.