Error of data formatting

Asked

Viewed 184 times

0

Well, here’s the thing.

I’m going to fetch certain texts from the database, and then list them on the web, and when I do, the accents are all squares and so on. But when I write it in my own code with accents and stuff, everything’s normal. And I’m using the meta tag and still nothing.

What’s the matter ?

php mysql mysqli helper

2 answers

0

The problem is in the coding between the written code and the database. You can follow a few steps to solve, I will exemplify with UTF-8:

First check that your IDE is encoding in UTF-8. In the case of Eclipse is in the Edit >> Set Encoding >> Exchange for UTF-8.

Second make sure your browser understands that you are using UTF-8:

<?php
header('Content-Type: text/html; charset=UTF-8');

...

<!DOCTYPE HTML>
<html>
  <head>
    <meta charset="UTF-8" >

Make sure your bank is encrypted to UTF-8:

Mysql

CREATE DATABASE nome_bd CHARACTER SET UTF8;

And that your access to the database is in the same code.

Mysql (PDO):

$dsn = "mysql:host=localhost;dbname=world;charset=utf8";
$opcoes = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'
);
$pdo = new PDO($dsn, $usuario, $senha, $opcoes);

Mysqli:

$mysqli = new mysqli(...);
$mysqli->set_charset('utf8'));

  • I tried to put chaset in php and still did nothing. I didn’t understand was the part of the database. She’s already a maid, but I have to raise her again ?

  • No need to recreate the bank, it will only cost you a little more work.

0

As you have already created the database you will have a slightly bigger job, do the following:

Change the database encoding:

ALTER DATABASE <nome_do_banco> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Changing the encoding of tables:

ALTER TABLE <nome_da_tabela> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Changing the encoding of columns:

ALTER TABLE <nome_da_tabela> MODIFY <nome_da_coluna> <VARCHAR(255)> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Or you can run these queries down to facilitate your work by having to go through the entire bank.

SELECT DISTINCT concat('ALTER DATABASE ', TABLE_SCHEMA, ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like  'database_name';

--

SELECT concat('ALTER TABLE ', TABLE_SCHEMA, '.', table_name, ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

-- 

SELECT concat('ALTER TABLE ', t1.TABLE_SCHEMA, '.', t1.table_name, ' MODIFY ', t1.column_name, ' ', t1.data_type , '(' , CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and CHARACTER_SET_NAME = ‘old_charset_name';

  • I got it, thanks Thiago

  • Share with us how you managed to solve the problem. It will be useful for the next.

Browser other questions tagged php mysql mysqli helper

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