MySQL Insert special characters

Question

I had a registration form which will insert into a member table, the collation is in utf8_general_ci, and I use SET NAMES utf8 in php script, OK here is the problem, I can't insert a string other than pure alphabet, I try input 'Hélène' in a form field, after the sql query run, I check with my db table, the field is inserted as 'H', the rest of the alphabet cannot be insert whenever the string come with special alphabet such as é, ř on behind.

Can someone please help? thanks.

SOLUTION:

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

mysql_query("SET NAMES utf8");

above two line is crucial part to accept input into database and output on a webpage.

Thanks everyone for the advise.

Solution 1

try to insert data after encoding. try mysql_real_escape_string() to encode. then execute insert query.

EDIT:-

This answer was posted one year ago. Now mysql_real_escape_string() for php 5 will work in mysqli::real_escape_string this format. please check here

Solution 2

And if you don't want to worry about so many different charset codings or if htmlentities doesn't work for you, here the alternative: I used mysqli DB connection (and PHPV5) Form post for writing/inserting to MySQl DB.

$Notes = $_POST['Notes']; //can be text input or textarea.

$charset = mysqli_character_set_name($link);  //only works for mysqli DB connection
printf ("To check your character set but not necessary %s\n",$charset);  

$Notes = str_replace('"', '"', $Notes);  //double quotes for mailto: emails.  
$von = array("ä","ö","ü","ß","Ä","Ö","Ü"," ","é");  //to correct double whitepaces as well
$zu  = array("ä","ö","ü","ß","Ä","Ö","Ü"," ","é");  
$Notes = str_replace($von, $zu, $Notes);  
echo " Notes:".$Notes."<br>" ;  
$Notes = mysqli_real_escape_string($link, $Notes); //for recommended mysqli DB connection.
//$Notes = mysql_real_escape_string($Notes); //for old deprecated mysql DB connection.

// mysqli_real_escape_string Escapes special characters in a string for use in an SQL statement

echo " Notes:".$Notes ;  //ready for inserting

Comments

[KarlosFontana]

When displaying the contents: And this line in the header if funny characters appear.<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

[tadman]

mysqli_real_escape_string shouldn't be used when there's a perfectly good placeholder system there.