PHP - File get contents and proper encoding

Question

I'm struggeling with a tiny script which is responsible for 2 things: - truncating database - uploading files into database

Looks like that:

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');


$mysql_host = 'localhost';
$mysql_username = 'x';
$mysql_password = 'y';
$mysql_database = 'z';

$db = new PDO('mysql:dbname='.$mysql_database.';host='.$mysql_host,$mysql_username,$mysql_password);

// works not with the following set to 0. You can comment this line as 1 is default
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

function truncate_db()
{
    global $db;

    $sql_query_1 = "
    TRUNCATE TABLE `VISITS`; 
    TRUNCATE TABLE `ANIMALS`;
    TRUNCATE TABLE `DOCTORS`;
    TRUNCATE TABLE `PAYMENTS`;
    TRUNCATE TABLE `CUSTOMER`
    "; 

    try {
        $stmt = $db->prepare($sql_query_1);
        $stmt->execute();
        echo "Truncate action - OK";
    }
    catch (PDOException $e)
    {
        echo $e->getMessage();
        die();
    }
}

function import_db()
{
    global $db;

    try
       {
         $sql_query_2 = implode(array_map(function ($v) {
            return file_get_contents($v);
            }, glob(__DIR__ . "/*.sql")));

         $qr = $db->exec($sql_query_2); 
         echo "Import action - OK";
       }
       catch (PDOException $e) 
       {
         echo 'Connection failed: ' . $e->getMessage();
    }
}

truncate_db();
echo '<br />';
import_db();

$db = null;
?>

Issue - files (sql one) which I'm uploading to the database contains special charaters (like ś, ó, ę etc.) After that I have an issue in the database that some of the words doesn't contain any more those symbols. After upload I have symbols like: ³, ¿ etc. How can I edit function import_db() to keep those characters? i thought about:

mb_convert_encoding

but I have no clue how to incorporate that into my code ;/ in my DB table, column with that words (containing special characters) is set to: UTF8_General_CI. thanks!

try this - $db = new PDO('mysql:dbname='.$mysql_database.';host='.$mysql_host,$mysql_username,$mysql_password;charset=utf8); — Sachin Vairagi, Apr 28 '16 at 13:55
not working. When I use your code OR $db->exec('SET NAMES utf8'); it just cut-off everything in the word after that special character :( — Julia Galden, Apr 28 '16 at 13:58
Can you please tell, where did you write $db->exec('SET NAMES utf8');? — Sachin Vairagi, Apr 28 '16 at 14:05
please check this link for all possible options, I hope this will help you - http://stackoverflow.com/questions/8002822/pdo-and-utf-8-special-characters-in-php-mysql — Sachin Vairagi, Apr 28 '16 at 14:06
I've placed it just after: $db = new PDO('mysql:dbname='.$mysql_database.';host='.$mysql_host,$mysql_username,$mysql_password); — Julia Galden, Apr 28 '16 at 14:07
@SachinVairagi - partially yes :) the only issue now is.. my files which I'm uploading must be converted into UTF8 w/o BOM. Is there any easy way in php how to convert it before upload? — Julia Galden, Apr 28 '16 at 14:19
@JuliaGalden , I don't understand why do you want to convert file into UTF8? can you please tell what is your basic issue and desired output? — Sachin Vairagi, Apr 29 '16 at 05:12
Why upload a file to the database, instead just upload the file to the server and then store the path to it into the database — Ikhlak S., Apr 30 '16 at 16:29

score 1 · Accepted Answer · answered Apr 30 '16 at 15:31

I'm unclear on what your problem is

Truncated text is when you try to insert Señor, but find that only Se shows up in the table. This form of truncation is usually caused by

The client had accented characters encoded in latin1 (or latin2, etc), and
SET NAMES utf8 was in effect during the INSERT (or LOAD DATA)

That is, you should either get the text in utf8 or you should change what you tell MySQL the encoding is.

If you can get the hex of the file contents, ś ó ę should be 2-byte hex C59B C3B3 C499 in utf8. latin1 only has ó as a single byte F3. In latin2, those are B6 F3 EA, so perhaps that is where you are coming from?

It is OK to have a mismatch between what SET NAMES says and what CHARACTER SET you have established to table/column. MySQL will convert the encoding as they are transferred.

Do not use mb_convert_encoding or any other conversion functions when using MySQL, it only adds to the confusion.

Also, do the TRUNCATEs one at a time. The API does not like multiple statements.

PHP - File get contents and proper encoding

1 Answers1