Problems with accentuation in Mysql

Asked

Viewed 692 times

3

I can’t save sentences or words with special characters ( ' & " ). My bank is like this:

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

All fields in utf8 and utf8_general_ci certinho... It simply does not save the entire record, but if I remove the special characters from the field (which is not the case), it saves the entire record!

I’m using PHP+Mysql... In PHP I use the header:

header('Content-Type: application/json; charset=utf-8');

Why data comes from a JSON request! I’ve tried with "utf8_decode()" too and nothing!

Query:

$matriz = json_decode ( json_encode ($_POST['dados']), true);        
$itens = $matriz['results'];

    foreach ($itens as $e ){
       $name = preg_replace("/[^a-zA-Z0-9_]/", "", strtr($e["name"], "áàãâéêíóôõúüçñÁÀÃÂÉÊÍÓÔÕÚÜÇÑ ", "aaaaeeiooouucnAAAAEEIOOOUUCN "));
       $SQL = "INSERT INTO tb_detalhes_empresa (place_id, nome_empresa, latitude, longitude, icone, scope, aberto_agora, reference, vicinity, harario_func_dias, foto_referencia, foto_height, foto_width, foto_atribuicoes, aberto_agora_periodos)VALUES('".utf8_decode($e["place_id"])."', '".$name."', '".$_POST['latitude']."', '".$_POST['longitude']."', '".utf8_decode($e["icon"])."', '', '', '".utf8_decode($e["reference"])."', '".utf8_decode($e["vicinity"])."', '', '', '', '', '', '');";                
       $query = mysqli_query($serve, $SQL); or die("erro".mysqli_error());
    }

Return JSON (part):

{
   "html_attributions" : [],
   "results" : [
      {
         "geometry" : {
            "location" : {
               "lat" : -33.870775,
               "lng" : 151.199025
            }
         },
         "icon" : "http://maps.gstatic.com/mapfiles/place_api/icons/travel_agent-71.png",
         "id" : "21a0b251c9b8392186142c798263e289fe45b4aa",
         "name" : "Rhythmboat Cruises",
         "opening_hours" : {
            "open_now" : true
         },
         "photos" : [
            {
               "height" : 270,
               "html_attributions" : [],
               "photo_reference" : "CnRnAAAAF-LjFR1ZV93eawe1cU_3QNMCNmaGkowY7CnOf-kcNmPhNnPEG9W979jOuJJ1sGr75rhD5hqKzjD8vbMbSsRnq_Ni3ZIGfY6hKWmsOf3qHKJInkm4h55lzvLAXJVc-Rr4kI9O1tmIblblUpg2oqoq8RIQRMQJhFsTr5s9haxQ07EQHxoUO0ICubVFGYfJiMUPor1GnIWb5i8",
               "width" : 519
            }
         ],
php mysql

  • You need to post more parts of your code, post the query that makes the data entry in the database.

  • edited the post!!!

  • I suspect the characters are breaking the query, they need to be escaped. take a test, remove '&' and do a code treatment with addslashes, $given = addslashes($_POST['donor']) to escape the quotes.

  • I think it should be utf8_encode() and not utf8_decode()

  • Dude, and worse than a complicated json array of the Google Places API, I’m posting a piece. But everything works perfectly, the problem is time to save data with special characters, records that do not have special, save normal!

  • Tried to utf8_encode()?

  • I tried, and nothing:

  • Like I said, try to escape the characters with the , type " & ', these characters may be misinterpreted.

  • These are the results: Regency Cafe </br> Specialty’s Cafe & Bakery </br> Montague’s Cafe </br> Freedom Cafe

  • Where it only shows 1° and the last

  • Great! It worked with "addslashes()"... Thanks Dunga

Show 6 more comments

2 answers

1

An alternative solution is that if we are using PDO, the fourth parameter at the time of instantiating the PDO class is super important for special character situations.

//Fazemos a conexão com nosso banco de dados

$dsn = "mysql:dbname=nome_do_banco;host=localhost"; 
$dbuser = "root"; //usuário do banco
$dbpass = ""; //senha do banco
try
{
    //Instanciamos a classe PDO passando como parâmetro os dados de conexão pegos acima
     $pdo = new PDO($dsn, $dbuser, $dbpass,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')); 
    //Esse quarto parâmetro é para corrigir problemas de acentuação

}
catch(PDOException $e)
{   
   //Se der erro na conexão, nós estourando esse Catch e é exibida essa mensagem junto com a mensagem do erro
   echo "A conexão falhou: ".$e->getMessage();
   exit;
}

That is the parameter I refer to:

array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')
  • In addition, of course, to leave the COLLATE and CHARSET of the database and the tables of the database configured in the correct way.

  • can still be better: private const OPTIONS = [&#xA; \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",&#xA; \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,&#xA; \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,&#xA; \PDO::ATTR_CASE => \PDO::CASE_NATURAL&#xA; ]; E declares at the PDO instance. resolves that accent part etc

0

From what I saw, it’s like Dunga said, there may be a break in the string, try to escape the special characters to insert into the bank:

$SQL = "INSERT INTO tb_detalhes_empresa 
           (place_id, nome_empresa, latitude, longitude, icone, scope, aberto_agora, reference, vicinity, harario_func_dias, foto_referencia, foto_height, foto_width, foto_atribuicoes, aberto_agora_periodos) 
       VALUES
           ('".mysqli_escape_string($serve, $e["place_id"])."', 
            '".mysqli_escape_string($serve, $name)."', 
            '".$_POST['latitude']."', 
            '".$_POST['longitude']."', 
            '".mysqli_escape_string($serve, $e["icon"]))."', 
            '', 
            '', 
            '".mysqli_escape_string($serve, $e["reference"]))."',               
            '".mysqli_escape_string($serve, $e["vicinity"]))."', 
            '', 
            '', 
            '', 
            '', 
            '', 
            '');";

Another detail, thinking about security, it would be interesting to perform a slightly larger filtering of the received variables, using filter_input or filter_var. Still on the issue of security, I would recommend the use of Prepared Statements, mysqli already has support for this.

Browser other questions tagged php mysql

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