0

I'm building a small database for a charity I volunteer with. It will track events in English and French. When I enter characters like an accented e (as in "journée") it appears in the database as (journée). If I can get it in the database properly the é comes out as a black diamond with a question mark (�).

Can anyone tell me how to prevent this?

Here's my table:

CREATE TABLE `dtf_events` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name_en` VARCHAR(50) NULL DEFAULT NULL,
    `name_fr` VARCHAR(50) NULL DEFAULT NULL,
    `description_en` VARCHAR(256) NULL DEFAULT NULL,
    `description_fr` VARCHAR(256) NULL DEFAULT NULL,
    `publish_start` DATE NULL DEFAULT NULL,
    `publish_end` DATE NULL DEFAULT NULL,
    `event_date` DATE NULL DEFAULT NULL,
    `link` VARCHAR(1024) NULL DEFAULT NULL,
    `category` ENUM('operational','fundraising','news','test') NULL DEFAULT NULL,
    `comment` TEXT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=34
;

And here's a sample of the output I get if the proper accented character is in the db. Note that this code generates a partial page that is intended to fit into an iframe in a text widget on a WordPress site that I have very little administrative control over.

28-f�v-2018 - Date Limite pour demande d'application
24-mar-2018 - Jour d'informations pour volontaire
23-sep-2018 - Journ�e d�orientation: b�n�voles et familles
24-oct-2018 - Journee du Vol 2018mer · 31-d�c-1969 - start is past, end is future

The é in février shows up with a black diamond with a question mark. That is pulled from a DATE field in the database in YYYY-MM-DD format and converted with the strftime() function:

$friendly_date = strftime("%a · %d-%b-%Y",strtotime($event_date));

You can see the output in the Evènements à Venir section on the right side of the page at http://yow.dreamstakeflight.ca/accueil/ (I had nothing to do with the design of that site ;{})

1

After you connect to MySQL in PHP OR in HeidiSQL, use SET NAMES 'utf8'. This is effectively equivalent to:

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

Now try your insert statement containing French characters.

  • Thank you very much. That has solved the majority of my problem. The only thing that's still a problem is that the é in février still shows up with a black diamond with a question mark. That is pulled from a DATE field in the database in YYYY-MM-DD format and converted with the strftime() function, so I can't understand why that would be treated differently. <code>$friendly_date = strftime("%a &middot; %d-%b-%Y",strtotime($event_date));</code> You can see the output in the Evènements à Venir section on the right side of the page at yow.dreamstakeflight.ca/accueil – Rob Feb 12 '18 at 2:37
0

You present two symptoms -- Mojibake and Black diamond. They do not happen in the same circumstances.

Go here: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

Look at "best practice" to see an overview of what should be done.

Look for "Mojibake" to see what may have been done wrong for that symptom.

Look for "black diamond" to see what may have been done wrong for that symptom.

You seem to have established CHARACTER SET utf8 on the columns in question. What about the client bytes? What about the connection parameters? (Without knowing what the client is, I can't be more specific.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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