how to get foreign characters to display in basic datatables

mkleinosky · April 2015

This code works: for example Galapagos shows with foreign character "Galápagos "

<!DOCTYPE html><html><head> <title>test foreign chars</title></head><body>
<?php  
$dbpassword="***********";$dbdatabase="*************";$dbhost="localhost";
$conn= new mysqli ($dbhost,$dbuser,$dbpassword);$conn->select_db($dbdatabase);
$s="Select * from whs order by whssite";
$Q = $conn->query($s);
while($row = $Q->fetch_assoc())
{ $whssite=$row["whssite"];?><?=$whssite?><br><? } ?>

see http://mosttraveledpeople.com/testwhs1.php - all whssites are listed with no special coding of page

this basic ajax foron DT does not show any with foreign characters whssite for Galápagos come back "whssite":null,

{"DT_RowId":"row_4","whsid":"1","whssite":null,"whsactive":"Y","whstype":"","whslongitude":"-91","whslatitude":"0.82","lat":"-0.81667","longt":"-91","whsrank":"122","whsvisitors":"344","whsregion":"LAC","whsregionname":"Americas","whscountry":"EC","whscountryname":"Ecuador"},

ajax code:

<?php ? ** what needs to go in here to force it to recognize foreign characters instead of returning "null" - (I do no special code in page above to get it to work) ?>


<?php

/*
 * Editor server script for DB table masterlist
 * Created by http://editor.datatables.net/generator
 */ 

