PHP读取mssql,json数据中文乱码 - StarkSoft

PHP及网页使用UTF-8编码，数据库是sql server2008,使用默认编码（936，即GBK编码）

当读取数据库数据时，使用php自带的json_encode()返回到前端，结果中文不显示。

解决办法：

<span style="color: #000000;">php    
    </span><span style="color: #008080;">header</span>("Content-Type: text/html;charset=utf-8"<span style="color: #000000;">);
    </span><span style="color: #008000;">//</span><span style="color: #008000;">告诉浏览器不要缓存数据</span>
    <span style="color: #008080;">header</span>("Cache-Control: no-cache"<span style="color: #000000;">);    
    
    </span><span style="color: #0000ff;">require</span> "../conn.php"<span style="color: #000000;">;
    </span><span style="color: #0000ff;">require</span> "../share/json_gbk2utf8.php"<span style="color: #000000;">;

    </span><span style="color: #800080;">$query</span> = '<span style="color: #000000;">SELECT 
                seq,
                employeeID,
                employeeName,
                department,
                position,
                sex,
                birthday,
                entryTime,
                description,
                convert(varchar(20),createTime,120) as createTime,<span style="color:#FF0000;">//这里要注意，因为mssql2008的datetimne类型是带有毫秒的，直接在前端显示
