php – Problems with accents in MySQL

Question:

I'm not able to save phrases or words with special characters ( ' & " ). My database is like this:

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

All fields in utf8 and utf8_general_ci just fine… It just doesn't save the entire record, but if I remove the special characters from the field (which isn't 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 the data comes from a JSON request! I've tried it 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());
    }

JSON return (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
            }
         ], 

Answer:

A workaround is that if we are using PDO , the fourth parameter when 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;
}

This is the parameter I refer to:

array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')

• Besides, of course, we have to leave the COLLATE and CHARSET of the database and database tables configured correctly.