Accent error in mysql

Asked

Viewed 5,007 times

-1

I’m having accentuation problem in the database of my site.

For example, in the database the text is "electric", but is posted on the site as "theTrical"

I tried some things, but now instead of "accents" gets "?" (interrogation)

I’ve tried several encodings.

mysql asp character-encoding accentuation

  • Your website is being developed in what language?

  • 1

    Gabriel is currently in ASP and soon until December 2018 I will migrate to PHP

3 answers

6

Wear this:

 ALTER DATABASE `sua_base` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

It will only change your database to UTF8, and that does not define anything and does not solve anything, there are multiple types of charset and probably you have to see what is ideal for you, for example the most used are UTF-8 and Latin1 and within these 2 there are variations still in mysql, see more details on:

So first you must be sure which charset is what you want, if it is latin1 or utf8, for example if you want to use latin1 change the tables or the database with ALTER will only change the encoding to something that may already conform to the encoding you want.

The problem of characters

Problems with characters like é or � occurs by bad configuration of the database up to your Asp (or php) scripts, then is not a mistake bank, but rather a misunderstanding of how to work with this.

If you will use UTF8 in the bank you must:

  • Save ALL the scripts as UTF-8 NO GOOD
  • Set the charset during connection to the bank
  • Set the HTTP header in the request response such as Content-Type: text/html; charset=utf8

If you are going to use Latin1 in the bank you must:

  • Save ALL the scripts as ANSI (or ISO-8859-1 or window-1252)
  • Set the charset during connection to the bank
  • Set the HTTP header in the request response, for example Content-Type: text/html; charset=ISO-8859-1 (or Content-Type: text/html; charset=windows-1252)

Attention, for it to work you must follow all the steps:

  • Choose the encoding that will suit you best, latin1 or utf8
  • Set the connection correctly
  • Set http charset with the same desired charset
  • Save all documents/script without exception that will use database data with the same encoding

Configuring UTF-8

You must save all ASP scripts (up to the ones you will use with <!--#include file="..."-->) in utf-8 without "GOOD", you can use software like Sublimetext or Notepad++ to convert the files:

  • Using Notepad++:

    utf8 sem bom notepad++

  • Using Sublime Text:

    utf8 sublime sublimetext

Then you must set on the connection to use UTF8 using the command SET CHARACTER SET utf8:

If already configured in the file my.ini this may be expendable, but I would keep to avoid problems if someone edit the my.ini

Dim cnnSimple  ' ADO connection
Dim rstSimple  ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")     

cnnSimple.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=[mySQL server];
DATABASE=[DBName];UID=[DBUser];PASSWORD=[DB Password];"

Set rstSimple = cnnSimple.Execute("SET CHARACTER SET utf8") 'Define o charset

Setting up latin1

First you must save "all scripts. Asp" as iso-8859-1 (or ANSI):

  • To save using Notepad++:

    salvando documento em ANSI com notepad++

  • To save using Sublimetext:

    salvando documento em iso-8859-1 ou windows 1252

Then you must set on the connection to use UTF8 using the command SET CHARACTER SET latin1:

If already configured in the file my.ini this may be expendable, but I would keep to avoid problems if someone edit the my.ini

Dim cnnSimple  ' ADO connection
Dim rstSimple  ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")     

cnnSimple.Open "DRIVER={MySQL ODBC 5.3 Driver};SERVER=[mySQL server];
DATABASE=[DBName];UID=[DBUser];PASSWORD=[DB Password];"

Set rstSimple = cnnSimple.Execute("SET CHARACTER SET latin1") 'Define o charset

Setting the HTTP charset

You can do this in two ways, via HTML or via response, follow:

  • Using UTF-8 in HTML

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

  • Using UTF-8 on response that should go before any HTML content:

    <%@Language=VBScript CodePage = 65001%>
<%
Response.charset ="utf-8"
Response.CodePage = 65001
%>
<html>
...

  • Using latin1 (iso-8859-1 or windows-1252) in HTML

    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

  • Using UTF-8 on response that should go before any HTML content:

    <%@Language=VBScript CodePage = 65001%>
<%
Response.charset ="iso-8859-1" 'pode usar windows-1252 também
Response.CodePage = 28591
%>
<html>
...

Completion

If you have followed all of the steps correctly there will probably be no more failures, neither in the reading of the database nor in the recording of the data, all that was said in this answer is the same that I have explained in:

  • I appreciate the attention of all I’ve made the corrections of all Now just need to correct the legacy data included erroneously But the new ones are all right! Thanks SAME About <%@Language=Vbscript Codepage = 65001%> <% Response.charset ="iso-8859-1" 'can use windows-1252 also Response.Codepage = 28591 %> It was not accepted by the server hosting the site but is worth because now everything is practically right !!!

  • @user2832991 thanks for the feedback, could you mark the answer as correct? If you do not know how this is done read this: https://pt.meta.stackoverflow.com/q/1078/3635

-1

Use the following command in the tables that are giving problem with accentuation.

 ALTER TABLE `sua_tabela` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

This is because the Charset set set for your Mysql does not have this accentuation type. With the above command you will use the Latin charset, which has these characters.

Or you can also change the entire database with the following command

ALTER DATABASE `sua_base` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

  • I appreciate the attention of all I’ve made the corrections of all Now just need to correct the legacy data included erroneously But the new ones are all right! THANK YOU VERY MUCH

-2


All tables are with DEFAULT CHARSET=utf8 but no COLLATE set, as provided in the dump (example below).

CREATE TABLE `adsense` (
  `id` int(11) NOT NULL DEFAULT '0',
  `codigo` mediumtext NOT NULL,
  `canal` varchar(500) NOT NULL,
  `local` varchar(10) NOT NULL,
  `publicado` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Take a backup of the database and remove everything from the current BD, then run the following command:

ALTER DATABASE `ConteudoAnimal` CHARSET = UTF8 COLLATE = utf8_general_ci;

After that do the process again of creating the tables and importing the data. Several data that are in the current export are already "broken" because they were inserted with the wrong encoding, therefore they will not serve to Insert in the tables.

See for example that the News table is with the correct collate in the varchar field and this has the data without encoding problem. Item by item verification.

  • 1

    I appreciate the attention of all I’ve made the corrections of all Now all that remains is to correct the erroneously included legacy data But the new ones are all right! Thank you EVEN

Browser other questions tagged mysql asp character-encoding accentuation

You are not signed in. Login or sign up in order to post.