PHP, PDO and legacy DB - change charset on the fly?

Question

Long time since I used PHP. Need to upgrade a very, very old site from mysql_* to PDO. The migration works well but the DB is a horrific mess of mixed Latin1 and utf8 tables. Cannot change that.

Have seen a neat solution here where you just create a connection for each type of charset, but my problem is that the site is built up as a hierarchy of classes all deriving from a single Db class, which defines the charset upon initialization.

In the old code the problem was "solved" by using mysql_set_charset(), but unfortunetaly I cannot finde a PDO equivalence.

How to change charset on the fly on a PDO connection? Is it even possible? Or can anyone suggest a kind of pattern?

This is a "fever rescue" caused by upgrading from PHP 5.x to 7.2, and it is not an option to refactor the entire codebase nor the database.

It goes more or less like this :

class Db {
  private $pdo;

  public function __construct() {  
    $dsn = "mysql:host=".$this->hostname.";dbname=".$this->database.";charset=".$this->charset;

    $opt = [
      PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES   => false
    ];

    try {
      $this->pdo = new PDO($dsn, $this->username, $this->password, $opt);
    } catch(PDOException $e) {
      echo "Error connecting to database: ". $e->getMessage();
    }
  }
}

class AnotherClass extends Db {
  public function __construct() {  
    parent::__construct();
    ...
  }
}

class YetAnotherClass extends AnotherClass [
  ...
}

Would like to implement a method on the Db class so I in the inherited classes can execute for example $this->changeCharset('Latin1');

would feeding the charset through __construct() as a parameter help or make sense in your case? — yasoh, Nov 30 '18 at 06:25
This is actually an even better idea!! It is a mess of classes all deriving from the same base classes, and it is the same base classes used as core for both "front end"-PHP and a lot of AJAX backend. But no PHP class is targeting multiple tables where the charset vary. Guess this is the "pattern" I was looking for (have been a long night and PHP far, far away) Could not see the wood for trees :( — davidkonrad, Nov 30 '18 at 06:37

score 1 · Accepted Answer · answered Nov 30 '18 at 06:11

1

When using PDO to connect to MySQL it is wise to explicitly set the character set to utf8 (of course, only when using utf8 is the charset). In the MySQL or MySQLi extension I would normally execute the query SET NAMES utf8 to set it.

In PDO the charset can be specified in the connection string:

$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);

The charset option is only used since PHP 5.3.6, so take this into account when running an older version of PHP. In that case you should run the following statement after constructing the PDO object:

$conn->exec('SET NAMES utf8');

But you should’t be running such an old version of PHP anyway.

answered Nov 30 '18 at 06:11

FreedomPride

1,098
1
7
30

1

Seems very promising. Will `$conn->exec('SET NAMES utf8');` work in PHP 7.2 for sure? It seem to work on PHP 5.5.9, so I can have a `setLatin1()` method on the base class executing `$this->pdo->exec('SET NAMES Latin1');`. It really is a mess that just need to be fixed and then move on :) – davidkonrad Nov 30 '18 at 06:31
1

@davidkonrad, yeah that's the turnaround for your issue. This is the similar way i did to cater for my problem. – FreedomPride Nov 30 '18 at 06:36
I take the chance and see if it works in production as well :) There seem not to be changes in 7.2 and PDO regarding this . Thanks a lot! – davidkonrad Nov 30 '18 at 06:45

score 1 · Answer 2 · answered Nov 30 '18 at 07:01

There is already a accepted answer but i'll post this just to extend my idea in the comments into your example code.

class Db {
  private $pdo;

  public function __construct($charset) {  
   $dsn = "mysql:host=".$this->hostname.";dbname=".$this->database.";charset=".$charset;

   $opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false
   ];

   try {
    $this->pdo = new PDO($dsn, $this->username, $this->password, $opt);
   } catch(PDOException $e) {
     echo "Error connecting to database: ". $e->getMessage();
   }
  }
}

class AnotherClass extends Db {
  public function __construct() {  
    parent::__construct('latin1');
  }
}

PHP, PDO and legacy DB - change charset on the fly?

2 Answers2