The Correct Encoding with MySQL

Hi all,

I started to work with a database and want to display some data on my web page. I established database connection and now displaying one field of data. The problem is with the encoding. The Collation value for the data field I am displaying is latin1_swedish_ci and I want to display it properly but it doesn't show up as it should. It shows question marks in diamonds for special characters.

Here is my code:

<?php
       
        $db_host = "hostname";
        $db_username = "username";
        $db_pass = "password";
        $db_name = "dbname";
        $db = mysqli_connect("$db_host","$db_username","$db_pass", "$db_name") or die ("could not connect to mysql");
        mysqli_set_charset($db, 'utf-8');
       
        $result = mysqli_query($db, 'SELECT authorname FROM tablename');
        while ($row = mysqli_fetch_array($result))
        {
                $authornames[] = $row['authorname'];
        }
?>
 
<?php foreach ($authornames as $author): ?>
<p><?php echo $author; ?></p>
<?php endforeach; ?>

PHP:

 

You need to convert the data in the database to UTF8. The problem now is that you have UTF8 data stored in a latin1 table/column.

This is a good guide on how to convert. http://paulkortman.com/2009/07/24/mysql-latin1-to-utf8-conversion/

It generally involves dumping all of the data from the database. Converting the database to UTF8. Converting the dumped data to UTF8 and then restoring the dump file.

 

But the same table is used on another page of the site without any problems, all characters are showing properly. It was coded by another person long ago and I couldn't figure out how he did.

 

That is strange then. Are the query's the same on both pages? Also, is there any code in the php that would convert the output to UTF8?

 

I use utf8_unicode_ci and can use different languages and display them fine.

 

UTF8 is the standard encoding for MySQL if you are using English as your website language.