4

I am having problems making mydatabase table show Latin caracters. (ã,õ,á,é,í,…). I am using WAMP server with Apache 2.4.9 and MySQL 5.6.17.

I have a simple form that I created in HTML and I have a PHP code witch runs a query and creates the account. Here is the PHP code:

include('config.php'); //My database data for connection

if(isset($_POST['submit'])) {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];

    $query = "INSERT INTO account SET username = '".$username."', password = PASSWORD('".$password."'), email = '".$email."'";
    $execute_query = mysqli_query($connection, $query);
    if($execute_query) { 
        //Creates the account
    }
    else { 
        //If an error occures
    }
}

Now for example, I create an account with:

Username: Luís
Password: 1234
E-mail: somemail@mail.com

When I go check the Table it shows the account created but the username shows LuÃs instead of Luís.

Things I have tried:

Creating a DataBase and Table with:
utf8_general_ci
utf8_unicode_ci
utf8mb4_general_ci
utf8mb4_unicode_ci
latin1_swedish_ci

None of them worked, it always shows that à instead of í.

3

I have fixed my problem with the query "SET NAMES utf8".

include('config.php'); //My Database data for connection.
//Connection set with $connection = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db)

if(isset($_POST['submit'])) {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];

    $connection->query("SET NAMES utf8"); //FIXED (Gets $connection from config.php and runs the query "SET NAMES utf8")
    $query = "INSERT INTO account SET username = '".$username."', password = PASSWORD('".$password."'), email = '".$email."'";
    $execute_query = mysqli_query($connection, $query);
    if($execute_query) { 
        //Creates the account
    }
    else { 
        //If an error occures
    }
}

Thank you @JakeGould for trying to help me, after all I had no problems with my database table. My database and table were set with CHARSET=utf8 and COLLATE=utf8_unicode_ci.

0

Diagnosing the root of the issue.

I am having problems making mydatabase table show Latin caracters. (ã,õ,á,é,í,…)

This has all of the signs of an encoding mismatch between your HTML form data, the PHP file and the MySQL. My guess is the issue is your HTML form and the PHP file are sending UTF8 data—since UTF8 is the default encoding method nowadays—to the MySQL database which is setup (by default) to use latin1_swedish_ci encoding.

So this is one of those “six of one, half a dozen of another” issues where you could theoretically change the encoding in the HTML and PHP file to be latin1_swedish_ci. But that is pretty regressive in today’s Internet world and the world of web development.

So I would recommend making sure your whole data chain—from the HTML form to the PHP to the MySQL database—is all UTF8. Once you get everything in UTF8, life is tons easier for tons of reasons.

As for why you are seeing this issue is because (by default) most MySQL installs set latin1_swedish_ci instead of utf8_general_ci for newly created databases. So you’re on the right track in your attempts to fix that.

But there is more than one thing you need to do to get a MySQL database—and datives server—set to properly handle character encoding and—in this case—UTF8 encoding.

Steps you can take to resolve the issue.

First, change the collation of the database and try again.

ALTER DATABASE [name of your database] CHARACTER SET utf8;

If this is a specific table, the collation can be changed as so:

ALTER TABLE [name of your table] CONVERT TO CHARACTER SET utf8;

And if it is a specific column in a table:

ALTER TABLE [name of your table] MODIFY [name of your column] [other settings] CHARACTER SET utf8 COLLATE utf8_general_ci;

Or perhaps you could export the current database, create a new database with this command & reimport the data:

CREATE DATABASE [name of your database] CHARACTER SET utf8 COLLATE utf8_general_ci;

And if you want to make a permanent change to the MySQL install on the machine giving you an issue, go and edit my.cnf. 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
5
  • 1
    latin1_swedish_ci ??? why did they choose a Swedish code set for the default?
    – DavidPostill
    Sep 25 '15 at 17:25
  • @DavidPostill Of course Stack Overflow answers that. “The bloke who wrote it was co-head of a Swedish company.” Sep 25 '15 at 17:27
  • Nothing worked. I ran the queries and changed the my.cnf but nothing. Maybe it has something to do with the query i am runing to create the account? Sep 25 '15 at 17:39
  • @JoãoLuís Did you restart the MySQL server as well? And what about Apache? What version of Apache are you using? Sep 25 '15 at 17:41
  • @JakeGould Thanks for the trivia lesson ;) However, according to the link "Great to see that after what, 20 years? they changed this into a sane default, like utf8_general_ci. Good job, MySQL"
    – DavidPostill
    Sep 25 '15 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.