When using MySQL database in PHP, you often encounter the problem of garbled code in the query results returned by the mysqli_result function. Usually, the garbled problem is caused by the character encoding not being set correctly. This article will explain why unset character encoding will cause garbled query results and provide solutions.
Character encoding is the way in which characters are stored and represented in a computer. The character sets of each language need to be clearly encoded to be displayed correctly. For example, UTF-8 encoding is a widely used character set that is able to support characters in a variety of languages. If the character encoding is inconsistent, the system will not be able to correctly identify and display the text, resulting in garbled code.
In MySQL database, each database, table, field, and connection can have its own character encoding. If the character encoding of the database connection is inconsistent with the character encoding of the database or table, garbled problems may occur.
When we use the mysqli_result function to get the query results, the default character set of MySQL may not be the character set we expect. In particular, garbled code is prone to occur if the character set of the database and table is not set correctly, or the character set of the database connection is not set correctly.
mysqli_result is a function in PHP to get the results of MySQL query. When we use mysqli_query to execute SQL query, it returns a mysqli_result object. Then we can use fetch_assoc() , fetch_row() and other methods in the object to get the query results.
For example:
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
$result = mysqli_query($conn, "SELECT * FROM users");
while($row = mysqli_fetch_assoc($result)) {
echo $row['name'];
}
In the above code, assuming that the field name of the query result contains Chinese characters, if the character encoding is not set correctly, the Chinese characters in the query result may be garbled.
By default, the character set of MySQL connections is determined by the configuration of the MySQL server. If the character set of the MySQL server is inconsistent with the data encoding we want to process, the characters in the query result may be decoded incorrectly, resulting in garbled code.
For example, suppose we store Chinese characters in the MySQL database and the character set of the table is UTF-8. However, if the PHP script uses Latin1 (i.e. ISO-8859-1) character set when connecting to the database, the data returned by the database will be decoded in Latin1 encoding, not UTF-8. Because the two encodes are different, the returned Chinese characters will appear garbled.
To solve the character encoding problem, we need to make sure that the correct character encoding is set when the database connection is connected. Character encoding can be set in the following ways:
After establishing a database connection, use the mysqli_set_charset() function to set the character encoding:
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
mysqli_set_charset($conn, 'utf8'); // Set the character encoding toUTF-8
This ensures that the database connection is encoded using UTF-8 characters, thus avoiding garbled issues.
We also need to make sure that the character encoding of the database and tables is also set to UTF-8. The character sets of databases and tables can be checked and modified by the following SQL statements:
View the database character set:
SHOW CREATE DATABASE database_name;
Modify the database character set:
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
View the character set of the table:
SHOW TABLE STATUS FROM database_name;
Modify the character set of the table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
In some cases, it may also be necessary to set the client character set by executing the following SQL statement:
SET NAMES 'utf8';
This ensures that the character encoding between the client and the MySQL server is consistent when the query is executed.
The garbled problem is usually caused by inconsistent character encoding settings, especially when using the mysqli_result function to get query results. If the character encoding is not set correctly, MySQL will use the default character set (usually Latin1), resulting in garbled query results. To avoid this problem, you should ensure:
Set the correct character encoding (e.g. UTF-8) when connecting to the database.
Ensure that the character sets of the database, table, and client are consistent.
If you still encounter problems, you can try setting SET NAMES 'utf8' to ensure consistency of character encoding.
Through these methods, the query results can be effectively avoided and garbled code can be ensured to correctly display the data.