How to fix MySQL and PHP encoding?

Question

PHP

Hello, the problem is that there is a database, there is a table in it, there are several fields in the table (all text fields are encoded utf8_general_ci, as well as the database itself with the table). So, if you make a request from PHP code to add data with Russian characters, then they are inserted into the table like this: ва, but they are displayed normally on the page. If you make a request to add data in phpmyadmin (exactly the same as in php), then normal Russian characters are inserted into the table, and when you try to display them using PHP, question marks appear. SET NAMES utf8 prescribed.

Answer 1

What is the encoding on the page?

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

Answer 2

If the file with the PHP script is in UTF-8, as well as the HTML page, from the form of which the parameters for writing to the table are probably passed, are also in UTF-8, then most likely the matter is in the settings of PhpMyAdmin itself, namely - in the Server connection collation option .
This option is usually available on the main page of PhpMyAdmin.

Answer 3

First try header('Content-Type:text/html;charset=UTF-8', true); in each php file, then you try to use PDO, not mysql_connect and everything will be fine.
For example (this is not ideal, but it will work):

header('Content-Type:text/html;charset=UTF-8', true);

function errorHandler($code, $message, $file, $line) {
  debug(array('Error'=>$code,'Message'=>$message,'In file'=>$file,'On line'=>$line));
  exit();
}

function fatalErrorShutdownHandler() {
  $last_error = error_get_last();
  if ($last_error['type'] === E_ERROR) {
  // fatal error
    errorHandler(E_ERROR, $last_error['message'], $last_error['file'], $last_error['line']);
  }
}

set_error_handler('errorHandler');
register_shutdown_function('fatalErrorShutdownHandler');

function logError($msg) {
  global $cfg;
  if (isset($cfg['realpath'])) {
    // если в конфиге есть
    $logFile = $cfg['realpath'].'/logs/log.txt'; 
  } else {
    // если в корне есть
    if (file_exists('./logs') && is_dir('./logs')) {
      $logFile = './logs/log.txt'; 
    } else {
      // если на уровень выше есть
      if (file_exists('../logs') && is_dir('../logs')) { 
        $logFile = '../logs/log.txt'; 
      }	
    }
    
  }
  if (file_exists($logFile)) file_put_contents($logFile, date("Y/m/d H:i.s",time())."\n-------------------\n".$msg."\n\n",FILE_APPEND);
}

// дебаг
function debug($arr, $backtrace=false, $log2file=false) {
  echo "<pre style='border:3px dashed red;border-radius:10px;padding:10px;text-transform:none;'>";
  ob_start();
    if ($backtrace==true) debug_print_backtrace();
    print_r($arr);
  $out = ob_get_contents();
  ob_end_flush();
  echo "</pre>\n\n";
  if ($log2file==true) logError($out);
}

class dbClass {

  private $link;
  private $callsCount=0;
  private $callsDebug=Array();
  private $rawDebug = false; // put all queryes to log
  private $cache = array(); // local cache

  // функция соединения с БД
  function connect($dbdata) {
    $driver = $dbdata[ "dbdriver" ];
    $dsn = "${driver}:";
    $user = $dbdata[ "dbuser" ] ;
    $password = $dbdata[ "dbpassword" ] ;
    $options = $dbdata[ "dboptions" ] ;
    $attributes = $dbdata[ "dbattributes" ] ;
    
    // перечитываем аттрибуты
    foreach ( $dbdata[ "dsn" ] as $k => $v ) { $dsn .= "${k}=${v};"; }
    
    try {
      // стараемся создать подключение
      $this->link = new PDO ( $dsn, $user, $password, $options );
      // устанавливаем аттрибуты
      foreach ( $attributes as $k => $v ) {
        $this->link -> setAttribute ( constant ( "PDO::{$k}" ), constant ( "PDO::{$v}" ) ) ;
      }
      
    } catch(PDOException $e) {
      // если что-то не так, то вываливаем ошибку	
      errorHandler(0,$e -> getMessage(),__FILE__,__LINE__);
      
    }
  }

  function __construct($cfg) {
    $this->connect($cfg);
  }

  function uncommentSQL($sql) {
    $sqlComments = '@(([\'"]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\[email protected]';
    /* Commented version
    $sqlComments = '@
        (([\'"]).*?[^\\\]\2) # $1 : Skip single & double quoted expressions
        |(                   # $3 : Match comments
            (?:\#|--).*?$    # - Single line comments
            |                # - Multi line (nested) comments
             /\*             #   . comment open marker
                (?: [^/*]    #   . non comment-marker characters
                    |/(?!\*) #   . ! not a comment open
                    |\*(?!/) #   . ! not a comment close
                    |(?R)    #   . recursive case
                )*           #   . repeat eventually
            \*\/             #   . comment close marker
        )\s*                 # Trim after comments
        |(?<=;)\s+           # Trim after semi-colon
        @msx';
    */
    $uncommentedSQL = trim( preg_replace( $sqlComments, '$1', $sql ) );
    preg_match_all( $sqlComments, $sql, $comments );
    $extractedComments = array_filter( $comments[ 3 ] );
    //var_dump( $uncommentedSQL, $extractedComments );
    return $uncommentedSQL;
  }
  
