Knowledge Base

What is the PHP SQL Turkish Character Problem?

Turkish characters (ş, ç, ğ, ü, ö, ı, Ş, Ç, Ğ, Ü, Ö, İ) often cause problems in web applications that work with PHP and MySQL. These problems manifest as characters that are incorrectly saved in the database or displayed incorrectly on the web page. Basically, the source of these problems is the incompatibility of different character encodings. For example, the database may use a different encoding while PHP scripts or HTML pages may use a different encoding. This incompatibility causes characters to be corrupted.

Why Does the Turkish Character Problem Occur?

The main reasons for the Turkish character problem are:

• Different Character Encodings: MySQL, PHP, and HTML can use different character encodings. The most commonly used encodings include UTF-8, Latin1 (ISO-8859-1), and ISO-8859-9 (Latin5). Incompatibility of these encodings leads to problems.
• Database Connection Settings: The character encoding of the connection established between PHP and the database may not be configured correctly.
• HTML Page Encoding: The character encoding of the HTML page may not be specified correctly. The browser may interpret the page with an incorrect encoding.
• PHP Script Encoding: The PHP script itself may be saved with a different encoding.
• Database Table and Column Encodings: Different character encodings may be defined for database tables and columns.

Which Character Encodings Should I Use?

The best approach to solve Turkish character problems is to use the UTF-8 character encoding everywhere. UTF-8 is an implementation of the Unicode character set and supports almost all languages ​​and characters. Therefore, UTF-8 has become the standard for modern web applications.

The table below compares different character encodings in terms of Turkish character support:

How Do I Set the Database (MySQL) Character Encoding?

Follow these steps to set the character encoding of the MySQL database:

1. Database Creation: Specify UTF-8 character encoding when creating the database.
2. Table Creation: Specify UTF-8 character encoding when creating tables as well.
3. Column Creation: Specify UTF-8 character encoding when creating columns as well.
4. MySQL Connection Encoding: Ensure that the connection between PHP and MySQL is set to UTF-8.

Step 1: Database Creation

CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_turkish_ci;

Here, `utf8_turkish_ci` provides a case-insensitive collation that is sensitive to Turkish characters.

Step 2: Table Creation

CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    field_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_turkish_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;

Step 3: Column Creation

To change the character encoding of a column in an existing table, you can use the following SQL command:

ALTER TABLE table_name MODIFY field_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_turkish_ci;

Step 4: MySQL Connection Encoding (PHP)

To specify UTF-8 character encoding when establishing a connection between PHP and MySQL, you can use one of the following methods:

Method 1: `mysqli_set_charset()` function

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set character set to UTF-8
mysqli_set_charset($conn, "utf8");

// ... other operations ...

$conn->close();
?>

Method 2: `SET NAMES utf8` query

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set character set to UTF-8
$conn->query("SET NAMES 'utf8'");

// ... other operations ...

$conn->close();
?>

Important Note: For MySQL version 5.5.3 and later, it is recommended to use the `utf8mb4` character set. `utf8mb4` is a wider version of UTF-8 and supports additional characters such as emojis. In the examples above, you can use `utf8mb4` instead of `utf8` and `utf8mb4_turkish_ci` instead of `utf8_turkish_ci`.

How Do I Set the Character Encoding of an HTML Page?

To set the character encoding of an HTML page, use the `` tag. Add the following line to the `` section:

<meta charset="UTF-8">

This tag informs the browser that the page is encoded with UTF-8. You can also add the following tag for pre-HTML5 versions (but `` is sufficient):

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

How to Set the Character Encoding of a PHP Script?

The PHP script itself must also be saved with UTF-8 encoding. To do this, save the file by selecting the "UTF-8 without BOM" (Byte Order Mark) option in the settings of the text editor you are using (e.g., VS Code, Sublime Text, Notepad++). BOM is a character sequence added to the beginning of the file by some editors and is not necessary for UTF-8 encoding. It can even cause problems in some cases.

How to Solve Turkish Character Problems in Form Data?

If you are experiencing Turkish character problems in form data, make sure that the character encoding of the HTML form is set correctly and that the PHP script is also running as UTF-8. Also, be careful not to perform any conversion operations when retrieving form data and saving it to the database.

Example HTML Form:

<form action="kaydet.php" method="post">
    <label for="ad">Your Name:</label>
    <input type="text" id="ad" name="ad"><br>

    <label for="soyad">Your Surname:</label>
    <input type="text" id="soyad" name="soyad"><br>

    <input type="submit" value="Save">
</form>

Example PHP Script (kaydet.php):

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set character set to UTF-8
mysqli_set_charset($conn, "utf8");

// Get form data
$ad = $_POST["ad"];
$soyad = $_POST["soyad"];

// SQL query
$sql = "INSERT INTO table_name (ad, soyad) VALUES ('$ad', '$soyad')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Can Character Encoding Be Set with the .htaccess File?

Yes, character encoding can also be set with the `.htaccess` file. This method allows you to specify a character encoding that will be valid for all PHP files by making a server-side configuration. However, before using this method, make sure that your server supports `.htaccess` files.

You can set the UTF-8 character encoding by adding the following lines to the `.htaccess` file:

AddDefaultCharset UTF-8
php_value default_charset "UTF-8"

These lines tell the server that the default character encoding is UTF-8 and that PHP should also use UTF-8 as the default character encoding.

How to Fix Incorrectly Saved Turkish Characters in the Database?

To fix Turkish characters that have already been incorrectly saved in the database, you can follow these steps:

1. Back Up the Database: Before performing any operations, back up the database. This allows you to recover your data in case of an error.
2. Identify the Incorrect Encoding: Determine which encoding the data was incorrectly saved with. Usually, an encoding like Latin1 or ISO-8859-9 was used.
3. Convert the Data to UTF-8: Convert the data to UTF-8 using SQL queries.

Example SQL Query:

If the data was incorrectly saved with Latin1 encoding, you can convert it to UTF-8 using the following query:

UPDATE table_name SET field_name = CONVERT(CAST(CONVERT(field_name USING latin1) AS BINARY) USING utf8);

This query converts the data in the `field_name` column from Latin1 to BINARY, then from BINARY to UTF-8. Repeat this process for all columns containing Turkish characters.

Important Note: Before running this query, be sure to test it in a test environment and back up your database. An incorrect operation can cause permanent data corruption.

Case Study: Turkish Character Problem Encountered on an E-Commerce Site

An e-commerce site encountered a problem where Turkish characters were displayed incorrectly in product names and descriptions. Upon investigation, it was found that the database was running with Latin1 encoding, while the HTML pages were using UTF-8 encoding. This incompatibility was causing characters such as "ş", "ç", "ğ" to be corrupted in product names and descriptions.

Solution:

1. The database was converted to UTF-8.
2. Tables and columns were recreated with UTF-8 character encoding.
3. PHP scripts and HTML pages were saved with UTF-8 encoding.
4. The default character encoding was set to UTF-8 with the `.htaccess` file.

Thanks to these steps, all Turkish character problems on the e-commerce site were resolved, and product names and descriptions began to be displayed correctly.

Summary Table: Solution Methods and Usage Areas

Result

Using UTF-8 character encoding is the best approach to solve Turkish character problems in web applications running with PHP and MySQL. Make sure that UTF-8 is configured correctly everywhere, including the database, HTML pages, PHP scripts, and MySQL connection. By implementing the solution methods presented in this article, you can easily solve Turkish character problems and ensure that your web application works correctly.