可能会有问题，所以要做一次转换</span>
                convert(varchar(20),updateTime,120) as updateTime                            
            FROM employees</span>'<span style="color: #000000;">;    
    </span><span style="color: #800080;">$arr</span> = <span style="color: #0000ff;">Array</span><span style="color: #000000;">();
    </span><span style="color: #800080;">$query</span> = <span style="color: #008080;">iconv</span>("utf-8", "gbk//ignore", <span style="color: #800080;">$query</span>);<span style="color: #008000;">//</span><span style="color: #008000;">为了解决中文乱码问题    </span>
    <span style="color: #0000ff;">if</span>(<span style="color: #800080;">$result</span> = sqlsrv_query(<span style="color: #800080;">$conn</span>, <span style="color: #800080;">$query</span><span style="color: #000000;">)){
        </span><span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span> = sqlsrv_fetch_array(<span style="color: #800080;">$result</span><span style="color: #000000;">)){
            </span><span style="color: #800080;">$arr</span>[] = <span style="color: #800080;">$row</span><span style="color: #000000;">;            
        }
    }
    </span><span style="color: #800080;">$data</span> = JSON(<span style="color: #800080;">$arr</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">     file_put_contents("E:/mylog.log", "JSON:".$data."\r\n", FILE_APPEND);    </span>
    <span style="color: #0000ff;">echo</span> <span style="color: #800080;">$data</span><span style="color: #000000;">;
</span>?>

 

<span style="color: #000000;">php
</span><span style="color: #008000;">//</span><span style="color: #008000;">json_gbk2utf8.php</span><span style="color: #008000;">
/*</span><span style="color: #008000;">*************************************************************
 *为了实现对含有中文字符的数组进行json编码
 *
*  使用特定function对数组中所有元素做处理
*  @param  string  &$array     要处理的字符串
*  @param  string  $function   要执行的函数
*  @return boolean $apply_to_keys_also     是否也应用到key上
*  @access public
*
************************************************************</span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">function</span> arrayRecursive(&<span style="color: #800080;">$array</span>, <span style="color: #800080;">$function</span>, <span style="color: #800080;">$apply_to_keys_also</span> = <span style="color: #0000ff;">false</span><span style="color: #000000;">)
{
    </span><span style="color: #0000ff;">static</span> <span style="color: #800080;">$recursive_counter</span> = 0<span style="color: #000000;">;
    </span><span style="color: #0000ff;">if</span> (++<span style="color: #800080;">$recursive_counter</span> > 1000<span style="color: #000000;">) {
        </span><span style="color: #0000ff;">die</span>('possible deep recursion attack'<span style="color: #000000;">);
    }
    </span><span style="color: #0000ff;">foreach</span> (<span style="color: #800080;">$array</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$key</span> => <span style="color: #800080;">$value</span><span style="color: #000000;">) {
        </span><span style="color: #0000ff;">if</span> (<span style="color: #008080;">is_array</span>(<span style="color: #800080;">$value</span><span style="color: #000000;">)) {
            arrayRecursive(</span><span style="color: #800080;">$array</span>[<span style="color: #800080;">$key</span>], <span style="color: #800080;">$function</span>, <span style="color: #800080;">$apply_to_keys_also</span><span style="color: #000000;">);
        } </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
</span><span style="color: #008000;">//</span><span style="color: #008000;">             file_put_contents("E:/mylog.log", "原始:".$value."\r\n", FILE_APPEND);</span>
            <span style="color: #800080;">$value</span> = <span style="color: #008080;">iconv</span>("gbk//ignore", "utf-8", <span style="color: #800080;">$value</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">             file_put_contents("E:/mylog.log", "utf-8:".$value."\r\n", FILE_APPEND);</span>
            <span style="color: #800080;">$array</span>[<span style="color: #800080;">$key</span>] = <span style="color: #800080;">$function</span>(<span style="color: #800080;">$value</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">             file_put_contents("E:/mylog.log", "urlencode:".$array[$key]."\r\n", FILE_APPEND);</span>
<span style="color: #000000;">        }

        </span><span style="color: #0000ff;">if</span> (<span style="color: #800080;">$apply_to_keys_also</span> && <span style="color: #008080;">is_string</span>(<span style="color: #800080;">$key</span><span style="color: #000000;">)) {
            </span><span style="color: #800080;">$new_key</span> = <span style="color: #800080;">$function</span>(<span style="color: #800080;">$key</span><span style="color: #000000;">);
            </span><span style="color: #0000ff;">if</span> (<span style="color: #800080;">$new_key</span> != <span style="color: #800080;">$key</span><span style="color: #000000;">) {
                </span><span style="color: #800080;">$array</span>[<span style="color: #800080;">$new_key</span>] = <span style="color: #800080;">$array</span>[<span style="color: #800080;">$key</span><span style="color: #000000;">];
                </span><span style="color: #0000ff;">unset</span>(<span style="color: #800080;">$array</span>[<span style="color: #800080;">$key</span><span style="color: #000000;">]);
            }
        }
    }
    </span><span style="color: #800080;">$recursive_counter</span>--<span style="color: #000000;">;
}

</span><span style="color: #008000;">/*</span><span style="color: #008000;">*************************************************************
 *
*  将数组转换为JSON字符串（兼容中文）
*  @param  array   $array      要转换的数组
*  @return string      转换得到的json字符串
*  @access public
*
************************************************************</span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">function</span> JSON(<span style="color: #800080;">$array</span><span style="color: #000000;">) {
    arrayRecursive(</span><span style="color: #800080;">$array</span>, 'urlencode', <span style="color: #0000ff;">true</span><span style="color: #000000;">);
    </span><span style="color: #800080;">$json</span> = json_encode(<span style="color: #800080;">$array</span><span style="color: #000000;">);
    </span><span style="color: #0000ff;">return</span> <span style="color: #008080;">urldecode</span>(<span style="color: #800080;">$json</span><span style="color: #000000;">);
}
</span><span style="color: #008000;">/*</span><span style="color: #008000;">
$array = array
(
        'Name'=>'希亚',
        'Age'=>20
);


echo JSON($array);
</span><span style="color: #008000;">*/</span>
?>

这样，sql server 2008中的中文就可以在网页正常显示了。

 

如果要将中文正常插入到sql server 2008中，还要加入一条代码：$query = iconv("utf-8", "gbk//ignore", $query);//为了解决中文乱码问题

完整代码如下 ：

<span style="color: #000000;">php 
    </span><span style="color: #008000;">/*</span><span style="color: #008000;">*
     * 如果员工编号在MySql中不存在则在MySql中插入员工记录
     * 如果该员工编号已经存在则进行更新操作
     </span><span style="color: #008000;">*/</span>
    <span style="color: #008000;">//</span><span style="color: #008000;">如果用JSON格式则要使用text/html，不能使用text/xml</span>
    <span style="color: #008080;">header</span>("Content-Type: text/html;charset=utf-8"<span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">     header("Content-Type: text/html;charset=GBK");
    //告诉浏览器不要缓存数据</span>
    <span style="color: #008080;">header</span>("Cache-Control: no-cache"<span style="color: #000000;">);
    
    </span><span style="color: #0000ff;">require</span> '../conn.php'<span style="color: #000000;">;
    </span><span style="color: #800080;">$seq</span> = <span style="color: #800080;">$_POST</span>["seq"<span style="color: #000000;">];
    </span><span style="color: #800080;">$employeeID</span> = <span style="color: #800080;">$_POST</span>["employeeID"<span style="color: #000000;">];
    </span><span style="color: #800080;">$employeeName</span> = <span style="color: #800080;">$_POST</span>["employeeName"<span style="color: #000000;">];
    </span><span style="color: #800080;">$department</span> = <span style="color: #800080;">$_POST</span>["department"<span style="color: #000000;">];

    </span><span style="color: #0000ff;">if</span>(!<span style="color: #0000ff;">isset</span>(<span style="color: #800080;">$seq</span>) || <span style="color: #800080;">$seq</span> == ""){<span style="color: #008000;">//</span><span style="color: #008000;">seq不存在则插入新记录</span>
        <span style="color: #800080;">$query</span> = "<span style="color: #000000;">INSERT INTO employees (employeeID, employeeName, department, 
                    createTime, updateTime)
                VALUES (N'</span><span style="color: #800080;">$employeeID</span>',N'<span style="color: #800080;">$employeeName</span>',N'<span style="color: #800080;">$department</span><span style="color: #000000;">', 
                    getdate(), getdate())</span>"<span style="color: #000000;">;
    }</span><span style="color: #0000ff;">else</span>{<span style="color: #008000;">//</span><span style="color: #008000;">如果seq已存在则更新已有记录</span>
        <span style="color: #800080;">$query</span> = "UPDATE employees SET employeeID='<span style="color: #800080;">$employeeID</span><span style="color: #000000;">', 
                employeeName='</span><span style="color: #800080;">$employeeName</span>',department='<span style="color: #800080;">$department</span><span style="color: #000000;">',
                updateTime=getdate()  
            WHERE seq='</span><span style="color: #800080;">$seq</span>'"<span style="color: #000000;">;
    }
    
</span><span style="color: #008000;">//</span><span style="color: #008000;">     file_put_contents("E:/mylog.log", $query."\r\n",FILE_APPEND);//用于调试</span>
    <span style="color:#FF0000;"><span style="color: #800080;">$query</span> = <span style="color: #008080;">iconv</span>("utf-8", "gbk//ignore", <span style="color: #800080;">$query</span>);<span style="color: #008000;">//</span><span style="color: #008000;">为了解决中文乱码问题</span></span>
    <span style="color: #0000ff;">if</span>(<span style="color: #800080;">$result</span> = sqlsrv_query(<span style="color: #800080;">$conn</span>, <span style="color: #800080;">$query</span><span style="color: #000000;">)){
        </span><span style="color: #0000ff;">echo</span> <span style="color: #0000ff;">true</span><span style="color: #000000;">;
    }</span><span style="color: #0000ff;">else</span><span style="color: #000000;">{
        </span><span style="color: #0000ff;">echo</span> <span style="color: #0000ff;">false</span><span style="color: #000000;">;
    }
</span><span style="color: #008000;">//</span><span style="color: #008000;">     echo $query;</span>
?>