CODEWITHSUNDEEP
phpmysqlencoding
Julia Galden
Julia Galden

Reputation: 399

PHP - File get contents and proper encoding

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!

Upvotes: 0

Views: 105

Answers (1)

Rick James
Rick James

Reputation: 142208

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.

Upvotes: 1

Related Questions