Issue Storing Arabic Chars in Database

Question

I'm trying to make a registration form where people will take the link and enter their information, which then gets stored in the database. Unfortunately, when I try to enter information in Arabic using the form ,it gets stored in database as an unknown chars.

I have tried the following without success.

• Set the default charset in the index as follows:

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

• Set the default charset in the database handler page:

  $cfg['DefaultCharset'] = 'utf_8';
  $cfg['DefaultConnectionCollation'] = 'utf8_general_ci';
  

• Changed the database Collation to utf8_general_ci

• Changed the table Collation to utf8_general_ci

• Exported as sql and edit the charset but I found it UTF-8 already.

• Made sure that my browser unicode is set to UTF-8.

And that's an example on how it works:

<form action="signup.php" method="POST" class="form">
<input type="text" name="first"><br />
<input type="text" name="uid"><br />
<input type="date" name="dob"><br />

-

$first = $_POST['first'];
$uid = $_POST['uid'];
$dob = $_POST['dob'];

$sql = "INSERT INTO students (first, uid, dob)
 VALUES ('$first','$uid', '$dob')";

$result = mysqli_query($conn, $sql);

And they're connected through a required file to connect to the db.

$cfg['DefaultCharset'] = 'utf_8';
$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

$conn = mysqli_connect("localhost", "root", "", "test2");

if (!$conn ) {
    die("Connection failed: ". mysqli_connect_error());

}

No idea what's causing this and I need a fast fix.

That's how it looks: https://i.stack.imgur.com/xiQwJ.png

Answer 1

I have two possible suggestions:

Set your mysqli connection to utf-8 via set_charset() or if that fails you can use mb_convert_encoding

So for example,

if (!mysqli_set_charset($conn, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($conn));
    exit();
} else {
    printf("Current character set: %s\n", mysqli_character_set_name($conn));
}

and if that fails or if you wish to use both in conjunction you could just do something like so as well:

$first = mb_convert_encoding($first, "UTF-8");
// and so forth

Answer 2

I suspect the issue you are having relates to character sets but on a per-column basis. If you created the tables and databases before changing the character sets then it's possible not all character sets were changed.

Verify my suspicions by reviewing the output of SHOW CREATE TABLE <tablename> similar to below:

mysql> show create table arabic;
+--------+---------------------------------------------------------------+
| Table  | Create Table                                                  |
+--------+---------------------------------------------------------------+
| arabic | CREATE TABLE `arabic` (
  `words` varchar(60) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------+

Notice the column words still has the charset latin1. Either modify the individual column like this:

mysql> ALTER TABLE arabic CHANGE words words VARCHAR(60) CHARACTER SET utf8;
Query OK, 0 rows affected (1.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

This other post here might be of some help also How to convert an entire MySQL database characterset and collation to UTF-8?

Example:

mysql> ALTER TABLE arabic CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE arabic;
+--------+---------------------------------------------------------------+
| Table  | Create Table                                                  |
+--------+---------------------------------------------------------------+
| arabic | CREATE TABLE `arabic` (
  `words` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------+
1 row in set (0.00 sec)