PHP7, export mySQL to CSV dont show special characters correct

Question

I have a html form that stores data to mysql. When special characters are used (øæå), it store the characters correcly in the database.

When i use a script to save the mysql table to an CSV file, the CSV file wont show special characters correct anymore.

Any ideas?

$conn = mysqli_connect($hostname, $user, $password, $database, $port);
if (mysqli_connect_errno()) {
die("Failed to connect to MySQL: " . mysqli_connect_error());
}

$csv_export = '';

$query = mysqli_query($conn, "SELECT * FROM ".$db_record);
$field = mysqli_field_count($conn);

for($i = 0; $i < $field; $i++) {
$csv_export.= mysqli_fetch_field_direct($query, $i)->name.';';
}

$csv_export.= '
';

while($row = mysqli_fetch_array($query)) {

for($i = 0; $i < $field; $i++) {
$csv_export.= '"'.$row[mysqli_fetch_field_direct($query, $i)->name].'";';
}
$csv_export.= '
';
}

header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=".$csv_filename."");
echo($csv_export);

Try data might be treated differently while storing in csv. If it is a json column, try doing json_decode before saving it. — Hariraman Radhakrishnan, May 08 '18 at 17:34

score 0 · Answer 1 · answered May 08 '18 at 19:00

I assume your script to save the mysql to CSV is run in linux shell or windows command prompt. Problem could be that data stored to database uses different character-set than environment that your script uses.

Check in which format data is stored to database or change it to UTF-8:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

And make sure your script environment uses the same character-set. On Ubuntu linux putting following lines to the file /etc/environment might do the trick (da_DK is for Denmark as just a guess from your characters, you need to set it to one your characters use):

LC_ALL=da_DK.UTF-8
LANG=da_DK.UTF-8

Do not use that flavor of `ALTER` until you have confirmed that "Columns are `CHARACTER SET latin1` (etc) and correctly encoded as such. but want utf8/utf8mb4 without touching other columns". — Rick James, May 10 '18 at 03:37

score 0 · Answer 2 · answered May 10 '18 at 03:34

Without any symptoms, it is hard to say which of several fixes are necessary. See Trouble with UTF-8 characters; what I see is not what I stored for a list of possible symptoms, together with what causes them.

When loading via LOAD DATA, be sure to specify the character set of the data as part of the statement.

HTML forms should start like <form accept-charset="UTF-8">.

Do not do any ALTERs until you have confirmed that the chosen syntax will be helpful, not make things worse.

score 0 · Accepted Answer · answered Jun 11 '20 at 19:11

0

The solution to this issue was to declare mysqli_char_set by doing this: mysqli_set_charset($conn,"utf8");

answered Jun 11 '20 at 19:11

phingoc

101
1
9

PHP7, export mySQL to CSV dont show special characters correct

3 Answers3