Hebrew issue when creating csv file using php

Question

I have this code:

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('שדה 1', 'שדה 2', 'שדה 3'));

include('config.php');
$id = 2;
$sql = "SELECT name FROM shoes WHERE event_id = '$id'";
$result = mysqli_query($connection,$sql);

// loop over the rows, outputting them
while ($row = mysqli_fetch_assoc($result)) fputcsv($output, $row);

The first line is ok, I get "שדה 1', 'שדה 2', 'שדה 3" but the information from my db I get like this: "׳“׳•׳¨ ׳‘׳ ׳–׳§"

Why it's not in Hebrew?

You can get it here: https://eventpay.co.il/1.php

Here my config file:

defined('DB_HOST')? null : define('DB_HOST', 'localhost');
defined('DB_USER')?  null : define('DB_USER', '***');
defined('DB_PASS')?  null : define('DB_PASS', '***');
defined('DB_NAME')?  null : define('DB_NAME', '***');

$connection = mysqli_connect(DB_HOST ,DB_USER ,DB_PASS ,DB_NAME);

if (mysqli_connect_errno($connection)){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    die();
}

mysqli_set_charset($connection, "utf8");

As you can see I using "mysqli_set_charset". In addition, my all db collation is utf8_general_ci. In my others php files the details are fine (from the db).

Thank you.

Answer 1

I downloaded the CSV from your site. This was most useful - if only all people did this when asking questions :)

On inspection it showed the first line is Hebrew/Windows-1255 encoded, while the second line is UTF-8 encoded. Look at the file with a decent text editor, such as Notepad++ and try swapping between Encoding types. A hex-editor confirmed my suspicion.

This means that your source code is written in 8bit Hebrew encoding while your DB correctly contains UTF-8 characters. Both strings are return to the client verbatim.

The first line looks correct because what ever you're using to view the .csv is also running in Windows-1255 mode. I suspect you're using Excel, which would explain the result.

As you can't guarantee that everyone is using Windows-1255/Hebrew code page, you're better off changing your source code to UTF-8 encoding. That means, the non-ASCII output of chars from your source code will come out as UTF-8.

If you're using Excel, you now have the problem of telling Excel to treat the file as UTF-8. A UTF-8 Byte Order Mark fulfils this problem. It allows Excel to work but be warned - not all plain text editors understand it.

To add a UTF-8 BOM:

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// UTF-8 BOM
fwrite($output, "\xEF\xBB\xBF");

// output the column headings
fputcsv($output, array('שדה 1', 'שדה 2', 'שדה 3'));

See the following articles:

• How can I output a UTF-8 CSV in PHP that Excel will read properly?
• PHP source code in UTF-8 files; how to interpret properly?