// DataTables PHP library and database connection
include( "./php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'whs', 'whskey' )
    ->fields(
 Field::inst( 'whsid' ),        
 Field::inst( 'whssite' ),
 Field::inst( 'whsactive'  ),
 Field::inst( 'whstype' ) ,
 Field::inst( 'whslongitude' ) ,
 Field::inst( 'whslatitude' ) , 
 Field::inst( 'lat' ),  
 Field::inst( 'longt' ),
 Field::inst( 'whsrank' ), 
 Field::inst( 'whsvisitors' ),  
 Field::inst( 'whsregion' ),
 Field::inst( 'whsregionname' ),
 Field::inst( 'whscountry' ),
 Field::inst( 'whscountryname' )   
    )
    ->process( $_POST )
    ->json();

mkleinosky · April 2015

This contentType did not help ...


$(document).ready(function() {

 $('#whs').DataTable( {
 //  "dom": "Tfrtip",
 "dom": '<"wrapper"Tf<l><"clear"><i><p><t>',
   "ajax": {
    "url": "dteditor/dte_whs1.php",
    "contentType": "application/json; charset=utf-8"
    },
  "columns": 
 [
  {"data": "whsid" },       
  {"data": "whssite" } , 
  {"data": "whstype" }  
 ],
 
  "paging": true,
  "pagingType": "full_numbers",
  "order": [ 1, "asc" ],
  'bAutoWidth': false , 
  "lengthMenu": [[  100,   -1], [ 100, "All"]], 
  } );
} );

allan · April 2015

Sounds like your database might not have stored the data in UTF8, or possibly your PHP/MySQL socket is not UTF8.

Try running the following SQL commands immediately after making the connection:

SET character_set_client=utf8;
SET character_set_connection=utf8;
SET character_set_results=utf8;

Allan

mkleinosky · April 2015

thanks, I have this page showing the data correctly: (it is a bit f a mess but proof of utf8 data coming back):
http://mosttraveledpeople.com/dteditor/testwhs1.php

data returned:


{ "data": [{ "whsid": "1", "whssite": "Galápagos Islands", "whsregion": "LAC", "whscountry": "EC", "whstype": "","x":null},{ "whsid": "2", "whssite": "City of Quito", "whsregion": "LAC", "whscountry": "EC ", "whstype": "C","x":null},{ "whsid": "999", "whssite": "z", "whsregion": "z", "whscountry": "", "whstype": "z","x":null} ] }

````

the code has no special utf-8 conversion

require_once '../_php_dbparms.php' ;

$conn= new mysqli ($dbhost,$dbuser,$dbpassword);
$conn->select_db($dbdatabase);
$s="Select whsid, whssite, whsregion, whscountry, whstype from whs where whsactive = 'Y' order by whsid limit 2";
 $ar=array();?> 
{
  "data": [<? 
$Q = $conn->query($s);
while ($r = mysqli_fetch_array($Q,MYSQLI_ASSOC)) {
 echo "{";
 foreach ($r as $k=>$v) {?>
 "<?=$k?>": "<?=$v?>",<? 
 } 
 echo '"x":null},';
}?>
{ "whsid": "999", "whssite": "z", "whsregion": "z", "whscountry": "", "whstype": "z","x":null}
]}

```

and the data it shows is ok ... so I am sure the db is utf8 - I am usin mysqli - I wonder if PDO is the issue?

allan · April 2015

I doubt it since you aren't using PDO.

However, it might be best to ask on StackOverflow or some other general programming site.

Allan

mkleinosky · April 2015

my calls failed - is there a corrected call syntax you can give me for setting utf8 in Ajax file please?

(Fatal error: Uncaught exception 'Exception' with message 'Unknown database command or not supported: set character_set_client=utf8' in /home/content/67/4410967/html/dteditor/php/Database/Query.php:256 Stack trace: #0 /home/content/67/4410967/html/dteditor/dte_whs1.php(18): DataTables\Database\Query->exec() #1 {main} thrown in /home/content/67/4410967/html/dteditor/php/Database/Query.php on line 256)

on $db->query("SET character_set_results=utf8")->exec();


use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;
 $db->query("SET character_set_client=utf8")->exec();
 $db->query("SET character_set_connection=utf8")->exec();
 $db->query("SET character_set_results=utf8")->exec();
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'whs', 'whskey' )
    ->fields(
 Field::inst( 'whsid' ),        
 Field::inst( 'whsactive'  ),
 Field::inst( 'whstype' ) ,
 Field::inst( 'whslongitude' ) ,
 Field::inst( 'whslatitude' ) , 
 Field::inst( 'lat' ),  
 Field::inst( 'longt' ),
 Field::inst( 'whsrank' ), 
 Field::inst( 'whsvisitors' ),  
 Field::inst( 'whsregion' ),
 Field::inst( 'whsregionname' ),
 Field::inst( 'whscountry' ),
 Field::inst( 'whscountryname' ),   
 Field::inst( 'whssite' )
    )
    ->process( $_POST )
    ->json();

PapaLazarou · April 2015

Try
SET NAMES 'utf8'
It does all three in one go.

mkleinosky · April 2015

Thanks
changing it to

$db->sql( "SET NAMES 'utf8'" );

go data instead of ,"whssite":null},

I now get ...

,"whssite":"Gal\u00e1pagos Islands"},

so that is progress ... now looking how to handle all foreign characters presented like that

PapaLazarou · April 2015

Also what does this produce in an SQL client :

SHOW VARIABLES LIKE 'character_set%'; 
SHOW VARIABLES LIKE 'collation%';

Here's what my local server says:

mkleinosky · April 2015

I even tried changing Query.php in/Datatbase


        try {
            $pdo = @new PDO(
//              "mysql:host={$host};{$port}dbname={$db}".self::dsnPostfix( $dsn ),
                "mysql:host={$host};charset=utf8;{$port}dbname={$db}".self::dsnPostfix( $dsn ),             
                $user,
                $pass,
                array(

mkleinosky · April 2015

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

shows exactly the same as yours except last line:


    character_set_client | utf8
    character_set_connection | utf8
    character_set_database | utf8
    character_set_filesystem | binary
    character_set_results | utf8
    character_set_server | utf8
    character_set_system | utf8
    character_sets_dir | /usr/share/mysql-5.0-96/share/mysql/charsets/

PapaLazarou · April 2015

See if when looking at your Data using a SQL software you can see the correct values with accents as you entered them. If you can see it stored with accents etc then your database is correct and there's an encoding issue.

Im no expert in this im having a similar encoding issue with UTF8 as you except when I do issue SET NAMES utf8 I get null back. Because I dont have root access on my shared server and the encoding is latin1.

mkleinosky · April 2015

using SQLyog I see the accented characters,
running a plain php mysqli query - I can echo the fetch coluumn and display the accented characters
(see http://mosttraveledpeople.com/dteditor/testwhs1.php)
which outputs:

{ "data": [{ "whsid": "1", "whssite": "Galápagos Islands", "whsregion": "LAC", "whscountry": "EC", "whstype": "","x":null},{ "whsid": "2", "whssite": "City of Quito", "whsregion": "LAC", "whscountry": "EC ", "whstype": "C","x":null},{ "whsid": "999", "whssite": "z", "whsregion": "z", "whscountry": "", "whstype": "z","x":null} ] }

code:

    $conn= new mysqli ($dbhost,$dbuser,$dbpassword);
$conn->select_db($dbdatabase);
$s="Select whsid, whssite, whsregion, whscountry, whstype from whs where whsactive = 'Y' order by whsid limit 2";

<?php
>
{
  "data": [<?
$Q = $conn->query($s);
while ($r = mysqli_fetch_array($Q,MYSQLI_ASSOC)) {
 echo "{";
 foreach ($r as $k=>$v) {?>
 "<?=$k?>": "<?=$v?>",<? 
 } 
 echo '"x":null},';
}?>
{ "whsid": "999", "whssite": "z", "whsregion": "z", "whscountry": "", "whstype": "z","x":null}
]
}
?>

allan · April 2015

Try removing the charset=utf8 from the DSN and the config.php file's dsn option in case that is causing the issue (double encoding perhaps?).

Might also be worth loading the Editor Ajax feed directly in your browser (like the link you gave for the mysqli feed) and see if the data is correctly presented there.

If you could give a link to the page you are working on that might also be useful.

Thanks,
Allan

mkleinosky · April 2015

Yay! got it to work ...

i tried all combinations of things - doing these 2 seemed to be the thing:

$db->sql( "SET NAMES 'utf8'" );
"Try removing the charset=utf8 from the DSN and the config.php file's dsn option in case that is causing the issue (double encoding perhaps?)."

Now my own Mysqli pages that read the data I use
$conn->query( "SET NAMES 'utf8'" );

and for DT/Editor I use 1. above

Thanks - this system can do anything!!

allan · April 2015

Excellent - good to hear!

how to get foreign characters to display in basic datatables

how to get foreign characters to display in basic datatables

Answers

Howdy, Stranger!

Categories

DataTables

how to get foreign characters to display in basic datatables

how to get foreign characters to display in basic datatables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables