Postgresql PHP无效字节序列，用于编码UTF8

I have a simple SQL syntax for inserting to table. I'm using Postgresql 8.4 and already set Database encoding to be UTF8, and POSIX for Collation and Character type.

我有一个简单的SQL语法插入表。我正在使用Postgresql 8.4并已将数据库编码设置为UTF8，将POSIX设置为Collation和Character类型。

The query is fine if i run it under pgadmin3, but bring error if i execute in PHP.

如果我在pgadmin3下运行它，查询就没问题，但如果我在PHP中执行，则会出错。

"Internal Server Error: SQLSTATE[22021]:
Character not in repertoire: 7 ERROR: 
invalid byte sequence for encoding \"UTF8\": 0xd85b\nHINT:
This error can also happen if the byte sequence does not match the encoding expected by the server,
which is controlled by \"client_encoding\"

So i tried to set NAMES and client_encoding from PHP(PDO), but still have the same problem

所以我试图从PHP（PDO）设置NAMES和client_encoding，但仍然有同样的问题

$instance->exec("SET client_encoding = 'UTF8';");
$instance->exec("SET NAMES 'UTF8';");

pg_set_client_encoding($link, "UNICODE"); my be work if i'm using native postgresql driver pg_pconnect, but currently i'm using PDO as Driver.

pg_set_client_encoding（$ link，“UNICODE”）;如果我使用本机postgresql驱动程序pg_pconnect，我的工作，但目前我正在使用PDO作为驱动程序。

and i also already set mb_internal_encoding('UTF-8');

我也已经设置了mb_internal_encoding（'UTF-8'）;

Is there any other way to fix this issue ?

有没有其他方法可以解决这个问题？

This error only appear when i trying to insert non ascii word like arabic or japanese word

此错误仅在我尝试插入非阿拉伯语或日语单词的非ascii单词时出现

3 个解决方案

#1

Try to encode into utf-8 with utf8_encode().

尝试使用utf8_encode（）编码为utf-8。

$query = "INSERT INTO student (id, firstName, lastName, age) VALUES (1, 'myFisrtName', 'myLastName', 23)";

pg_exec($connection, utf8_encode($query ));

#2

Answering on an older post but, I just had a similar situation, during a CSV import, I noticed the error: invalid byte sequence for encoding "UTF 8": 0x95 in ....

I've fixed the error by just converting encoding from Windows-1252 to UTF-8 in PHP by using: mb_convert_encoding($fieldValue,'UTF-8','Windows-1252')

回答一个较旧的帖子，但我刚刚遇到类似的情况，在CSV导入过程中，我注意到错误：编码“UTF 8”的无效字节序列：0x95 in ....我通过转换编码修复了错误使用以下命令从Windows-1252到PHP中的UTF-8：mb_convert_encoding（$ fieldValue，'UTF-8'，'Windows-1252'）

    $query = "INSERT INTO student 
              (id, firstName, lastName, age) 
              VALUES 
              (1, '".mb_convert_encoding($firstName,'UTF-8','Windows-1252')."', 
                  '".mb_convert_encoding($lastName,'UTF-8','Windows-1252')."', 23)";

Hope this will help someone.

希望这会对某人有所帮助。

#3

-1

I'am will be can't submit correct unicode SQL-query (quercus for java vary bad work from unicode and all like "SET NAMES 'UTF8';" no working) , and I resolve this from Base64 convertation:

我将无法提交正确的unicode SQL查询（quercus for java改变了unicode的糟糕工作，所有这些都像“SET NAMES'UTF8';”没有用），我从Base64转换中解决了这个问题：

$name_enc = base64_encode($name);       
$res = $db->prepare(
            'INSERT INTO "MyTable"("ID", "Name") VALUES
               (   nextval(\'gen_addresses\'), 
                   convert_from(decode(?, \'base64\'), \'UTF8\'));' 
     )->execute(array($name_enc));

#1