How to fix issue regarding character encoding with DataTable?

Question

I often follow your suggestions on this forum. Now I have an issue with Datatable plugin. On my website, I have created a food table. This table takes all data from mysql database and use DataTable to have some additional plugins. The issue is that DataTable doesn't encode correctly some characters showing some "??". I saw that mysql encode is base on latin1 and not on utf8. How can I force the utf8 encoding?

Below the code for database access:

<?php
$link = mysqli_connect ("XXXXXXXX", 
"XXXXX", "XXXXX", "XXXXX"); 

if (mysqli_connect_error()) {

    die ("Problemi di connessione con il server. Riprovare più tardi");

};
?>

Below the code created for the table:

<div style="overflow-x:auto;">

          <table id="tabellaAlimenti" class="table table-striped">
              <thead>
                <tr>
                    <th>Nome</th>
                    <th>Categoria</th>
                    <th>Kcal</th>
                    <th>Carbo (g)</th>
                    <th>Prot (g)</th>
                    <th>Gras (g)</th>
                </tr>
              </thead>

              <tbody>
                  <?php

                    $query = "SELECT * FROM databaseAlimenti";

                    if ($result = mysqli_query($link, $query)) {

                        while($row = mysqli_fetch_array($result)) {

                            echo "<tr>";
                        echo "<td id='nome'><a href='http://wellness4yourself-com.stackstaging.com/alimento/?nome=" .urlencode($row['Nome']). "'>" . $row['Nome'] . "</a></td>";
                            echo "<td>" . $row['Categoria'] . "</td>";
                            echo "<td>" . $row['kcal'] . "</td>";
                            echo "<td>" . $row['Carboidrati (g)'] . "</td>";
                            echo "<td>" . $row['Proteine (g)'] . "</td>";
                            echo "<td>" . $row['Grassi totali (g)'] . "</td>";
                            echo "</tr>";

                        }
                      };
                    ?>
              </tbody>

            </table>

        </div>

 <script type="text/javascript">

          $(document).ready(function() {

              $('#tabellaAlimenti').DataTable( {

                  select: true,
              });


          });


      </script>

Thanks in advance

First, use collation utf8_general_ci for your database When you establish your mysqli connection run a query $link->query("SET NAMES 'utf8'"); Then test and see if your utf-8 characters are stored properly. — Michael Eugene Yuen, Mar 17 '18 at 15:25
I tried what you said but unfortunately Datatable continue to show this issue. If I don't use DataTable, the issue doesn't appear — Pasquale, Mar 17 '18 at 19:52
You need to provide more detail. First, check you database, yout data is fetch directly using php. Have you set in side your section? Are the data in your database already utf8? I don't see any problem with utf8 with database unless you have your data encoded with json — Michael Eugene Yuen, Mar 17 '18 at 19:57

score 0 · Answer 1 · answered Mar 17 '18 at 16:37

0

First of all you need to set correct encoding for your table in mysql:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

Then you need to check encoding header in your server response This header looks like that:

Content-Type: text/html; charset=utf-8

If charset value is incorrect in this header, you can redefine this one with php or via .htaccess(if you using apache) or via server configuration file.

answered Mar 17 '18 at 16:37

SlovyanskiyYehor

456
3
9

Ho provato ma Wordpress mi ha dato un massaggio di avvertimento dicendo che: Warning: Cannot modify header information - headers already sent by (output started at.....) – Pasquale Mar 17 '18 at 19:53
It happens because you added header after content begins to send. You should to add header via .htaccess or you can add it in the index.php file. I think this header will be useful for all pages in your site. – SlovyanskiyYehor Mar 17 '18 at 21:25

How to fix issue regarding character encoding with DataTable?

1 Answers1