  function parseQuery($q) {
    $q = $this->uncommentSQL($q);
    $q = str_replace("\n", " ", $q);
    $q = str_replace("\r", " ", $q);
    $q = str_replace("\t", " ", $q);
    $q = preg_replace("/\/\*.*\*\//Uis",'',$q);
    $q = preg_replace("/\s+/is",' ',$q);
    $q = trim($q);
    $type = explode(" ",$q);
    $type = trim(mb_strtoupper($type[0],"UTF-8"));
    return $type;

  }
  
  // простой запрос к базе
  function query($query,$cache=false) {
    global $cfg;
    // разбираем запрос
    $type = $this->parseQuery($query);
    $pureQuery = $this->uncommentSQL($query);
    
    if ($cfg['debug']==true) { $this->callsDebug[]=array("hash"=>md5($pureQuery),'query'=>str_replace("\t","",$query)); }
    
    // кеширование
    if (isset($this->cache[md5($pureQuery)]) && in_array($type,array('SELECT', 'SHOW'))) {
      return $this->cache[md5($pureQuery)];
    }
    // выполняем запрос
    try {
      $result=$this->link->query($query);
      
      // получаем результаты 
      if (in_array($type,array('SELECT', 'SHOW'))) {
        $result->setFetchMode(PDO::FETCH_OBJ);
        //TODO: если в запросе есть INTO OUTFILE то $result->fetch() бросает ошибку т.к. нечего возвращать надо научиться ловить пустой результат
        while($row = $result->fetch()) {
          $res[]=$row;
        }
      } elseif(in_array($type,array('INSERT'))) {
        $res=$this->link->lastInsertId(); 
      }
      
      // увеличиваем счетчик запросов
      $this->callsCount++;
      // если дебаг включен то добавляем запрос в лог
      if ($this->rawDebug == true) { logError($query); }
      
    } catch(PDOException $e) {
      errorHandler(0, $e -> getMessage()."\n".$query, __FILE__,__LINE__);
    }
    // кеширование
    if ($cache==true) $this->cache[md5($pureQuery)] = (isset($res[0])) ? $res : false;
    return (isset($res[0])) ? $res : false;
  }
  
  function queryInsertBinary($query, $binarray) {
    $pdoLink = $this->link;
    $stmt = $pdoLink->prepare($query);
    foreach($binarray as $key=>$value) {
      /*
      $db->queryInsertBinary(
          "INSERT INTO tbl VALUES(NULL, :SOME_ID, :BINARY_DATA);",
          array(
            'SOME_ID'		=> array('data'=>123,'param'=>array(PDO::PARAM_STR,sizeof('123'))), 
            'BINARY_DATA'	=> array('data'=>$binary_data,'param'=>array(PDO::PARAM_LOB,sizeof($binary_data))),
          )
      );
      */
      $stmt->bindParam(":".$key, $value['data'], $value['param'][0], $value['param'][1]); //PDO::PARAM_STR || PDO::PARAM_LOB, sizeof($binary)
    }
    $stmt->execute();
    return $pdoLink->lastInsertId();
  }
  
  
  function getVar($var) {
    return $this->$var;
  }
  
  function analizeUnCache(){
    $uncached = array();
    foreach($this->callsDebug as $cdk=>$cdv) {
      if (!isset($this->cache[$cdv['hash']])) {
        if (isset($uncached[$cdv['hash']])) $uncached[$cdv['hash']]++; else $uncached[$cdv['hash']]=1;
      }
    }
    return $uncached;
  }
  
  function analizeAll(){
    $queryAnalizer=array();
    foreach($this->callsDebug as $k=>$v) {
      if (isset(	$queryAnalizer[$v['hash']]	)) {
        $queryAnalizer[$v['hash']]++;
      } else {
        $queryAnalizer[$v['hash']] = 1;
      }
    }
    return $queryAnalizer;
  }
  
}

$cfg = array(
  'db' =>
  array(
    'dbdriver' => 'mysql',
    'dbuser' => 'root',
    'dbpassword' => 'root',
    'dsn' =>
    array(
      'host' => 'localhost',
      'dbport' => '3306',
      'dbname' => 'simple_db_name',
      'charset' => 'utf8',
    ),
    'dboptions' =>
    array(
      'PDO::MYSQL_ATTR_INIT_COMMAND' => 'set names utf8',
    ),
    'dbattributes' =>
    array(
      'ATTR_ERRMODE' => 'ERRMODE_EXCEPTION',
    ),
  ),
  'debug' => true,
  'siteurl' => (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] == "on" ? 'https://' : 'http://') . (isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : 'localhost'),
  'realpath' => dirname(dirname(__FILE__)),
);


$db = new dbClass($cfg['db']);