2

I'm trying to insert text into a MySQL DataBase from a form using PHP.
Problem is, when I check the DataBase in phpmyadmin, I find that special characters like ü, ö, ä, è, é, à... are being corrupted, and I get things like üöäéèÃ.I've tried the following:

  • Putting accept-charset="latin1_swedish_ci" in the <form> tag
  • Setting default_charset = "latin1_swedish_ci" in the php.ini file
  • Putting mysqli_set_charset($dbc, 'latin1_swedish_ci'); and mysql_query('SET NAMES latin1_swedish_ci'); after the mysqli_connect() function
  • Setting the collation of the DataBase, the table and the individual columns to latin1_swedish_ci

I noticed that running the query directly in the phpmyadmin the values weren't corrupted, so I suppose the error must be somewere in the PHP and/or the MySQL connection.
Also, I tried doing the same with utf-8_general_ci, and obtained same results. It seems to be ignoring anything I do.
Advice and ideas welcome. Thanks guys,
Sean

Improve this question
5
  • What is the character encoding of the page containing the form ? Commented Jun 27, 2011 at 12:32
  • accept-charset="latin1_swedish_ci" is no valid HTML. Please use a fitting charset, see HTML docs. You might be looking for latin1, list of all registered charsets. Commented Jun 27, 2011 at 12:36
  • And your question looks like that you don't know how MySQL handles charsets, so read about that here. Commented Jun 27, 2011 at 12:37
  • There is a confusion on the scenario stated above. First part of above you said - I check the DataBase in phpmyadmin, I find that special characters like ü, ö, ä, è, é, à... are being corrupted, and I get things like üöäéèà AND then you said - I noticed that running the query directly in the phpmyadmin the values weren't corrupted. Please make sure are you found data corrupted in PHPMYADMIN? Commented Jun 27, 2011 at 12:41
  • Thank you for your reply. Well, the corruption only happens when I try to insert records using my PHP script, not when I run the query directly in phpmyadmin. Commented Jun 27, 2011 at 12:51

6 Answers 6

Reset to default
4

Not sure if you tried this or not... but I had kind of the same problem while inserting Hebrew into a MySQL database. I did the following: Database tables are collation is: utf8_unicode_ci

In PHP I do this:

@ $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
$db->set_charset('utf8');

I create a new connection and than I specify the connetion as UTF-8.

This solved my problem when dealing with Hebrew...

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

OP is looking for latin1 encoding, not UTF-8.
I do it like this: $dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() ); mysqli_set_charset($dbc, 'latin1_swedish_ci'); mysql_query('SET NAMES latin1_swedish_ci');
Well.... I did it with UTF-8 it doesn't mean that the exact same thing can't be done with latin1_swedish_ci - Next time I will make sure to include the exact encoding asked for in the question.
The way your doing it, it should be: ` mysql_set_charset('latin1_swedish_ci', $dbc); `
2

I can not properly say if that is the cause of your problem, but in any case you can not put a Mysql specifier of charset and collation neither into the HTML form nor into PHP.ini.

Right now you use the MySQL value somewhat blindly and put it everywhere you assume it fit - without even knowing. Don't do that. First of all decide which encoding you want to use and accept. From your question I would say that is latin1.

So configure HTML and PHP appropriately:

<form accept-charset="latin1">

For the HTML form element. Note that there is no latin1_swedish_ci in HTML, see the HTML docs.

A next and similar point is in your php.ini setting. There doesn't exists something like you put there. Change it to latin1 as well:

default_charset = "latin1"

With these two settings corrected, the browser is aware what the encoding of the page is that contains the form, and the form even signals the browser the accepted encoding of the data passed back to the server.

So this should ensure that the data you have inside the PHP variables is latin1 encoded. You can then pass this data into your database if you properly configured the encoding of the database client, the database server, the encoding of the connection between the two and naturally the encoding of the data in the database as well.

However, the proper setup of the form is the very first step you need to do before you can ensure that the mysql connection isn't wrangling stuff up.

Improve this answer

1 Comment

Thank you very much for taking the time to reply! Much appreciated. Thanks to your advice I managed to fix the problem, since I did not set the PHP variables as 'latin1' but as 'latin1_swedish_ci', which as you point out is incorrect in HTML and won't work in the php.ini file. Thanks and have a good day!
0

Make sure the collation and the MYSQL connection collation are both latin1_swedish_ci

Improve this answer

2 Comments

I don't think collation would have anything to do with it.
I had the same problem and it was solved by changing the collation of the database, don't know, maybe I was lucky :)
0

I think you should set character enconding of the page to:

<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
Improve this answer

Comments

0

For those Hebrew users:

HTML PAGE:

<html lang="he">
  <head>
    <meta charset="utf-8">

PHP FORM

header('Content-Type: text/html; charset=UTF-8');
$con = mysqli_connect("HOST","DB_USER","PASSWORD","DB_NAME");
$con->set_charset('utf8');

DB, TABLE, FEILDs COLLATION

utf8_general_ci

Improve this answer

Comments

-1

You may user 

mysql_query("set character_set_results='utf8'");

before executing MySQL query

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.