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
– yasoh, Commented Nov 30, 2018 at 6: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
– davidkonrad, Commented Nov 30, 2018 at 6:37

FreedomPride · Accepted Answer · 2018-11-30 06:11:59Z

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, 2018 at 6:11

FreedomPride

1,1001 gold badge8 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

davidkonrad Over a year ago

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 :)

FreedomPride Over a year ago

@davidkonrad, yeah that's the turnaround for your issue. This is the similar way i did to cater for my problem.

davidkonrad Over a year ago

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!

yasoh · Accepted Answer · 2018-11-30 07:01:31Z

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');
  }
}

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related