MySQL returns the following error when I want to execute a statement:
SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘xD1’ for column ‘last_update’ at row 1
Here is the code to insert the value:
$connection = DataBaseMySQLConnect::getConnection(self::$dbName);
$query = "INSERT "
. " into `answer` "
. " ( "
. " `reference`, "
. " `language`, "
. " `date`, "
. " `info`, "
. " `body`, "
. " `url_html`, "
. " `url_word`, "
. " `url_pdf`, "
. " `last_update` "
. " ) "
. " VALUE "
. " ("
. " :reference, "
. " :language, "
. " :date, "
. " :info, "
. " :body, "
. " :url_html, "
. " :url_word, "
. " :url_pdf, "
. " :last_update "
. " )";
$stmt = DataBaseMySQLConnect::prepare($query, $connection);
DataBaseMySQLConnect::bindParam($stmt, ':reference', $this->fields['reference'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':language', $this->fields['language'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':date', $this->fields['date'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':info', $this->fields['info'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':body', $this->fields['body'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':url_html', $this->fields['url_html'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':url_word', $this->fields['url_word'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':url_pdf', $this->fields['url_pdf'], 'str');
DataBaseMySQLConnect::bindParam($stmt, ':last_update', $this->fields['last_update'], 'str');
$boolContinue = DataBaseMySQLConnect::execute($stmt);
All the static functions use PDO. I just added log writing.
The connection is created using PHP PDO and I bind the value.
I also use utf8mb4 charset both in the connection and table, and collation utf8mb4_0900_ai_ci in the table.
I tried with utf8mb4_unicode_ci but the error remains the same.
Could someone help me on this topic, please?
I have read a lot of discussions about similar cases but I did not find any efficient solution.
Thanks in advance!
2
Answers
I finally found the solution and it was not a problem of character encoding.
In fact, to avoid Mysql errors when inserting a too long value, a function shortens them if necessary. And this was the case for the Bulgarian value because PHP htmlspecialchar made it much longer than the column size.
As a result, the encoding was inconsistent and could not be inserted.
Anyway, thank you very much for your reactivity and your help!
Try executing following query before executing insert query. I remember having similar issue which was resolved by running the following query after connection initializing;