PHP processing of HTML encoded strings

When I wrote a PHP script to access the database today, I found that the Chinese strings in it were stored using HTML encoding (for example, the HTML encoding corresponding to the Chinese character "Mao" is "Mao"), which made it possible to perform conditional queries on this field. It is not possible to directly use Chinese for query, but it must be converted into encoding before condition matching.

When converting readable characters and HTML encoded characters in PHP, you need to use the htmlentities and html_entity_decode functions. So write the following query statement,

$sql="selectid,depart_name,first_name,last_name,local_name,extension,mobile,title ";
$sql.="frompb_extensione,pb_departmentd ";
$sql.="wheree.depart_id=d.depart_id ";
$sql.="andd.plant='".$plant."' ";
$sql.="and(first_namelike'%".$HTTP_POST_VARS["q"]."%'";
$sql.="orlast_namelike'%".$HTTP_POST_VARS["q"]."%'";
$sql.="orlocal_namelike'%".htmlentities($HTTP_POST_VARS["q"])."%'";
$sql.="orextensionlike'%".$HTTP_POST_VARS["q"]."%'";
$sql.="ormobilelike'%".$HTTP_POST_VARS["q"]."%'";
$sql.="ordepart_namelike'%".$HTTP_POST_VARS["q"]."%'";
$sql.="ortitlelike'%".$HTTP_POST_VARS["q"]."%'";
$sql.=") ";

and execute it. But something strange happened. This statement can be executed correctly in SQL Analyzer and the result is queried, but when this script is executed in IE, the query result is prompted to be zero. Why is this?

After carefully checking this SQL and viewing the source code in IE, I found the root cause of the problem. The query value is converted into HTML encoding through the htmlentities function. There is a special character & in this encoding (such as the example in the first paragraph). This character has another meaning in IE. In IE, "&" is generally used to represent this symbol, causing the SQL statement output seen in IE to be different from the actually executed statement.

To solve this problem, we need to find a way to prevent IE from using "&" to represent the "&" symbol. After some searching, I found that this problem can be solved by using the following code.

<?php
$trans=get_html_translation_table(HTML_ENTITIES);
$trans=array_flip($trans);
$encoded="Hallo&<Frau>&Kr&auml;mer";
$original=strtr($encoded,$trans);
echo$original;
?>

Execute this code, and the output $original variable value is "Hallo & & Krämer", which is exactly the result I want.

The above are the problems and solutions I encountered at work. I wrote them on my blog to share them with friends who encountered similar problems to me.