CiviCRM Community Forums (archive)

Hey,

From my previous post I would imagine I change the character set for the database and the server to be utf8 also, is that correct?

No, settings that you have should be enough - I have exactly the same as you and extended characters end up in the database just fine.

And can someone explain to me why this only causes a problem when I go through the API and NOT when I update in CiviCRM itself or directly in the MySQL database?

As Lobo mentioned, there shouldn't be any difference - both ways use practically the same "infrastructure" to initialise DB connection. Would be good to check it though.

Is "é" the only character that you have problems with? Did you try any other extended characters?

Thx,
m

As far as other special characters go, I have now tried

RenëÖó&egrave

and I end up with

Ren

in CiviCRM.....

the mysql log should be in /var/log/mysql.log (or the path u specified)

is this on a windows machine? maybe the OS is the culprit

lobo

I have in the meantime done another conversion. I have made sure that all MySQL schemas, databases and fields have utf8 and unicode. But I still have the same problem, even when using a Linux machine. For this particular conversion I have worked around the problem by using the following function after a contact, location or note has been added of the type Organisation:
private function correct_specials($field) {

/* this function checks if there are special characters
   in name or address fields, and updates the fields
   directly in the Civi database.  */
global $db_civi;
$name = NULL;
$digit = 0;
$specials = array("ë", "í","á", "é","ü", "ú", "ñ","ä","ö", "ó", "ï");
$update = FALSE;

switch($field){

case "name":

/* check organisation */
foreach ($specials as $special ){
$digit = strpos($this->name, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
/* if special chars found */
if ($update) {
$name = $db_civi->real_escape_string($this->name);

$upd_name = "UPDATE civicrm_contact SET sort_name =
'$name', display_name = '$name', organization_name =
'$name' WHERE id = $this->id";
$r_name = $db_civi->query($upd_name) or die("Can't
execute SQL-query " .$upd_name ." (Class Organisation,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

case "address":

/* check address1 */
foreach ($specials as $special ){
$digit = strpos($this->address1, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
/* check city1 if still required */
if (!$update) {
foreach ($specials as $special ){
$digit = strpos($this->city1, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
}

/* if special chars found */
if ($update) {

$street = $db_civi->real_escape_string($this->address1);
$city = $db_civi->real_escape_string($this->city1);

$upd_adr = "UPDATE civicrm_address SET street_address =
'$street', city = '$city' WHERE contact_id =
$this->id";
$r_adr = $db_civi->query($upd_adr) or die("Can't
execute SQL-query " .$upd_adr ." (Class Organisation,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

case "note":

/* check note */
foreach ($specials as $special ){
$digit = strpos($this->note5, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}

/* if special chars found */
if ($update) {

$note = $db_civi->real_escape_string($this->note5);

$upd_note = "UPDATE civicrm_note SET note =
'$note' WHERE contact_id = $this->id";
$r_note = $db_civi->query($upd_note) or die("Can't
execute SQL-query " .$upd_note ." (Class Organisation,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

} // switch
}


and this when an Individual is added:
private function correct_specials($field) {

/* this function checks if there are special characters
   in name or address fields, and updates the fields
   directly in the Civi database. */
global $db_civi;
$first = NULL;
$middle = NULL;
$last = NULL;
$sort = NULL;
$diplay = NULL;
$digit = 0;
$specials = array("ë", "í","á", "é","ü", "ú", "ñ","ä","ö", "ó", "ï");
$update = FALSE;

switch($field){

case "name":

/* check surname */
foreach ($specials as $special ){
$digit = strpos($this->surname, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
/* check first name if still required */
if (!$update) {
foreach ($specials as $special ){
$digit = strpos($this->firstname, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
}
/* check middle name if still required */
if (!$update) {
foreach ($specials as $special ){
$digit = strpos($this->middlename, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
}

/* if special chars found */
if ($update) {
$first = $db_civi->real_escape_string($this->firstname);
$middle = $db_civi->real_escape_string($this->middlename);
$last = $db_civi->real_escape_string($this->surname);
$sort = $last.", ".$first;

if (empty($middle)) {
if (empty($first)) {
$display = $last;
} else {
$display = $first." ".$last;
}
} else {
if (empty($first)) {
$display = $middle." ".$last;
} else {
$display = $first." ".$middle." ".$last;
}
}

$upd_name = "UPDATE civicrm_contact SET first_name =
'$first', last_name = '$last', middle_name =
'$middle', sort_name = '$sort', display_name =
'$display', addressee_display = '$display'
WHERE id = $this->id";
$r_name = $db_civi->query($upd_name) or die("Can't
execute SQL-query " .$upd_name ." (Class Individual,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

case "address":

/* check address2 */
foreach ($specials as $special ){
$digit = strpos($this->address2, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
/* check city2 if still required */
if (!$update) {
foreach ($specials as $special ){
$digit = strpos($this->city2, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}
}

/* if special chars found */
if ($update) {

$street = $db_civi->real_escape_string($this->address2);
$city = $db_civi->real_escape_string($this->city2);

$upd_adr = "UPDATE civicrm_address SET street_address =
'$street', city = '$city' WHERE contact_id =
$this->id";
$r_adr = $db_civi->query($upd_adr) or die("Can't
execute SQL-query " .$upd_adr ." (Class Individual,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

case "note":

/* check note */
foreach ($specials as $special ){
$digit = strpos($this->note5, $special);

if (is_integer($digit)) {
$update = TRUE;
}
}

/* if special chars found */
if ($update) {

$note = $db_civi->real_escape_string($this->note5);

$upd_note = "UPDATE civicrm_note SET note =
'$note' WHERE contact_id = $this->id";
$r_note = $db_civi->query($upd_note) or die("Can't
execute SQL-query " .$upd_note ." (Class Individual,
function correct_specials), error: " .
$db_civi->errno. ", " . $db_civi->error);

}
break;

} // switch
}

After completion of this project and doing a blog about it, I will dive into this issue and see if I can work out where the problem is, and if there is a way to solve it in the API.

Erik