CODEWITHSUNDEEP
phphtmlmysqlunicode
tishantha
tishantha

Reputation: 497

How to take unicode characters from the mysql data base in PHP

I am developing a local website which gives information in local language. In my MySQL database , there is a table called pageContent and it has a column called "text". that "text" column type is text and collation is utf8_sinhala_ci

enter image description here

In MySQL it displays the fonts normally:

enter image description here

But when I take it in to PHP page and echo the value, then it shows question marks like ??????????.

enter image description here

But if I hard code something and echo, then it displays normally in PHP page also.

enter image description here

I feel something goes wrong when I take values from the database. But I couldn’t found the error. I have tried following codes also.

mysql_query ("set collation_connection='utf8_sinhala_ci'"); 
  $result =  mysqli_query($this->connecDB,"SELECT * FROM pageContent");

But that doesn’t work.

Upvotes: 4

Views: 1770

Answers (3)

AppEmmanuel
AppEmmanuel

Reputation: 142

So in my case, I had tried changing the collation from utf8mb4_unicode_ci for mysql and had to change it to uft8_general_ci.

Then pasted : 

mysqli_set_charset( $con, 'utf8');

right before I did the SELECT command.

This is my code for reading from db :

/*

$DB_SERVER="db_server_name";
$DB_USER_READER="root";
$DB_PASS_READER="passw*rd";
$DB_NAME="db_name";
$DB_PORT="port number";

$SELECT_WHAT="`name_of_column_as_in_your_table`";
$WHICH_TBL="`table_name`";
$ON_WHAT_CONDITION="`id`='7'";

*/


$con = mysqli_connect($DB_SERVER, $DB_USER_READER, $DB_PASS_READER, $DB_NAME, $DB_PORT);//this is the unique connection for the selection

    mysqli_set_charset( $con, 'utf8');


        $slct_stmnt = "SELECT ".$SELECT_WHAT." FROM ".$WHICH_TBL." WHERE ".$ON_WHAT_CONDITION;

    $slct_query = mysqli_query($con, $slct_stmnt);

        if ($slct_query==true) {
//Do your stuff here . . . 
}

And it worked like a charm. All the best. The above code can work with reading chineese, russian or arabic or any international language from the mysql database table column holding such data.

Upvotes: 0

Giacomo1968
Giacomo1968

Reputation: 26014

I feel something goes wrong when I take values from the database. But i couldn't found the error. I have tried following codes also.

You need to make sure your entire chain from the connection, to the database, to the tables is all UTF8 clean. I have a detailed answer to a similar question here.

But in your case, check the actual MySQL server my.cnf file. The following would set the whole chain to UTF-8:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

More immediately, look at your code:

mysql_query("set collation_connection='utf8_sinhala_ci'"); 
$result = mysqli_query($this->connecDB,"SELECT * FROM pageContent");

In one line you are calling mysql_query and the next you are calling mysqli_query. But these are conflicting methods that should not be used together. It should simply be mysqli_query like so:

mysqli_query($this->connecDB, "SET collation_connection='utf8_sinhala_ci'"); 
$result = mysqli_query($this->connecDB,"SELECT * FROM pageContent");

Note how I am setting mysqli_query($this->connecDB,… before your SET collation_connection='utf8_sinhala_ci'. That will send the query on the same connection you are using for $result. Or perhaps you can try this instead:

mysqli_query($this->connecDB, "SET NAMES 'utf8'");
$result =  mysqli_query($this->connecDB, "SELECT * FROM pageContent");

Upvotes: 3

luttkens
luttkens

Reputation: 1264

Make sure that your PHP-file it self is using the same encoding as your database (UTF8). That have caused me errors in the passed.

If that doesn't help, an ugly solution would be to convert the output with utf8_decode().

Upvotes: 1

Related Questions