fgetcsv inserting charactor like ?????? into mysql database

Question

I am making a little php application with fgetcsv for inserting multiple record into mysql database. It works fine with english charactor. But when I trying to add foreign language like bengali or hindi it inserts ????? into mysql database.

Here my mysql database table structure

Here a result for foreign language character

Here my application code

<?php

    // Use PDO to connect to the DB
    $dsn = 'mysql:dbname=phpcsv_db;host=localhost';
    $user = 'root';
    $password = '';

    try {
        $dbh = new PDO($dsn, $user, $password);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }

    $handle = fopen('data.csv', "r");

    for($i =1;($data = fgetcsv($handle, 10000, ",")) !== FALSE; $i++) {
        // The query uses placeholders for data
        $sql = "INSERT INTO phpcsv
                    (name,email,phone) 
                VALUES
                    (:name,:email,:phone)";
        $sth = $dbh->prepare($sql);

        // The data is bound to the placeholders
        $sth->bindParam(':name', $data[0]);
        $sth->bindParam(':email', $data[1]);
        $sth->bindParam(':phone', $data[2]);

        // The row is actually inserted here
        $sth->execute();
        $sth->closeCursor();
    }
ini_set('auto_detect_line_endings',FALSE);

fclose($handle);

Here the data csv file I am using and some foreign character here শ্যামল অসিম কার্তিক

Any suggestion or solution will highly appreciate. Thank you.

Verify that your CSV file in encoded in UTF-8 like your database table. For instance, I opened it in Notepad++ and it also shows ?????? for me [link](https://i.imgur.com/8S0rIDG.png) — Sergey Benzenko, Jan 24 '18 at 05:23
Possible duplicate of [How to make MySQL handle UTF-8 properly](https://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly) — Milan Chheda, Jan 24 '18 at 05:25
Yeah, I've noticed that. Notepad++ gives me same result. It will be best if we solve this issue programmly. — S.k.joy, Jan 24 '18 at 06:02
Possible duplicate of [Converting a Word document into usable HTML in PHP](https://stackoverflow.com/questions/198721/converting-a-word-document-into-usable-html-in-php) — SherylHohman, Jan 24 '18 at 06:04

score 1 · Answer 1 · answered Jan 24 '18 at 05:29

1

Use fgets() get file all string in a variable, then use mb_convert_encoding() to convert encoding, then str_getcsv() convert string to array.

if (($handle = fopen("books.csv", "r")) === FALSE)
    throw new Exception("Couldn't open books.csv");

$data = "";

// get file all strin in data
while (!feof($handle)) {
    $data .= fgets($handle, 5000);
}

// convert encoding
$data = mb_convert_encoding($data, "UTF-8", "auto");

// str_getcsv
$array = str_getcsv($data);

the from array you can save it in db.

answered Jan 24 '18 at 05:29

Shobi

10,374
6
46
82

This. We process multiple CSV's and encoding is the worst, we also need to ensure the data is parsed as UTF-8 for other purposes. – Joshua Jan 24 '18 at 05:57
Sorry, I tried but couldn't figure it out. Can you please change my code with your code? – S.k.joy Jan 24 '18 at 06:03
hmm... now the `$array` variable contains all the data from the csv file properly encoded. verify it by `var_dump($array);` . then loop through that array and try to insert it in to database, – Shobi Jan 24 '18 at 06:07
var_dump($array) return like this image http://prntscr.com/i4rkvv – S.k.joy Jan 24 '18 at 06:16

fgetcsv inserting charactor like ?????? into mysql database

1 Answers1