3

i hav a database that contains spanish characters. to populate the database i am getting the values from client page which has character encoding=UTF-8. when i insert the values in mySql database the rows contain altered data. for example if i insert 'México', the entry in the database is 'México'. the impact this has is when i do a query on the table specifying 'México', i get no results. my question is how do u insert spanish or other latin accent in mysql database? i hav tried all the collations, htmlentities() etc but nothing works!! when making mysql query i checked what data is being sent and it is in its correct form 'México' but when i see the table entry through phpmyadmin, its altered!!

Gumbo
  • 643,351
  • 109
  • 780
  • 844
samach
  • 3,244
  • 10
  • 42
  • 54
  • Your table’s character encoding is probably not UTF-8. – Gumbo Aug 22 '11 at 16:39
  • my tables collation is utf8_general_ci...is this the same? i mean character encoding and collation is the same thing? – samach Aug 22 '11 at 16:42
  • [Collation and character encoding are different things.](http://dev.mysql.com/doc/refman/5.0/en/charset-general.html) – Gumbo Aug 22 '11 at 16:43
  • thanks for the link...hw can i set my tables character encoding? – samach Aug 22 '11 at 16:47
  • https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored explains how you can get Mojibake. – Rick James Sep 01 '17 at 23:09

3 Answers3

6

Change your mysql database/ table / column encoding to UTF-8 (and also set the collation to a compatible value).

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE mytable 
MODIFY country CHAR(50) 
CHARACTER SET utf8 COLLATE utf8_general_ci;

Also specify the char set at the PHP side when connecting. 

mysql_set_charset('utf8',$conn);

Take a look at this article for further info and a script to batch change every table / column in a database.

Anthony Accioly
  • 21,918
  • 9
  • 70
  • 118
  • Anthony i cant query my database after inserting items in database...luks like the value doesnt matches the one i provide and the one in database!! i i also post this question in this form...dont knw hw to post the link – samach Aug 23 '11 at 14:20
  • Salman, are you setting the utf8 encoding in the PHP side also when querying? (Second block of code). You must always use the same encoding (in this case utf8). If you insert using utf8 and query using the default charsert (probably ISO-8859-1) things will not work. Take a look at [this page](http://php.net/manual/en/function.mysql-set-charset.php) from the PHP manual. – Anthony Accioly Aug 23 '11 at 16:34
  • not working :/ also, do you have a PDO version of the PHP command? – oldboy Oct 02 '17 at 00:06
2

I just wasted 4 hours on the same problem as you.

Everything is in UTF-8

EG: 

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

The MySQL database is in UTF-8 PHP is in UTF-8

After four hours and on many occasions tearing my hair out I have found a solution that works. 

$content = htmlentities($row[mystring], ENT_QUOTES, "ISO-8859-1");
$content = html_entity_decode($content);

It takes the accents converts them to html characters then converts them back into UTF-8

This is a great hack and it works perfectly.

For some inexplicable reason, the data in my MYSQL database is not in the UTF-8 format even though I have gone to extreme measures like exporting the data to a text file in phpmyadmin saving it as UTF-8 in a text editor and re-importing it. None of this worked.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
John Coltrane
  • 111
  • 1
  • 1
  • 6
0

Check two things first:

  • Are you inserting the data as UTF-8? If the data is coming from a web page, make sure the page's encoding is set to UTF-8 (encoding meta tag is set in the page header).
  • Are you sure the data is not saved as Unicode? This is the reverse situation: if phpMyAdmin uses something else other than UTF-8, you'd see the garbled characters when it displays the contents.
JJJ
  • 32,902
  • 20
  • 89
  • 102
  • before inserting i echo the data and its in the desired form...sorry i dint get ur second point...hw do i check if the data is saved as unicode or nt? – samach Aug 22 '11 at 16:45
  • Echoing the data doesn't necessarily tell you anything if the page's encoding is wrong. – JJJ Aug 22 '11 at 16:46