4

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.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
tishantha
  • 487
  • 5
  • 13
  • 34

3 Answers3

3

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");
Community
  • 1
  • 1
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
1

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().

luttkens
  • 1,272
  • 8
  • 16
  • The PHP & HTML are fine as indicated by “But if I hard code something and echo, then it displays normally in PHP page also.” The issue is the database connection & collation. – Giacomo1968 May 10 '14 at 18:34
  • 1
    Ok, I reasoned that since the screen shot is from PHPMyAdmin, and it worked, the problem must be somewhere in the PHP-files. Anyways, your answer show a lot more deep knowledge that taught me new stuff :) – luttkens May 10 '14 at 18:51
0

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.

AppEmmanuel
  • 144
  • 1
  • 6