0

I have a mysql table with 70K+ data which is utf8 encoded.

The data looks good on browser but on sql tools it looks like gibberish पवठ. Therefore I decided to convert this table data into latin1 encoding and save it into another table.

But I am unable to write function to do it. Please help. This problem has been haunting me for very long now.

My Specs: 

PHP - 5.6.30
MYSQL - 5.7.22
Dean Ambrose
  • 183
  • 10
  • I think you should read up on [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Naruto Jun 05 '18 at 08:46
  • @Naruto I have read this question. I know UTF-8 is good for all purpose and I will continue to use it for operations in my application but I want the data in mysql table to be in readable format as well. That's why I want to convert it into a latin1 – Dean Ambrose Jun 05 '18 at 08:48
  • 1
    Can I suggest you configure your tools to work with UTF8 encoding or replace your tools with ones that do. What you are suggesting make no sense, and will generate you a lot of new Gibberish as UTF8 will not directly convert to latin1 without loosing lots of the utf8 data – RiggsFolly Jun 05 '18 at 08:50
  • 1
    Well you must do something wrong, because I have no problems at all with using UTF-8 all the way, and yes my data is readable in the database. – Naruto Jun 05 '18 at 08:51
  • Which tools are causing you problems? – RiggsFolly Jun 05 '18 at 08:51
  • @Naruto can I show you some of my code. and you can help me from there. – Dean Ambrose Jun 05 '18 at 08:51
  • You should look at the preferences of your "sql tool". You don't want to change the encoding of the table to make the text readable in different contexts. – vijoc Jun 05 '18 at 08:52
  • @RiggsFolly I am using php + mysql + phpmyadmin – Dean Ambrose Jun 05 '18 at 08:52
  • 1
    What versions of these? All those (at current verions) should deal with UTF8 quite happily – RiggsFolly Jun 05 '18 at 08:54
  • php - 5.6.30 mysql -5.7.22 – Dean Ambrose Jun 05 '18 at 08:55
  • And phpMyAdmin? Is that a reasonably current version. I assume it is this you are mainly talking about when you say issues – RiggsFolly Jun 05 '18 at 08:57
  • @RiggsFolly I forgot to state that but I the data I am talking about is in hindi language not english.... maybe this is causing some issue. – Dean Ambrose Jun 05 '18 at 09:03
  • @DeanAmbrose — Latin 1 isn't capable of expressing hindi characters! You need ISO-8859-12 for that … and that spec was never finished. – Quentin Jun 05 '18 at 09:04
  • @Quentin I created a latin1 charset table and saved hindi data in it from google translate and it worked fine. – Dean Ambrose Jun 05 '18 at 09:06
  • @Quentin can I express hindi characters with utf8? – Dean Ambrose Jun 05 '18 at 09:06
  • 1
    If it's storing "पवà¤", then it's *not* "working fine". See the duplicate. – deceze Jun 05 '18 at 09:06
  • @DeanAmbrose — MySQL charsets are *weird*. I suspect that while you might have labeled the table as being latin1, the data you were putting in it wasn't latin1. – Quentin Jun 05 '18 at 09:07
  • @DeanAmbrose — UTF-8 supports *everything* (more or less, the lack of support for Klingon is a sore point in some circles). – Quentin Jun 05 '18 at 09:07
  • @Quentin Meh… http://www.evertype.com/standards/csur/klingon.html – deceze Jun 05 '18 at 09:08
  • @Quentin so if my hindi data is saving as "पवà¤" that means there is something wrong with my current settings?? – Dean Ambrose Jun 05 '18 at 09:09
  • because apart from their gibberish look in mysql the data looks good while performing other operations such as filtering comparision sorting etc. – Dean Ambrose Jun 05 '18 at 09:10
  • Read [Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/). – deceze Jun 05 '18 at 09:11
  • @deceze thanks for the article... I guess what this article is trying to say is that converting to different encoding will lead to data loss and inefficiency in the back without the user knowing ....am I right? – Dean Ambrose Jun 05 '18 at 09:16
  • It says that if you get the encoding settings wrong somewhere in the chain, then you're working with [mojibake](https://en.wikipedia.org/wiki/Mojibake), which is never a good thing. – deceze Jun 05 '18 at 09:19
  • @deceze In my case I have set the db table charset to be `UTF8` and encoding to `utf8_unicode_ci`. I have set `mysqli_set_charset($link, "UTF-8");` in my config file. My web form is accepting hindi data. So based on this can you suggest me something if I am doing anything wrong. **please this would be very very helpful for me.** – Dean Ambrose Jun 05 '18 at 09:23
  • The encoding is called `utf8` in MySQL, or preferably even ***`utf8mb4`***. – deceze Jun 05 '18 at 09:25
  • you mean instead of `utf8_unicode_ci`? – Dean Ambrose Jun 05 '18 at 09:28
  • `mysqli_set_charset($link, 'utf8mb4')` – deceze Jun 05 '18 at 09:28
  • okay so after using mysqli_set_charset($link, 'utf8mb4') my data is coming fine now.... but is there any way to recover my previous data. – Dean Ambrose Jun 05 '18 at 09:34
  • Read. [The. Duplicate.](https://stackoverflow.com/a/20237671/476) – deceze Jun 05 '18 at 09:35
  • SELECT CONVERT(BINARY CONVERT(field_name USING latin1) USING utf8) FROM table_name are you talking about this. – Dean Ambrose Jun 05 '18 at 09:35
  • i think I got it.....since I was using wrong encoding 'UTF-8' instead of "utf8" my data got saved into latin1 format by defult. And now just need to convert it inti utf8 using the function in your answer. – Dean Ambrose Jun 05 '18 at 10:04
  • thanks @deceze for your time and incredible help. – Dean Ambrose Jun 05 '18 at 10:12

0 Answers0