how to get foreign characters to display in basic datatables

how to get foreign characters to display in basic datatables

mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5
  1. 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();

This question has an accepted answers - jump to answer

Answers

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5
    edited 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"]], } ); } );
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5

    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?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5

    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();
  • PapaLazarouPapaLazarou Posts: 7Questions: 1Answers: 0

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

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5

    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

  • PapaLazarouPapaLazarou Posts: 7Questions: 1Answers: 0
    edited 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:

    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/charsets/

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5

    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(
  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5
    edited 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/
  • PapaLazarouPapaLazarou Posts: 7Questions: 1Answers: 0

    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.

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5
    1. using SQLyog I see the accented characters,
    2. 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";
    ?>
    {
      "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}
    ]
    }
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • mkleinoskymkleinosky Posts: 46Questions: 6Answers: 5
    Answer ✓

    Yay! got it to work ...

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

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

    2. "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!!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Excellent - good to hear!

This discussion has been closed.