将html实体转换为utf-8并将其插入到mysql数据库中 [英] Converting html entities to utf-8 and inserting them into a mysql database

问题描述

我试图将字符串从HTML-ENTITIES转换为UTF-8，然后将编码后的字符串保存在数据库中. html实体是希腊字母，例如:νω

I am trying to convert a string from HTML-ENTITIES to UTF-8 and then save the encoded string in my database. The html entities are greek letters and look for example like this: νω

现在，我尝试了数千种不同的方法，从仅使用utf8_encode或html_entity_decode开始，直到现在我遇到了mb_convert_encoding()函数. 现在真正的怪异之处是，在转换我的字符串然后输出它时，它正确地编码为utf-8，但是当将此字符串插入到我的数据库中时，我最终得到的东西是:ξÏνω.

Now I tried thousands of different ways, starting from just using utf8_encode or html_entity_decode until now I came across the function mb_convert_encoding(). Now the really weird thing is that when converting my string and then outputting it, it is correctly encoded to utf-8, but when inserting this string into my database I end up getting something like: ξÏνω.

这是编码的代码:

header('Content-Type: text/html; charset=utf-8');
mb_internal_encoding('utf-8');
......
while($arr = $select->fetch_array(MYSQLI_ASSOC))
{   
$text = $arr["greek"];
$result = mb_convert_encoding($text, 'UTF-8', 'HTML-ENTITIES');  
$mysqli->query("UPDATE some SET greek = '".$result."'");    
}

当输出我的查询，然后在phpmyadmin中手动执行sql查询时，它工作正常，因此这似乎不是我的数据库的问题.将编码后的字符串传输到我的数据库时肯定有一些问题...

When outputting my query and then manually doing a sql query in phpmyadmin it works fine, so it doesnt seem to be a problem of my db. There must be some problem when transferring the encoded string to my database...

推荐答案

如您在脚本中所看到的，您正在指示浏览器使用UTF8.这是第一步.

As you see in your script, you are instructing the browser to use UTF8. That is the first step.

但是您的数据库需要相同的东西，并且表上的编码/排序规则也必须为UTF8.

However your database needs the same thing and also the encoding/collation on the tables need to be UTF8 too.

您可以使用utf8_general_ci或utf8_unicode_ci作为排序规则来重新创建表，或者转换现有表(请参见

You can either recreate your tables using utf8_general_ci or utf8_unicode_ci as the collation, or convert the existing tables (see here)

您还需要确保您的数据库连接(即与mysql的php代码)正在使用UTF8.如果您使用的是PDO，那么会有很多文章介绍如何做到这一点.最简单的方法是:

You need to also make sure that your database connection i.e. php code to mysql is using UTF8. If you are using PDO there are plenty of articles that show how to do that. The simplest way is to do:

$mysqli->query('SET NAMES utf8');

注意，您现在要进行的更改是最终更改.如果更改数据库的连接编码，则可能会影响现有数据.

NOTE The change you will make now is final. If you change the connection encoding to your database, you could affect existing data.

EDIT 您可以执行以下操作来设置连接

EDIT You can do the following to set the connection

$mysqli = new mysqli($host, $user, $pass, $db);

if (!$mysqli->set_charset("utf8")) {
   die("Error loading character set utf8: %s\n", $mysqli->error);
}

$mysqli->close();

感兴趣的链接:

是否使用"SET NAMES"

这篇关于将html实体转换为utf-8并将其插入到mysql数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！