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.
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.