Problems with accentuation in MySQL

Question

Problems with accentuation in MySQL

Navigation

#1 by (0 votes)

1

I can not save sentences or words with special characters ('&'). My bank looks like this:

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

All fields in utf8 and utf8_general_ci right ... 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 the data comes from a JSON request! I have already 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

asked by anonymous 13.02.2016 / 18:55

1 answer

Is there an alternative method to window.print ()? Send Attachments by Amazon SES (SendEmail Function)

score 0 · Answer 1

From what I saw, that's how Dunga said, the string break might be occurring, 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, would be interesting to do a slightly larger filtering of the variables received, using filter_input or filter_var. Also in the security issue, I would recommend using Prepared Statements, mysqli already supports this.