The problem occurs when the field name of the table in SQLServer is Chinese. the written query statement is tested in SQLServe and returned through a record. an error is returned when the SQL extension query in PHP is used.
I. problems:
The database is MS SQLServer2000. you need to import data from a table in SQLServer2000 to mysql5. the fields in the SQLServer2000 table are named in Simplified Chinese (it is strongly recommended that the field name be Chinese ). In fact, the operation is to query SQL server records and insert them into MySQL. The selected scripting language is PHP. enabling MSSQL and MySQL extensions in PHP makes operations on these two databases very easy.
The problem occurs when the field name of the table in SQL Server is Chinese. the written query statement is tested in SQL Server and returned by a record. an error is returned when the SQL extension query in PHP is used.
I checked the information online. there is not much information on the internet. many netizens think that PHP's MSSQL extension does not support Chinese characters in SQL statements. After checking the information, php mssql supports Chinese statements in SQL. Most of the errors are caused by encoding. the encoding database and the encoding are inconsistent with the query statement encoding, and the Chinese part of the query statement in SQLServer becomes garbled, resulting in query failure.
II. solution:
After knowing the cause, analyze and solve the problem and confirm that the encoding is not uniform. The solution involves the following steps:
1. confirm the encoding of the SQLServer Database. my data encoding is GBK.
2. confirm the encoding of the current PHP script file. my encoding is UTF-8.
3. convert the encoding of the SQL query statement.
Supplement: some netizens mentioned that PHP script file encoding should be consistent with the database encoding. In fact, this step is not necessary, as long as you confirm that your SQL statement is consistent with the database encoding, we recommend that you do not need to convert it because if your PHP script file contains other PHP scripts, you must encode and convert all include or require script files, otherwise, PHP script encoding is not uniform and prone to errors. if there are many files associated with each other, this is also a very troublesome problem and complicated things.
III. solution:
Write a conversion function to encode and convert SQL statements before performing SQL operations. The following is my sample code:
The code is as follows:
// Encoding conversion function
Function utf8togb ($ s ){
Return iconv ('utf-8', 'gbk // IGNORE ', $ s); // when the IGNORE parameter is a character that cannot be converted, IGNORE
}
// We recommend that you replace all Chinese fields with English aliases to facilitate encoding and conversion in the following operations.
$ SQL = "SELECT [id], [column] as typeid, [subject] as title, [author] as author, convert (text, [body]) as body FROM [document table]; ";
$ SQL = utf8togb ($ SQL );
