How to solve the garbled code in php query sql server database

SQL Server and PHP are two major technologies commonly used for web application development. SQL Server is a relational database, and PHP is a general scripting language. When developing web applications, it is often necessary to query data in SQL Server in PHP code. However, when querying data in a SQL Server database in PHP code, sometimes garbled characters appear in the query results. This article will discuss the garbled code problems encountered when querying SQL Server databases in PHP code and provide corresponding solutions.

1. Problem description

When querying the SQL Server database in PHP code, sometimes garbled characters appear in the query results. The following is an example of garbled characters:

PHP code:

<?php
$sqlsrv = new PDO("sqlsrv:Server=localhost;Database=Test", "sa", "123456");
$stmt = $sqlsrv->prepare("SELECT * FROM users");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($results);
?>

Result:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => é½å¤©æ¶¯
            [age] => 20
        )

    [1] => Array
        (
            [id] => 2
            [name] => å¼ ä¸‰
            [age] => 25
        )

    [2] => Array
        (
            [id] => 3
            [name] => ç½—亦纲
            [age] => 30
        )

)

As you can see, garbled characters appear in the query results.

2. Problem Analysis

In this example, the reason for the garbled characters is the different character sets used by SQL Server and PHP. Specifically, SQL Server uses the UTF-8 character set, while PHP uses the ISO-8859-1 character set by default. Therefore, when querying the SQL Server database in PHP code, PHP will process the UTF-8 character set data obtained from the database as data in the ISO-8859-1 character set, resulting in garbled characters.

3. Solution

In order to solve the problem of garbled characters in the query results, it is necessary to convert the UTF-8 data obtained from SQL Server into ISO-8859-1 data that can be recognized by PHP. Here are some solutions.

1. Set PDO connection options

PDO connection options can be used to set relevant parameters when establishing a PDO connection. PDO provides two character set-related connection options: PDO::MYSQL_ATTR_INIT_COMMAND and PDO::SQLSRV_ATTR_ENCODING. Both options can be used to set the character set during connection to avoid garbled characters.

For SQL Server, you can use the PDO::SQLSRV_ATTR_ENCODING option to set the character set when connecting. The following is the sample code:

<?php
$options = array(
    PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
);
$sqlsrv = new PDO("sqlsrv:Server=localhost;Database=Test", "sa", "123456", $options);
$stmt = $sqlsrv->prepare("SELECT * FROM users");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($results);
?>

In this example, we use the PDO::SQLSRV_ATTR_ENCODING option in the PDO connection to set the character set to UTF-8. This can avoid garbled characters caused by inconsistent character sets.

2. Use PHP functions to convert character sets

In addition to setting the character set in the PDO connection options, you can also use the functions provided by PHP to convert UTF-8 data obtained from SQL Server. ISO-8859-1 data that can be recognized by PHP to avoid garbled characters. The following is a specific example:

<?php
$sqlsrv = new PDO("sqlsrv:Server=localhost;Database=Test", "sa", "123456");
$stmt = $sqlsrv->prepare("SELECT * FROM users");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($results as &$result) {
    $result['name'] = iconv('UTF-8', 'ISO-8859-1', $result['name']);
}

print_r($results);
?>

In this example, we use the iconv() function to convert UTF-8 data obtained from SQL Server to ISO-8859-1 data. This way you can avoid garbled code problems.

4. Summary

When querying the SQL Server database in PHP code, garbled query results are a common problem. The root cause of this problem is that SQL Server and PHP use different character sets, which causes the UTF-8 data obtained from SQL Server to be processed as ISO-8859-1 data, resulting in garbled characters. In order to solve this problem, you can set the character set in the PDO connection options, or you can use the corresponding function in the PHP code to convert the UTF-8 data obtained from SQL Server into ISO-8859-1 data recognized by PHP. The two solutions provided above can effectively avoid the problem of garbled characters. Which solution to choose can be chosen according to the actual situation.

The above is the detailed content of How to solve the garbled code in php query sql server database. For more information, please follow other related articles on the PHP Chinese website!